{"id":988,"date":"2011-04-05T18:30:00","date_gmt":"2011-04-05T16:30:00","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=988"},"modified":"2011-03-25T17:39:04","modified_gmt":"2011-03-25T15:39:04","slug":"to-top-or-not-to-top-an-exists","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2011\/04\/05\/to-top-or-not-to-top-an-exists\/","title":{"rendered":"To TOP or not to TOP an EXISTS"},"content":{"rendered":"

Earlier this year I had a look at a query pattern that I often see on forums and in production code, that of the Distinct within an IN subquery. Today I’m going to look at a similar patters, that being the use of TOP 1 within an EXISTS subquery.<\/p>\n

Three tests. First a straightforward exists with no correlation (no where clause linking it to an outer query). Second, an exists with a complex query (one with a non-sargable where clause and a group by and having). Third an exists subquery correlated to the outer query.<\/p>\n

Table structures are nice and simple, in fact, for ease I’m going to use the same tables as I did back on the exists, in and inner join tests. Code to create and populate the tables it attached to the end of the post.<\/p>\n

First up, a simple exists query, in an IF, just to be different.<\/p>\n

IF EXISTS (SELECT 1 FROM PrimaryTable_Medium)\r\nPRINT 'Exists'\r\n\r\nIF EXISTS (SELECT TOP (1) 1 FROM PrimaryTable_Medium)\r\nPRINT 'Exists too'<\/pre>\n
For a benchmark, a SELECT 1 FROM PrimaryTable_Medium has the following IO characteristics<\/p>\n
Table ‘PrimaryTable_Medium’. Scan count 1, logical reads 89, physical reads 0.<\/p>\n
SQL Server Execution Times:
\nCPU time = 15 ms,\u00a0 elapsed time = 510 ms.<\/p><\/blockquote>\n
Ignore the elapsed time, that’s likely mostly from displaying the records. I’m going to focus mostly on the CPU and IO.<\/p>\n
Execution plans of the two exists variations are absolutely identical.<\/p>\n
\"TopExists1\"<\/a><\/p>\n
The index operators are scans because there is no way they could be anything else, there’s no predicate so a seek is not possible. That said, it’s not a full index scan. The properties of the Index Scan show 1 row only (actual and estimated). So SQL did not read the entire index, just enough to evaluate the EXISTS, and that’s what it did in both cases. IO stats confirm that.<\/p>\n
<\/p>\n
Table ‘PrimaryTable_Medium’. Scan count 1, logical reads 2, physical reads 0.<\/p>\n
SQL Server Execution Times:
\nCPU time = 0 ms,\u00a0 elapsed time = 0 ms.
\nExists<\/p>\n
Table ‘PrimaryTable_Medium’. Scan count 1, logical reads 2, physical reads 0.<\/p>\n
SQL Server Execution Times:
\nCPU time = 0 ms,\u00a0 elapsed time = 0 ms.
\nExists too<\/p><\/blockquote>\n
Two reads in each case and a CPU time so low it’s immeasurable. A full scan of the index takes 89 reads (as shown earlier) so it should be clear that SQL read a minimal amount of data, both when the TOP was specified and when it wasn’t.<\/p>\n
On to a more complex test. Again, using EXISTS within an IF<\/p>\n
IF EXISTS (\r\nSELECT 1 FROM PrimaryTable_Medium\r\nWHERE RIGHT(SomeColumn,2) > 'HH'\r\nGROUP BY LEFT(SomeColumn,1)\r\nHAVING COUNT(*) > 1\r\n)\r\nPRINT 'Exists Again'\r\n\r\nIF EXISTS (\r\nSELECT TOP (1) 1 FROM PrimaryTable_Medium\r\nWHERE RIGHT(SomeColumn,2) > 'HH'\r\nGROUP BY LEFT(SomeColumn,1)\r\nHAVING COUNT(*) > 1\r\n)\r\nPRINT 'Still Exists'<\/pre>\n
If I run just the SELECT 1 alone, 10 rows are returned.<\/p>\n
Execution plans are a lot more complex, pretty much to be expected. They’re still identical, as are the IOs and CPU time.<\/p>\n
\"TopExists2\"<\/a><\/p>\n
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0.
\nTable ‘PrimaryTable_Medium’. Scan count 1, logical reads 89, physical reads 0..<\/p>\n
SQL Server Execution Times:
\nCPU time = 31 ms,\u00a0 elapsed time = 27 ms.
\nExists Again<\/p>\n
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0.
\nTable ‘PrimaryTable_Medium’. Scan count 1, logical reads 89, physical reads 0.<\/p>\n
SQL Server Execution Times:
\nCPU time = 31 ms,\u00a0 elapsed time = 45 ms.
\nStill Exists<\/p><\/blockquote>\n
This time the index scan was a scan of the entire index (89 pages). Because of the aggregation and the having, SQL couldn’t abort the scan once it had what it needed. All rows needed to be returned so that the aggregation and subsequent filter could be done.<\/p>\n
One last test, with an EXISTS subquery.<\/p>\n
I’m going to create a secondary table that has one 20% of the values for SomeColumn in PrimaryTable_Medium, but has each one repeated 500 times for a total of 615000 rows.<\/p>\n
SELECT\u00a0 ID ,\r\nSomeColumn\r\nFROM dbo.PrimaryTable_Medium pm\r\nWHERE EXISTS (SELECT 1 FROM dbo.Secondary s WHERE pm.SomeColumn = s.SomeColumn)\r\n\r\nSELECT\u00a0 ID ,\r\nSomeColumn\r\nFROM dbo.PrimaryTable_Medium pm\r\nWHERE EXISTS (SELECT TOP(1) 1 FROM dbo.Secondary s WHERE pm.SomeColumn = s.SomeColumn)<\/pre>\n
Again, the execution plans are absolutely identical<\/p>\n
\"TopExists3\"<\/a><\/p>\n
So, for that matter, are the execution statistics<\/p>\n
Table ‘PrimaryTable_Medium’. Scan count 1, logical reads 22, physical reads 0.
\nTable ‘Secondary’. Scan count 1, logical reads 1605, physical reads 0.<\/p>\n
SQL Server Execution Times:
\nCPU time = 171 ms,\u00a0 elapsed time = 443 ms.<\/p>\n
Table ‘PrimaryTable_Medium’. Scan count 1, logical reads 22, physical reads 0.
\nTable ‘Secondary’. Scan count 1, logical reads 1605, physical reads 0.<\/p>\n
SQL Server Execution Times:
\nCPU time = 172 ms,\u00a0 elapsed time = 387 ms.<\/p><\/blockquote>\n
So, in conclusion, is there any point in adding a TOP to an exists subquery? Does it persuade SQL to return only the minimum information needed to satisfy the Exists?<\/p>\n
No to both. The Exists operator itself tries to retrieve jus the absolute minimum of information, so the addition of TOP 1 does nothing except add 5 characters to the query size.<\/p>\n","protected":false},"excerpt":{"rendered":"
Earlier this year I had a look at a query pattern that I often see on forums and in production code, that of the Distinct within an IN subquery. Today I’m going to look at a similar patters, that being… (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":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[25,15,16],"tags":[],"class_list":["post-988","post","type-post","status-publish","format-standard","hentry","category-performance","category-sql-server","category-syndication"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-fW","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/988","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=988"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/988\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=988"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=988"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=988"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}