{"id":942,"date":"2013-02-27T15:29:46","date_gmt":"2013-02-27T23:29:46","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/joe\/?p=942"},"modified":"2013-02-27T18:12:06","modified_gmt":"2013-02-28T02:12:06","slug":"redundant-query-plan-branches","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/redundant-query-plan-branches\/","title":{"rendered":"Redundant Query Plan Branches"},"content":{"rendered":"

Consider the following \u201cbasic_member\u201d view definition from the Credit database:<\/p>\n

\r\nCREATE VIEW [dbo].[basic_member]\r\nAS\r\nSELECT\u00a0 [member].[member_no],\r\n[member].[lastname],\r\n[member].[firstname],\r\n[member].[middleinitial],\r\n[member].[street],\r\n[member].[city],\r\n[member].[state_prov],\r\n[member].[mail_code],\r\n[member].[phone_no],\r\n[member].[region_no],\r\n[member].[expr_dt],\r\n[member].[member_code]\r\nFROM\u00a0\u00a0\u00a0 [dbo].[member]\r\nWHERE\u00a0\u00a0 [member].[member_no] NOT IN (SELECT\u00a0\u00a0\u00a0 [corp_member].[member_no]\r\nFROM\u00a0\u00a0\u00a0\u00a0\u00a0 [dbo].[corp_member]);\r\n\r\nGO\r\n<\/pre>\n
A simple SELECT from this view returns 8,498 rows and has the following plan shape (and I\u2019m boxing in an \u201careas of interest\u201d via SQL Sentry Plan Explorer\u2019s rendering of the plan):<\/p>\n
\"image\"<\/a><\/p>\n
We see that the view has a predicate on member_no NOT IN the corp_member table.\u00a0 But what happens if the original report writer doesn\u2019t look at the view definition and decides they need this same predicate applied at the the view reference scope (not realizing this was already taken care of)?\u00a0 For example:<\/p>\n
\r\nSELECT\u00a0 [basic_member].[member_no],\r\n[basic_member].[lastname],\r\n[basic_member].[firstname],\r\n[basic_member].[middleinitial],\r\n[basic_member].[street],\r\n[basic_member].[city],\r\n[basic_member].[state_prov],\r\n[basic_member].[mail_code],\r\n[basic_member].[phone_no],\r\n[basic_member].[region_no],\r\n[basic_member].[expr_dt],\r\n[basic_member].[member_code]\r\nFROM\u00a0\u00a0\u00a0 [dbo].[basic_member]\r\nWHERE\u00a0\u00a0 [basic_member].[member_no] NOT IN\r\n(SELECT\u00a0\u00a0 [corp_member].[member_no]\r\nFROM\u00a0\u00a0\u00a0\u00a0 [dbo].[corp_member]);\r\n<\/pre>\n
Like the previous query against the view, we see 8,498 rows.\u00a0 But unlike the previous query, we see the following plan:<\/p>\n
\"image\"<\/a><\/p>\n
Notice the redundancy \u2013 even though the result set is identical between the two versions.\u00a0 And the tables I\u2019m using are small, but you can still see the difference in scan count and logical reads.<\/p>\n
Query Against View<\/strong><\/p>\n
Table ‘member’. Scan count 2, logical reads 305<\/span>, physical reads 2, read-ahead reads 294, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
\nTable ‘corporation’. Scan count 1, logical reads 8<\/span>, physical reads 1, read-ahead reads 6, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<\/span><\/p>\n
Query with Redundant Predicate<\/strong><\/p>\n
Table ‘member’. Scan count 3, logical reads 325<\/span>, physical reads 2, read-ahead reads 294, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
\nTable ‘corporation’. Scan count 2, logical reads 16<\/span>, physical reads 1, read-ahead reads 6, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<\/span><\/p>\n
And there is additional I\/O overhead associated with the second plan of course.\u00a0 For the Credit database, the scale is small, but imagine the difference for a very large table.<\/p>\n
You cannot always count on easily identifying redundant areas.\u00a0 The Query Optimizer may find an optimal plan in spite of the way it was written \u2013 but if you do see repeating branches in a query execution tree associated with a performance issue, you may want to explore the possibility of overlapping\/redundant logic further.<\/p>\n","protected":false},"excerpt":{"rendered":"
Consider the following \u201cbasic_member\u201d view definition from the Credit database: CREATE VIEW [dbo].[basic_member] AS SELECT\u00a0 [member].[member_no], [member].[lastname], [member].[firstname], [member].[middleinitial], [member].[street], [member].[city], [member].[state_prov], [member].[mail_code], [member].[phone_no], [member].[region_no], [member].[expr_dt], [member].[member_code] FROM\u00a0\u00a0\u00a0 [dbo].[member] WHERE\u00a0\u00a0 [member].[member_no] NOT IN (SELECT\u00a0\u00a0\u00a0 [corp_member].[member_no] FROM\u00a0\u00a0\u00a0\u00a0\u00a0 [dbo].[corp_member]); GO A simple SELECT from this view returns 8,498 rows and has the following plan shape (and I\u2019m […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22,28],"tags":[],"class_list":["post-942","post","type-post","status-publish","format-standard","hentry","category-execution-plan","category-performance"],"yoast_head":"\nRedundant Query Plan Branches - Joe Sack<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/redundant-query-plan-branches\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Redundant Query Plan Branches - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"Consider the following \u201cbasic_member\u201d view definition from the Credit database: CREATE VIEW [dbo].[basic_member] AS SELECT\u00a0 [member].[member_no], [member].[lastname], [member].[firstname], [member].[middleinitial], [member].[street], [member].[city], [member].[state_prov], [member].[mail_code], [member].[phone_no], [member].[region_no], [member].[expr_dt], [member].[member_code] FROM\u00a0\u00a0\u00a0 [dbo].[member] WHERE\u00a0\u00a0 [member].[member_no] NOT IN (SELECT\u00a0\u00a0\u00a0 [corp_member].[member_no] FROM\u00a0\u00a0\u00a0\u00a0\u00a0 [dbo].[corp_member]); GO A simple SELECT from this view returns 8,498 rows and has the following plan shape (and I\u2019m […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/redundant-query-plan-branches\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2013-02-27T23:29:46+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-02-28T02:12:06+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/02\/image_thumb4.png\" \/>\n<meta name=\"author\" content=\"Joseph Sack\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Joseph Sack\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/redundant-query-plan-branches\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/redundant-query-plan-branches\/\",\"name\":\"Redundant Query Plan Branches - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2013-02-27T23:29:46+00:00\",\"dateModified\":\"2013-02-28T02:12:06+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/redundant-query-plan-branches\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/redundant-query-plan-branches\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/redundant-query-plan-branches\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Execution Plan\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/execution-plan\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Redundant Query Plan Branches\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\",\"name\":\"Joe Sack\",\"description\":\"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\",\"name\":\"Joseph Sack\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"caption\":\"Joseph Sack\"},\"description\":\"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.\",\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/joe\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Redundant Query Plan Branches - Joe Sack","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/joe\/redundant-query-plan-branches\/","og_locale":"en_US","og_type":"article","og_title":"Redundant Query Plan Branches - Joe Sack","og_description":"Consider the following \u201cbasic_member\u201d view definition from the Credit database: CREATE VIEW [dbo].[basic_member] AS SELECT\u00a0 [member].[member_no], [member].[lastname], [member].[firstname], [member].[middleinitial], [member].[street], [member].[city], [member].[state_prov], [member].[mail_code], [member].[phone_no], [member].[region_no], [member].[expr_dt], [member].[member_code] FROM\u00a0\u00a0\u00a0 [dbo].[member] WHERE\u00a0\u00a0 [member].[member_no] NOT IN (SELECT\u00a0\u00a0\u00a0 [corp_member].[member_no] FROM\u00a0\u00a0\u00a0\u00a0\u00a0 [dbo].[corp_member]); GO A simple SELECT from this view returns 8,498 rows and has the following plan shape (and I\u2019m […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/redundant-query-plan-branches\/","og_site_name":"Joe Sack","article_published_time":"2013-02-27T23:29:46+00:00","article_modified_time":"2013-02-28T02:12:06+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/2013\/02\/image_thumb4.png"}],"author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/redundant-query-plan-branches\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/redundant-query-plan-branches\/","name":"Redundant Query Plan Branches - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2013-02-27T23:29:46+00:00","dateModified":"2013-02-28T02:12:06+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/redundant-query-plan-branches\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/redundant-query-plan-branches\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/redundant-query-plan-branches\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/"},{"@type":"ListItem","position":2,"name":"Execution Plan","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/execution-plan\/"},{"@type":"ListItem","position":3,"name":"Redundant Query Plan Branches"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/","name":"Joe Sack","description":"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648","name":"Joseph Sack","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","caption":"Joseph Sack"},"description":"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.","sameAs":["http:\/\/3.209.169.194\/blogs\/joe","https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack"],"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/942","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/comments?post=942"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/942\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=942"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=942"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=942"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}