Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server 'In' Statement Item Order for Performance

Given the SQL Statement:

SELECT * 
FROM MY_TABLE
WHERE SomeNumberField in (0,99999)

If I can guarantee that the majority of rows in MY_TABLE have SomeNumberField set to 99999, and can project that this will remain the case indefinately, is it better to write the above query like this:

SELECT * 
FROM MY_TABLE
WHERE SomeNumberField in (99999,0)
like image 245
James Wiseman Avatar asked Feb 01 '10 15:02

James Wiseman


2 Answers

If you have an index on SomeNumberField, then this statement will be either just split into two range scans on the index, or processed as a TABLE SCAN / CLUSTERED INDEX SCAN with a filter.

The latter is more probable, provided that the majority of your rows have SomeNumberField = 999999

The range scans will always be performed in the index order, regardless of the constants order in the IN predicate.

Filter comparison time is negligible compared to the time required to fetch the data pages.

like image 163
Quassnoi Avatar answered Oct 04 '22 21:10

Quassnoi


This kind of optimization can be done automatically by the SQL optimizer provided you gather some statistics that are used by the optimizer. You can use tools provided by the database vendor for this task. For SQL server refer to the following article on MSDN: http://msdn.microsoft.com/en-us/library/cc966419.aspx

like image 35
bkm Avatar answered Oct 04 '22 21:10

bkm