{"id":1796,"date":"2016-02-02T16:30:27","date_gmt":"2016-02-02T14:30:27","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=1796"},"modified":"2016-01-23T11:15:29","modified_gmt":"2016-01-23T09:15:29","slug":"on-the-addition-of-useless-where-clauses","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2016\/02\/02\/on-the-addition-of-useless-where-clauses\/","title":{"rendered":"On the addition of useless where clauses"},"content":{"rendered":"

I remember a forum thread from a while back. The question was on how to get rid of the index scan that was in the query plan. Now that\u2019s a poor question in the first place, as the scan might not be a problem, but it\u2019s the first answer that really caught my attention.<\/p>\n

Since the primary key is on an identity column, you can add a clause like ID > 0 to the query, then SQL will use an index seek.<\/p><\/blockquote>\n

Technically that\u2019s correct. If the table has an identity column with the default properties (We\u2019ll call it ID) and the clustered index is on that identity column, then a WHERE clause of the form WHERE ID > 0 AND <any other predicates on that table> can indeed execute with a clustered index seek (although it\u2019s in no way guaranteed to do so). But is it a useful thing to do?<\/p>\n

Time for a made up table and a test query.<\/p>\n

CREATE TABLE dbo.Orders(\r\n  OrderID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,\r\n  OrderDate DATETIME2(7) NULL,\r\n  ShipmentRef CHAR(10) NULL,\r\n  ShipmentDate DATE NULL,\r\n  Status VARCHAR(20) NOT NULL\r\n);<\/pre>\n
That\u2019ll do the job. And then a few hundred thousand rows via SQL Data Generator and we\u2019re good to go.<\/p>\n
And for a query that has a nasty index scan, how about<\/p>\n
SELECT\u00a0 OrderDate,\r\n    ShipmentDate,\r\n    Status\r\n  FROM\u00a0\u00a0\u00a0 dbo.Orders\r\n  WHERE\u00a0\u00a0 LTRIM(RTRIM(Status)) = 'Delivered';<\/pre>\n
\"ClusteredIndexScan\"<\/a><\/p>\n
Now, that\u2019s running as a clustered index scan because the predicate\u2019s not SARGable and besides, there\u2019s no index on that column, but let\u2019s pretend we don\u2019t know that.<\/p>\n
If I add a WHERE clause predicate that filters no row out, can I get a query plan with an index seek?<\/p>\n
SELECT\u00a0 OrderDate,\r\n    ShipmentDate,\r\n    Status\r\n  FROM\u00a0\u00a0\u00a0 dbo.Orders\r\n  WHERE\u00a0\u00a0 LTRIM(RTRIM(Status)) = 'Delivered'\r\n    AND OrderID > 0;<\/pre>\n
Why yes, I can.<\/p>\n
\"ClusteredIndexSeek\"<\/a><\/p>\n
Op Success? Well\u2026<\/p>\n
The goal of performance tuning is to improve the performance of a query, not to change operators in a query plan. The plan is a tool, not a goal.<\/p>\n
Have we, by adding a WHERE clause predicate that filters out no rows, improved performance of the query? This needs an extended events session to answer. Nothing fancy, just a sql_statement_completed event will do the trick.<\/p>\n
I ran each query 10 times, copied the captured events into Excel and averaged them:<\/p>\n
Query with just the LTRIM(RTRIM(Status)) = ‘Delivered’
\nCPU: 77ms
\nDuration: 543ms<\/p>\n
Query with LTRIM(RTRIM(Status)) = ‘Delivered’ AND OrderID > 0
\nCPU: 80ms
\nDuration: 550ms<\/p>\n
We haven\u2019t tuned that query. I won\u2019t say we\u2019ve made it slower either, the differences are well within the error range on our measuring, but there\u2019s definitely no meaningful performance gain.<\/p>\n
There\u2019s no gain because we haven\u2019t changed how the query executes. A scan, and in this case it will be a scan of the entire index, will likely use the non-leaf levels of the b-tree to locate the logical first page of the leaf level, then will read the entire leaf level. The seek we managed to generate will use the b-tree to find the value 0 in the clustered index key, that\u2019s what makes it a seek. Since the column is an identity starting at 1, that means the first row read will be on the logical first page of the leaf level, then it will read the entire leaf level.<\/p>\n
Both will do the same amount of work, and so we haven\u2019t done anything useful to the query by adding a WHERE clause that filters out no rows.<\/p>\n
Scans are not always bad. If a query needs to read every row of a table, that\u2019s a scan and effort shouldn\u2019t be expended trying to make it an index seek.<\/p>\n
To improve the performance of a query, we need to make changes that reduce the work needed to run the query. That often starts with reducing the amount of data that the query reads, by changing the query so that it can use indexes effectively and\/or adding indexes to support the query. Not by adding pointless pieces to a query just to change plan operators from ones that are believed to be bad to ones that are believed to be good. Doing that is just a waste of time and effort.<\/p>\n","protected":false},"excerpt":{"rendered":"
I remember a forum thread from a while back. The question was on how to get rid of the index scan that was in the query plan. Now that\u2019s a poor question in the first place, as the scan might… (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":"On the addition of useless where clauses","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-1796","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-sY","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1796","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=1796"}],"version-history":[{"count":6,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1796\/revisions"}],"predecessor-version":[{"id":1802,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1796\/revisions\/1802"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=1796"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=1796"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=1796"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}