SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Which One is Better? IN or BETWEEN?

I just had an interesting case of performance tuning: a query with multiple predicates on a very large table. Something like this:

SELECT
    ...
FROM
    dbo.LotsOfEvents
WHERE
    EventType BETWEEN 1 AND 3
AND
    OperatorId = 9876;

 

There was a non-clustered index on EventType and OperatorId (in that order).

The number of rows returned by the query was 12, and I would expect a very efficient index seek in this case.

There was indeed an index seek, but the number of logical reads was huge, and it was practically a very large scan.

 

The reason is that the first index key was “EventType” on which the predicate was BETWEEN.

So the optimizer performed an seek predicate on this key and scanned the entire range of rows between 1 and 3 (approximately 300M rows).

Then it performed a residual predicate on the “OperatorId” column, which resulted in just 12 rows.

 

One option is to change the order of the keys in the index, but doing that on such a large table has a lot of implications.

Instead, I modified the query to use IN instead of BETWEEN, like this:

 

 

SELECT
    ...
FROM
    dbo.LotsOfEvents
WHERE
    EventType IN (1,2,3)
AND
    OperatorId = 9876;

 

Now the optimizer performed 3 seek operations, each one with a very efficient seek predicate on both “EventType” and “OperatorId”.

This small change in the query resulted in 9 logical reads instead of 300M, and in almost zero execution time instead of an application timeout.

 

This doesn’t mean that IN is always better then BETWEEN.

But it was certainly better in this case.

 

The post Which One is Better? IN or BETWEEN? appeared first on Madeira Data Solutions.

Guy Glantser

Guy Glantser, Data Platform MVP, is the leader of the Israeli PASS chapter and also the CEO and founder of Madeira Data Solutions. His career has been focused on the Microsoft Data Platform for the past 20 years, performing various database roles as either an on-site DBA, an external consultant or a speaker. Guy is involved in many activities in the Microsoft Data Platform community. He occasionally speaks at community events, such as PASS Summit, SQLBits, SQL Saturdays and user groups around the world. He also co-hosts the SQL Server Radio podcast.

Comments

Leave a comment on the original post [www.madeiradata.com, opens in a new window]

Loading comments...