sql select condition performance

Question

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?

You should use query analyzer. Is the Id field a Primary Key? — Christian Phillips
– Christian Phillips, Commented Jul 10, 2014 at 11:16
Not enough information to answer. Is there an index on the table? Primary Key? How large is this table? Have you looked at the execution plan? The example given here is so trivial it won't make a difference. — alroc
– alroc, Commented Jul 10, 2014 at 11:18

Nimantha · Accepted Answer · 2022-01-05 07:51:56Z

3

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.

edited Jan 5, 2022 at 7:51

Nimantha

6,5476 gold badges32 silver badges78 bronze badges

answered Jul 10, 2014 at 11:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex · Accepted Answer · 2014-07-10 11:28:13Z

0

To answer the simple question yes option 2 SELECT * FROM Tab WHERE id IN (2, 4) would be faster as you said because int comparison is faster.

One way to speed it up is to add indexes to your columns to speed up evaluation, filtering, and the final retrieval of results.

If this table was to grow even more you should also not SELECT * but SELECT id, value otherwise you may be pulling more data than you need.

You can also speed up your query's buy adding WITH(NOLOCK) as the speed of your query might be affected by other sessions accessing the tables at the same time. For example SELECT * FROM Tab WITH(NOLOCK) WHERE id IN (2, 4) . As mentioned below though adding nolock is not a turbo and should only be used in appropriate situations.

answered Jul 10, 2014 at 11:28

Alex

1,0727 silver badges22 bronze badges

3 Comments

alroc Over a year ago

NOLOCK is not a magic turbo button and should not be used unless you know exactly why you're using it and the data integrity ramifications of it. Even if you're fine with dirty reads, it's a bad idea and bad practice to fall into.

Alex Over a year ago

Agreed, I have just added some of this information to my answer. In my situation if I did not have NOLOCK on my query's the speeds would be considerably slower.

alroc Over a year ago

If you need NOLOCK on all of your queries to get acceptable performance (at the expense of dirty reads and the other risks), your database is not configured or designed well. Fix the root cause.

Collectives™ on Stack Overflow

sql select condition performance

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related