Recently while working on a requirement for a client we came across this scenario. It’s fairly widely used although I would not recommend it. The example is in very simple terms explained below:-
|
|
SEEK | SCAN |
If you look at the above query you will see that when the @int parameter has a value we get an Index Seek and everything is fine. When the parameter is NULL however we get a scan and we fetch all rows those with NULLS and those without NULLs. In this table the id column is a PK.
So the developer basically wants to fetch all rows when he doesn’t pass a value to the stored procedure input so naturally we need to scan the table else we seek a single value within the Index.
The problem with the way above query is written is that it becomes cumbersome when we deal with 3 or more parameters, such that when a combination is being passed we need to identify the combo and fetch rows that meet that combo.
So we rewrite the query as below:-
In the above query we replace the IF ELSE Logic with ISNULL and OR Conditions to fetch rows based on the input being passed. However if you notice the execution plan you will see that we now have a SCAN happening even when filtering for only a single row in the PK Column. So while we were able to simplify the query we have introduced a performance issue resulting in an increase in query execution time and number of logical reads.
Another common way the above query is written when your browse the forums is as below:-
In the above query we replace the OR condition and ISNULL logic with a case statement. This still doesn’t address the SCAN issue with the index.
At this point we have three different solutions that will work functionally but do not provide performance improvement we would like. Most people stop at one of the above three as shown in the forums listed below.
http://stackoverflow.com/questions/1671084/stored-proc-return-all-rows-when-parameters-are-null
http://stackoverflow.com/questions/6131542/how-to-return-all-records-if-parameter-is-null
There is also one approach as shown in the last link where Dynamic SQL is used to modify where criteria as needed. Naturally this is not recommended so all the reasons why Dynamic SQL is not recommended.
So what is the other choice we have!! Well the assumption is that the same logic needs to be applied to all the columns. This is not needed. For example in the above table the id column is a PK and will never have a NULL value in it. So we rewrite the query as shown below
Notice how by changing the way we filter the PK column we are able to achieve a IX Seek.
See the query execution plan for different combination below
Both parameters have values |
|
NON PK Columns is filtered |
|
BOTH inputs are NULL |
|
Ideally the last case should never occur coz there will not be a need to perform select * from a table.
As you can see by filtering the PK column differently than the other columns we are able to perform SEEK operations when fetching just 1 row, 100 rows or all rows. Thus reducing the performance impact of OR conditions and CASE Statements.
Addendum
A comparison of time and Io stats for above approach vs ISNULL and OR Condition approach. Notice how while performing similar number of scans we are still able to avoid a parallelism operator. Also compare the relative costs of the two execution plans to each other.
Please Consider Subscribing
