Speed of SQL Comparisons 
Author Message
 Speed of SQL Comparisons

Does anyone know if there is a difference in speed between Integer or
Text comparisons?  I have a field [Data] that I can store as either Int
or Text in a table on which I run thousands of queries.  

Which would be faster:

SELECT * FROM TABLE
WHERE [DATA]=12345

OR

SELECT * FROM TABLE
WHERE [DATA]="12345"

Thanks,
Anna



Mon, 23 Jul 2001 03:00:00 GMT  
 Speed of SQL Comparisons
Number compares on number fields are faster. But the data field has to be a
number type for this to be true.

Michael


Quote:
> Does anyone know if there is a difference in speed between Integer or
> Text comparisons?  I have a field [Data] that I can store as either Int
> or Text in a table on which I run thousands of queries.

> Which would be faster:

> SELECT * FROM TABLE
> WHERE [DATA]=12345

> OR

> SELECT * FROM TABLE
> WHERE [DATA]="12345"

> Thanks,
> Anna



Mon, 23 Jul 2001 03:00:00 GMT  
 Speed of SQL Comparisons
Dear Anna:

This answer is from a theoretical point of view:  I've programmed such
things, and the string comparison is bound to take several times as long as
the integer or long integer version.  However, the comparison is probably a
tiny fraction of the CPU needed to set up the problem, retrieve the data to
be compared.  It could easily take 1000 times as long to prepare for the
comparison than to make it.  If a disk access is required, then it takes
tens or hundreds of thousands of times as long.  So, if you can speed up
0.001% of a job, how much faster is it.  You'll never see it!

So, test it emperically and prove me wrong.  The time it takes to retrieve
a string compared to an integer may be more important than the time to
compare, so all bets are off!

Tom Ellison

Quote:

> Does anyone know if there is a difference in speed between Integer or
> Text comparisons?  I have a field [Data] that I can store as either Int
> or Text in a table on which I run thousands of queries.

> Which would be faster:

> SELECT * FROM TABLE
> WHERE [DATA]=12345

> OR

> SELECT * FROM TABLE
> WHERE [DATA]="12345"

> Thanks,
> Anna



Mon, 23 Jul 2001 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Comparison speed Access/SQL/ other database format

2. Speed Comparison Post #1 of 2 (Was Fast Drawing)

3. Speed Comparison Post #2 of 2 - quite large (Was Fast Drawing)

4. X-basic speed comparison

5. Floating Point Speed - Comparison of VB 5.0, Delphi 3.0, and VC 5.0

6. Speed comparison with VB3 & 4-32bit

7. Speed comparison of VB4

8. Speed of string comparisons in VB 3.0 Pro

9. Speed, Speed, and Speed!

10. Speed Speed Speed - Cutting down on wasted cycles

11. SQL string comparison - dbgrid

12. How to include a memory variable in SQL like comparison

 

 
Powered by phpBB® Forum Software