{"id":1808,"date":"2016-02-09T16:30:42","date_gmt":"2016-02-09T14:30:42","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=1808"},"modified":"2016-02-09T22:04:07","modified_gmt":"2016-02-09T20:04:07","slug":"and-other-pointless-query-rewrites","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2016\/02\/09\/and-other-pointless-query-rewrites\/","title":{"rendered":"and other pointless query rewrites"},"content":{"rendered":"

In a similar vein to last week\u2019s blog post\u2026 I heard an interesting comment recently. \u201cChange that Column != 2 to a Column > 2 or Column < 2 combination, it can use indexes better.\u201d<\/p>\n

Sounds like something that clearly needs testing!<\/p>\n

I\u2019ll start with simple numbers table.<\/p>\n

CREATE TABLE dbo.Numbers (\r\n  Number INT NOT NULL\r\n);\r\n\r\nALTER TABLE Numbers ADD CONSTRAINT PK_Numbers\r\n  PRIMARY KEY CLUSTERED (Number);<\/pre>\n
and put 1 million rows into it<\/p>\n
INSERT INTO dbo.Numbers (Number)\r\nSELECT TOP (1000000) ROW_NUMBER() OVER (ORDER BY (SELECT 1))\r\n  FROM msdb.sys.columns c CROSS JOIN msdb.sys.columns c2;<\/pre>\n
Let\u2019s start, before we get into comparing things, with looking at the execution plan of a query with a != (or <>) operator.<\/p>\n
SELECT Number\r\n  FROM Numbers\r\n  WHERE Number <> 12742; -- because 2 is on the first page of the index, and I don\u2019t want any special cases here<\/pre>\n
\"InequalityExecutionPlan\"<\/a><\/p>\n
That\u2019s kinda complicated for a query with one table and one predicate. Let\u2019s look at in pieces. The easiest place to start is the Clustered Index Seek. The seek predicate on the clustered index seek is<\/p>\n
Seek Keys[1]: Start: [Test].[dbo].[Numbers].Number > Scalar Operator([Expr1009]), End: [Test].[dbo].[Numbers].Number < Scalar Operator([Expr1010])<\/p><\/blockquote>\n
Hmm\u2026Looks like the parser\/optimiser has already made our intended change for us. There\u2019s some funky stuff in the top part of the plan, but what it\u2019s essentially doing is generating two rows for the nested loop join, both with just the value that we\u2019re excluding from the query, then the seek runs twice. I suspect that\u2019s once for the less than 12742 and once for the greater than 12742 portions of the original predicate.<\/p>\n
But, let\u2019s do the full due diligence, the plan may not tell the whole story.<\/p>\n
The performance numbers for the inequality form of the query, gathered via Extended Events and aggregated with Excel are:<\/p>\n
Duration 122ms
\nCPU 105ms
\nLogical reads: 1619<\/p>\n
This is our baseline, the numbers we\u2019re comparing against. If the comment mentioned at the beginning is correct, then the revised query will have a significantly better performance.<\/p>\n
The revised query is:<\/p>\n
SELECT Number\r\n  FROM Numbers\r\n  WHERE Number > 12742 OR Number < 12742;<\/pre>\n
Execution plan is much simpler, no constant scans, no joins. Just a single index seek operation that executes once.<\/p>\n
\"InequalityRevised\"<\/a><\/p>\n
Is is better though?<\/p>\n
Duration: 126ms
\nCPU: 103ms
\nLogical reads: 1619<\/p>\n
No, it\u2019s not.<\/p>\n
Yes, we have a simpler plan, but we do not have a more efficient query. We\u2019re still reading every page in the index and fetching all but one row of the table. The work required is the same, the performance characteristics are the same.<\/p>\n
But, maybe, if the numbers aren’t unique and we’re excluding more than just one row it’ll be different.<\/p>\n
That needs a slightly different table to test on.<\/p>\n
CREATE TABLE MoreNumbers (\r\nSomeNumber INT NOT NULL,\r\nFiller CHAR(100)\r\n);\r\n\r\nCREATE CLUSTERED INDEX idx_MoreNumbers ON dbo.MoreNumbers(SomeNumber);\r\nGO\r\n\r\nINSERT INTO dbo.MoreNumbers (SomeNumber, Filler)\r\nSELECT TOP (500000) NTILE(50) OVER (ORDER BY (SELECT 1)), ''\r\nFROM msdb.sys.columns c CROSS JOIN msdb.sys.columns c2;<\/pre>\n
I’m just going to look at the performance characteristics this time. The execution plans are the same as for the earlier query. The two queries are:<\/p>\n
SELECT * FROM dbo.MoreNumbers WHERE SomeNumber != 24;\r\nSELECT * FROM dbo.MoreNumbers WHERE SomeNumber < 24 OR SomeNumber > 24;<\/pre>\n
Query 1:<\/p>\n
Duration 97ms
\nCPU 77ms
\nLogical Reads 7624<\/p>\n
Query 2:<\/p>\n
Duration 98ms
\nCPU 75ms
\nLogical Reads 7624<\/p>\n
Again identical.<\/p>\n
Just like with the pointless WHERE clause predicate<\/a> last week, we have a query change that has had no effect on the query performance. Now, to be honest, there are some query form changes that can improve performance. For example, converting a set of OR predicates to UNION can improve query performance sometimes (and leave it unchanged in others), and so these kinds of rewrites do need to be tested to see if they\u2019re useful.<\/p>\n
More importantly though, those of us who are posting on forums and advising others have a responsibility to do these tests before we recommend changes to others, as they may very well not do them. If we don\u2019t, we\u2019re propagating myths and not helping the advancement of our field.<\/p>\n","protected":false},"excerpt":{"rendered":"
In a similar vein to last week\u2019s blog post\u2026 I heard an interesting comment recently. \u201cChange that Column != 2 to a Column > 2 or Column < 2 combination, it can use indexes better.\u201d Sounds like something that clearly… (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: and other pointless query rewrites","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-1808","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-ta","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1808","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=1808"}],"version-history":[{"count":17,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1808\/revisions"}],"predecessor-version":[{"id":1826,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1808\/revisions\/1826"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=1808"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=1808"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=1808"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}