SQL Server Performance Forum – Threads Archive
Most efficient manner to query int field?
I have an int field and want to know if it is more efficient to query that field using ">" and "<" or the Left string function e.g. My data are integers for dates yyyymmdd so if I want to find anything for the month of June I can search for it in two ways: 1. Fieldname>20040601 and Fieldname<20040701 or 2. Left(fieldname,6) = 200406Both of these methods will yield the same data set…but which one is more efficient….? Or is there an even better method? Keep in mind that the table it is querying is only 75,000 records.
My 2 cents on this: If your table had considerably many more records, I believe the "<" and ">" would yield the results ‘faster’. Remember that the second construct requires SQL server to convert the integer to an in-memory character variable first before operatimg on it – a minor detour but a detour nonetheless. With 75K records, either method should not exhibit a significant difference in performance even without a useful index on that column unless your scenario has other ‘complications’.
Nathan H.O.
Moderator
SQL-Server-Performance.com
Also, as vbkenya says, when fieldname is converting to characters it then becomes a 6 byte string, instead of a 32 bit integer.<br /><br />Purely going on textbook theory the first method should be marginally faster purely from a cpu point of view, since 32 bits can be compared in one instruction whereas comparing 6 bytes is more work for the cpu since it wont fit nicely into a register<br /><br />Probably such a minor difference that its neglible though <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> Id opt for the first method I think
]]>