This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.
When looking into performance issues on support cases there is one pattern called ALL-IN-ONE QUERY that we always sees that cause many issues. This pattern is very common and logically looks correct but will cause huge performance degradation.
The idea behind this pattern is that you want a query that filter by the parameter or ignore the filter if send null or zero. And as said logically it looks correct
- (CustomerID = @CustomerID OR @CustomerID = 0)
Or some other variations
- (CustomerID = @CustomerID OR @CustomerID IS NULL)
- CustomerID = CASE WHEN ISNULL(@CustomerID, 0) = 0 THEN CustomerID ELSE @CustomerID END
Find below a sample. For this test I want to filter by Customer ID or by Last Name.
- If I send @CustomerID = XXX I want to filter specific customer ID filter
- If I send @CustomerID = 0 I want the query to ignore the customer ID filter
- if I send @LastName I want to filter specific last name
- if send NULL to @LastName filter, want to ignore it
- I can also ignore both to list all users
What will happen is that the query works, usually on dev databases with small datasets will run fine, but when you go to production with huge databases you going to notice a huge slowdown
Does not matter the parameter you sent it will always scan the index. Even that you have a covering index it will completely ignore it.
This is not a defect this is an expected behavior when SQL is building query plan.
Solution 1 - OPTION RECOMPILE
One workaround, if query is not executed very often, is to use OPTION (RECOMPILE) at end. This might not be a good option if running very frequently because it will increase the CPU usage and can cause compilation queue waits because for each new execution SQL will have to create a new plan.
It will create the best plan for each set of filters used
Solution 2 - IF/ELSEs
One workaround when you have a limited number of options is to create a series of IF and ELSEs. But this can be an issue as number of options increase.
Solution 3 - Dynamic query
When you have multiple options the best option is to use Dynamic query + sp_executesql sending the parameters, this way you will have good plan depending on parameters sent to procedure and also will reuse plan.
!!! Use sp_executesql parameters. Do not concatenate parameters to the string. This can lead to SQL Injection issues
I hope this help you build better queries