[SOLVED] sql select condition performance

Issue

I have a table ‘Tab’ with data such as:

 id |  value
---------------
 1  |  Germany
 2  |  Argentina
 3  |  Brasil
 4  |  Holland

What way of select is better by perfomane?

1. SELECT * FROM Tab WHERE value IN ('Argentina', 'Holland')

or

2. SELECT * FROM Tab WHERE id IN (2, 4)

I suppose that second select would be faster, because int comparison is faster than string. Is that true for MS SQL?

Solution

This is a premature optimization. The comparison between integers and strings is generally going to have a minimal impact on query performance. The drivers of query performance are more along the lines of tables sizes, query plans, available memory, and competition for resources.

In general, it is a good idea to have indexes on columns used for either comparison. The first column looks like a primary key, so it automatically gets an index. The string column should have an index built on it. In general, indexes built on an integer column will have marginally better performance compared to integers built on variable length string columns. However, this type of performance difference really makes a difference only in environments with very high levels of transactions (think thousands of data modification operations per second).

You should use the logic that best fits the application and worry about other aspects of the code.

Answered By – Gordon Linoff

Answer Checked By – Dawn Plyler (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *