{"id":2106,"date":"2018-03-13T16:30:15","date_gmt":"2018-03-13T14:30:15","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=2106"},"modified":"2018-01-29T23:56:29","modified_gmt":"2018-01-29T21:56:29","slug":"revisiting-catch-all-queries","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2018\/03\/13\/revisiting-catch-all-queries\/","title":{"rendered":"Revisiting catch-all queries"},"content":{"rendered":"

I originally wrote about catch-all queries<\/a> early in 2009, just as something that I\u2019d seen several times in client code. It turned into the 3rd most popular post ever on my blog.<\/p>\n

A lot\u2019s changed since 2009. When I wrote the original post, most production servers were SQL 2005 or SQL 2000. SQL 2008 had been out less than a year and its fix for catch-all queries, the RECOMPILE hint, didn\u2019t even work properly (it had an incorrect results bug in RTM, was pulled in SP1 and fixed in SP2)<\/p>\n

As such, my feelings on how to solve the problem with catch-all queries has changed over the years.<\/p>\n

Before I get to solutions, let\u2019s start with the root cause of the problem with catch-all queries – plan caching and the need for plans to be safe for reuse.<\/p>\n

Let\u2019s take a sample query. I\u2019ll use the same one I used in the original post.<\/p>\n

CREATE PROCEDURE SearchHistory\r\n(@Product int = NULL, @OrderID int = NULL, @TransactionType char(1) = NULL, @Qty int = NULL)\r\nAS\r\nSELECT ProductID, ReferenceOrderID, TransactionType, Quantity,\r\nTransactionDate, ActualCost\r\nFROM Production.TransactionHistory\r\nWHERE (ProductID = @Product Or @Product IS NULL)\r\nAND (ReferenceOrderID = @OrderID OR @OrderID Is NULL)\r\nAND (TransactionType = @TransactionType OR @TransactionType Is NULL)\r\nAND (Quantity = @Qty Or @Qty is null)\r\nGO<\/pre>\n
There are two nonclustered indexes on the TransactionHistory table, one on ProductID, one on ReferenceOrderID and ReferenceLineID.<\/p>\n
For the initial discussion, let\u2019s just consider two of the clauses in the WHERE. I\u2019ll leave the other two in the stored proc, but they won\u2019t be used.<\/p>\n
WHERE (ProductID = @Product Or @Product IS NULL)\r\nAND (ReferenceOrderID = @OrderID OR @OrderID Is NULL)<\/pre>\n
We would expect, if the ProductID parameter is passed, to get a seek using the index on ProductID, if the ReferenceOrderID parameter is passed, to get a seek using the index on ReferenceOrderID, and if both are passed, then either an index intersection or a seek on one of the indexes, key lookup and secondary filter for the other, plus, in all cases, a key lookup to fetch the columns for the SELECT.<\/p>\n
That\u2019s not what we get (I cleared the plan cache before running each of these).<\/p>\n
\"ProductScan\"<\/a><\/p>\n
\"OrderScan\"<\/a><\/p>\n
The expected indexes are used, but they\u2019re used for scans not seeks. Why? Let\u2019s just consider the second plan for a bit.<\/p>\n
The index aren\u2019t used for seeks, because plans must be safe for reuse. If a plan was generated with an index seek, seeking for ReferenceOrderID = @OrderID, and that plan was cached and reused later when @OrderID was NULL, we\u2019d get incorrect results. ReferenceOrderID = NULL matches no records.<\/p>\n
And so we have index scans with the full predicate (ReferenceOrderID = @OrderID OR @OrderID Is NULL) applied after the index is read.<\/p>\n
This is not particularly efficient, as the properties on the index seek shows.<\/p>\n
\"InefficientIndexScan\"<\/a><\/p>\n
The entire index, all 113443 rows were read, to return a single row. Not ideal, but it\u2019s far from the largest problem with this form of query.<\/p>\n
The plan\u2019s got an index scan on the index on ReferenceOrderID, and then a key lookup back to the clustered index. That key lookup has a secondary filter on it, (ProductID = @Product Or @Product IS NULL). The optimiser assumed that a small number of rows would be returned from the index seek on ReferenceOrderID (1.47 to be specific), and hence the key lookup would be cheap, but that\u2019s not going to be the case if the plan is reused with a ProductID passed to it instead of a ReferenceOrderID.<\/p>\n
Before we look at that, the performance characteristics for the procedure being called with the ReferenceOrderID parameter are:<\/p>\n
\"PerformanceOrder\"<\/a><\/p>\n
The duration and CPU are both in microseconds, making this a very fast query, despite the index scan.<\/p>\n
Now, without clearing the plan cache, I\u2019m going to run the procedure with only the ProductID parameter passed.<\/p>\n
\"PerformanceProduct\"<\/a><\/p>\n
CPU\u2019s gone from an average of 8ms to around 120ms. Duration has gone from average around 6ms to about 125ms and reads have jumped from 271 (2 MB of data processed) to 340 597 (2.6 GB of data processed)<\/p>\n
And this is for a table that has 113k records and a query that returned 4 rows.<\/p>\n
The key lookup, which was fine when an OrderID was passed, is not fine when @OrderID is NULL and the index scan returns the entire table.<\/p>\n
\"ExpensiveIndexScan\"<\/a><\/p>\n
\"ExpensiveKeyLookup\"<\/a><\/p>\n
The plans that the optimiser has come up with for this query form aren\u2019t stable. They\u2019re safe for reuse, they have to be, but performance-wise they\u2019re not stable.<\/p>\n
But, maybe it\u2019s just this form of query, there are other ways to write queries with multiple optional parameters.<\/p>\n
Let\u2019s try the CASE and COALESCE forms.<\/p>\n
CREATE PROCEDURE SearchHistory_Coalesce\r\n(@Product int = NULL, @OrderID int = NULL, @TransactionType char(1) = NULL, @Qty int = NULL)\r\nAS\r\nSELECT ProductID, ReferenceOrderID, TransactionType, Quantity,\r\nTransactionDate, ActualCost\r\nFROM Production.TransactionHistory\r\nWHERE ProductID = COALESCE(@Product, ProductID)\r\nAND ReferenceOrderID = COALESCE(@OrderID, ReferenceOrderID)\r\nAND TransactionType = COALESCE(@TransactionType, TransactionType)\r\nAND Quantity = COALESCE(@Qty, Quantity)\r\nGO\r\n\r\nCREATE PROCEDURE SearchHistory_Case\r\n(@Product int = NULL, @OrderID int = NULL, @TransactionType char(1) = NULL, @Qty int = NULL)\r\nAS\r\nSELECT ProductID, ReferenceOrderID, TransactionType, Quantity,\r\nTransactionDate, ActualCost\r\nFROM Production.TransactionHistory\r\nWHERE ProductID = CASE WHEN @Product IS NULL THEN ProductID ELSE @Product END\r\nAND ReferenceOrderID = CASE WHEN @OrderID IS NULL THEN ReferenceOrderID ELSE @OrderID END\r\nAND TransactionType = CASE WHEN @TransactionType IS NULL THEN TransactionType ELSE @TransactionType END\r\nAND Quantity = CASE WHEN @Qty IS NULL THEN Quantity ELSE @Qty END\r\nGO<\/pre>\n
\"Coalesce\"<\/a><\/p>\n
\"Case\"<\/a><\/p>\n
These both give us full table scans, rather than the index scan\/key lookup we saw earlier. That means their performance will be predictable and consistent no matter what parameter values are used. Consistently bad, but at least consistent.<\/p>\n
It\u2019s also worth noting that neither of these will return correct results if there are NULL values in the columns used in the WHERE clause (because NULL != NULL). Thanks to Hugo Kornelis (b<\/a> | t<\/a>) for pointing this out.<\/p>\n
And then two more forms that were mentioned in comments on the original post, slightly more complicated:<\/p>\n
CREATE PROCEDURE SearchHistory_Case2\r\n(@Product int = NULL, @OrderID int = NULL, @TransactionType char(1) = NULL, @Qty int = NULL)\r\nAS\r\nSELECT\u00a0 ProductID,\r\nReferenceOrderID,\r\nTransactionType,\r\nQuantity,\r\nTransactionDate,\r\nActualCost\r\nFROM\u00a0\u00a0\u00a0 Production.TransactionHistory\r\nWHERE\u00a0\u00a0 (CASE WHEN @Product IS NULL THEN 1\r\nWHEN @Product = ProductID THEN 1\r\nELSE 0\r\nEND) = 1\r\nAND (CASE WHEN @OrderID IS NULL THEN 1\r\nWHEN @OrderID = ReferenceOrderID THEN 1\r\nELSE 0\r\nEND) = 1\r\nAND (CASE WHEN @TransactionType IS NULL THEN 1\r\nWHEN @TransactionType = TransactionType THEN 1\r\nELSE 0\r\nEND) = 1\r\nAND (CASE WHEN @Qty IS NULL THEN 1\r\nWHEN @Qty = Quantity THEN 1\r\nELSE 0\r\nEND) = 1\r\nGO\r\n\r\nCREATE PROCEDURE SearchHistory_Complex\r\n(@Product int = NULL, @OrderID int = NULL, @TransactionType char(1) = NULL, @Qty int = NULL)\r\nAS\r\nSELECT\u00a0 ProductID,\r\nReferenceOrderID,\r\nTransactionType,\r\nQuantity,\r\nTransactionDate,\r\nActualCost\r\nFROM\u00a0\u00a0\u00a0 Production.TransactionHistory\r\nWHERE ((ProductID = @Product AND @Product IS NOT NULL) OR (@Product IS NULL))\r\nAND ((ReferenceOrderID = @OrderID AND @OrderID IS NOT NULL) OR (@OrderID IS NULL))\r\nAND ((TransactionType = @TransactionType AND @TransactionType IS NOT NULL) OR (@TransactionType IS NULL))\r\nAND ((Quantity = @Qty AND @Qty IS NOT NULL) OR (@Qty IS NULL))<\/pre>\n
These two give the same execution plans as the first form we looked at, index scan and key lookup.<\/p>\n
Performance-wise, we\u2019re got two different categories of query. We\u2019ve got some queries where the execution plan contains an index scan on one or other index on the table (depending on parameters passed) and a key lookup, and others where the execution plan contains a table scan (clustered index scan) no matter what parameters are passed.<\/p>\n
But how do they perform? To test that, I\u2019m going to start with an empty plan cache and run each query form 10 times with just the OrderID being passed and then 10 times with just the ProductID being passed, and aggregate the results.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Procedure<\/td>\nParameter<\/td>\nCPU (ms)<\/td>\nDuration (ms)<\/td>\nReads<\/td>\n<\/tr>\n
SearchHistory<\/td>\nOrderID<\/td>\n5.2<\/td>\n50<\/td>\n271<\/td>\n<\/tr>\n
<\/td>\nProductID<\/td>\n123<\/td>\n173<\/td>\n340597<\/td>\n<\/tr>\n
SearchHistory_Coalesce<\/td>\nOrderID<\/td>\n7.8<\/td>\n43<\/td>\n805<\/td>\n<\/tr>\n
<\/td>\nProductID<\/td>\n9.4<\/td>\n45<\/td>\n805<\/td>\n<\/tr>\n
SearchHistory_Case<\/td>\nOrderID<\/td>\n12.5<\/td>\n55<\/td>\n805<\/td>\n<\/tr>\n
<\/td>\nProductID<\/td>\n7.8<\/td>\n60<\/td>\n804<\/td>\n<\/tr>\n
SearchHistory_Case2<\/td>\nOrderID<\/td>\n10.5<\/td>\n48<\/td>\n272<\/td>\n<\/tr>\n
<\/td>\nProductID<\/td>\n128<\/td>\n163<\/td>\n340597<\/td>\n<\/tr>\n
SearchHistory_Complex<\/td>\nOrderID<\/td>\n7.8<\/td>\n40<\/td>\n272<\/td>\n<\/tr>\n
<\/td>\nProductID<\/td>\n127<\/td>\n173<\/td>\n340597<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 <\/p>\n
The query forms that had the clustered index scan in the plan have consistent performance. On large tables it will be consistently bad, it is a full table scan, but it will at least be consistent.<\/p>\n
The query form that had the key lookup have erratic performance, no real surprise there, key lookups don\u2019t scale well and looking up every single row in the table is going to hurt. And note that if I ran the queries in the reverse order on an empty plan cache, the queries with the ProductID passed would be fast and the queries with the OrderID would be slow.<\/p>\n
So how do we fix this?<\/p>\n
When I first wrote about this problem 7 years ago, I recommended using dynamic SQL and discussed the dynamic SQL solution in detail. The dynamic SQL solution still works very well, it\u2019s not my preferred solution any longer however.<\/p>\n
What is, is the RECOMPILE hint.<\/p>\n
Yes, it does cause increased CPU usage due to the recompiles (and I know I\u2019m likely to get called irresponsible and worse for recommending it), but in *most* cases that won\u2019t be a huge problem. And if it is, use dynamic SQL.<\/p>\n
I recommend considering the RECOMPILE hint first because it\u2019s faster to implement and far easier to read. Dynamic SQL is harder to debug because of the lack of syntax highlighting and the increased complexity of the code. In the last 4 years, I\u2019ve only had one case where I went for the dynamic SQL solution for a catch-all query, and that was on a server that was already high on CPU, with a query that ran many times a second.<\/p>\n
From SQL 2008 SP2\/SQL 2008 R2 onwards, the recompile hint relaxes the requirement that the generated plan be safe for reuse, since it\u2019s never going to be reused. This firstly means that the plans generated for the queries can be the optimal forms, index seeks rather than index scans, and secondly will be optimal for the parameter values passed.<\/p>\n
\"CatchallIndexSeek\"<\/a><\/p>\n
And performance-wise?<\/p>\n
\"RecompilePerformance\"<\/a><\/p>\n
Reads down, duration down and CPU down even though we\u2019re recompiling the plan on every execution (though this is quite a simple query, so we shouldn\u2019t expect a lot of CPU to generate the plan).<\/p>\n
How about the other forms, do they also improve with the RECOMPILE hint added? As I did before, I\u2019m going to run each 10 times and aggregate the results, that after adding the RECOMPILE hint to each.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Procedure<\/td>\nParameter<\/td>\nCPU (ms)<\/td>\nDuration (ms)<\/td>\nReads<\/td>\n<\/tr>\n
SearchHistory<\/td>\nOrderID<\/td>\n0<\/td>\n1.3<\/td>\n28<\/td>\n<\/tr>\n
<\/td>\nProductID<\/td>\n0<\/td>\n1.2<\/td>\n19<\/td>\n<\/tr>\n
SearchHistory_Coalesce<\/td>\nOrderID<\/td>\n6.2<\/td>\n1.2<\/td>\n28<\/td>\n<\/tr>\n
<\/td>\nProductID<\/td>\n3.2<\/td>\n1.2<\/td>\n19<\/td>\n<\/tr>\n
SearchHistory_Case<\/td>\nOrderID<\/td>\n1.6<\/td>\n1.3<\/td>\n28<\/td>\n<\/tr>\n
<\/td>\nProductID<\/td>\n0<\/td>\n1.2<\/td>\n19<\/td>\n<\/tr>\n
SearchHistory_Case2<\/td>\nOrderID<\/td>\n7.8<\/td>\n15.6<\/td>\n232<\/td>\n<\/tr>\n
<\/td>\nProductID<\/td>\n7.8<\/td>\n11.7<\/td>\n279<\/td>\n<\/tr>\n
SearchHistory_Complex<\/td>\nOrderID<\/td>\n1.5<\/td>\n1.4<\/td>\n28<\/td>\n<\/tr>\n
<\/td>\nProductID<\/td>\n0<\/td>\n1.2<\/td>\n19<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 <\/p>\n
What can we conclude from that?<\/p>\n
One thing we note is that the second form of case statement has a higher CPU, duration and reads than any other. If we look at the plan, it\u2019s still running as an index scan\/key lookup, despite the recompile hint.<\/p>\n
The second thing is that the more complex forms perform much the same as the simpler forms, we don\u2019t gain anything by adding more complex predicates to \u2018guide\u2019 the optimiser.<\/p>\n
Third, the coalesce form might use slightly more CPU than the other forms, but I\u2019d need to test a lot more to say that conclusively. The numbers we\u2019ve got are small enough that there might well be measuring errors comparable to the number itself.<\/p>\n
Hence, when this query form is needed, stick to the simpler forms of the query, avoid adding unnecessary predicates to \u2018help\u2019 the optimiser. Test the query with NULLs in the filtered columns, make sure it works as intended.<\/p>\n
Consider the RECOMPILE hint first, over dynamic SQL, to make it perform well. If the query has long compile times or runs very frequently, then use dynamic SQL, but don\u2019t automatically discount the recompile hint for fear of the overhead. In many cases it\u2019s not that bad.<\/p>\n","protected":false},"excerpt":{"rendered":"
I originally wrote about catch-all queries early in 2009, just as something that I\u2019d seen several times in client code. It turned into the 3rd most popular post ever on my blog. A lot\u2019s changed since 2009. When I wrote… (Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"New blog post: Revisiting catch-all queries","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[25,15,16,17],"tags":[],"class_list":["post-2106","post","type-post","status-publish","format-standard","hentry","category-performance","category-sql-server","category-syndication","category-t-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-xY","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/2106","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=2106"}],"version-history":[{"count":6,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/2106\/revisions"}],"predecessor-version":[{"id":2220,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/2106\/revisions\/2220"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=2106"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=2106"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=2106"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}