Friday 23 August 2013

1.10 - Performance Of The IN Operator

I recently had to look at the performance of a query that was running with high CPU, and taking over 40+ seconds to run.
The scenario -
A stored proc used a view that joined to a table with a list of numbers from a table. This table was generated from a string that was passed to the stored proc as a string (possibly 10 values) separated by commas. This string was then split into a table via a UDF (table function) and this was then used in the IN operator to filter the number of results (it had to be dynamic). All indexes possible were made available, within the efficient running of the system.

If I changed the IN operator to a != or = this changed the query time to < 200ms from 40+Seconds but this was not fit for purpose as in this instance as the filter had to be dynamic. I then changed the IN statement that was contained in the WHERE clause to be part of a inner join and to join on the predicates that the IN operator was using ....and, wow! the number of logical reads on the table slumped from 4+million to 4 and the time down to 6 secs!! Not too bad as it was an offline, but crucial process.

So the long and the short is to remember; If you can join to it do so, as you may pay the price for utilisation of IN statements.

No comments:

Post a Comment