{"id":4615,"date":"2016-05-05T07:25:40","date_gmt":"2016-05-05T14:25:40","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4615"},"modified":"2019-08-21T14:47:29","modified_gmt":"2019-08-21T21:47:29","slug":"reconciling-set-based-operations-with-row-by-row-iterative-processing","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/reconciling-set-based-operations-with-row-by-row-iterative-processing\/","title":{"rendered":"Reconciling set-based operations with row-by-row iterative processing"},"content":{"rendered":"<p style=\"text-align: justify;\">Yesterday in class we had a discussion around the conceptual problem of reconciling the fact that SQL Server does set-based operations, but that it does them in query plans that pass single rows around between operators. In other words, it uses iterative processing to implement set-based operators.<\/p>\n<p style=\"text-align: justify;\">The crux of the discussion is: if SQL Server is passing single rows around, how is that set-based operations?<\/p>\n<p style=\"text-align: justify;\">[Edit 8\/21\/19 &#8211; what about batch mode operations? See the end of the post for details.]<\/p>\n<p style=\"text-align: justify;\">I explained it in two different ways\u2026<\/p>\n<h2 style=\"text-align: justify;\">SQL Server Example<\/h2>\n<p style=\"text-align: justify;\">This explanation compares two ways of doing the following logical operation using SQL Server: update all the rows in the <em>Products<\/em> table where <em>ProductType<\/em>\u00a0= 1 and set the <em>Price<\/em> field to be 10% higher.<\/p>\n<p style=\"text-align: justify;\">The cursor based way (row-by-agonizing-row, or RBAR) would be something like the following:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nDECLARE @ProductID   INT;\r\nDECLARE @Price       FLOAT;\r\n\r\nDECLARE &#x5B;MyUpdate] CURSOR FAST_FORWARD FOR\r\nSELECT &#x5B;ProductID], &#x5B;Price]\r\nFROM &#x5B;Products]\r\nWHERE &#x5B;ProductType] = 1;\r\n\r\nOPEN &#x5B;MyUpdate];\r\n\r\nFETCH NEXT FROM &#x5B;MyUpdate] INTO @ProductID, @Price;\r\n\r\nWHILE @@FETCH_STATUS = 0\r\nBEGIN\r\n    UPDATE &#x5B;Products]\r\n    SET &#x5B;Price] = @Price * 1.1\r\n    WHERE &#x5B;ProductID] = @ProductID;\r\n\r\n    FETCH NEXT FROM &#x5B;MyUpdate] INTO @ProductID, @Price;\r\nEND\r\n\r\nCLOSE &#x5B;MyUpdate];\r\nDEALLOCATE &#x5B;MyUpdate];\r\n<\/pre>\n<p style=\"text-align: justify;\">This method has to set up a scan over the <em>Products<\/em> table based on the <em>ProductType<\/em>, and then runs a separate <em>UPDATE<\/em>\u00a0transaction for each row returned from the scan, incurring all the overhead of setting up the <em>UPDATE\u00a0<\/em>query, starting the transaction, seeking to the correct row based on the <em>ProductID<\/em>, updating it, and tearing down the transaction and query framework\u00a0again each time.<\/p>\n<p style=\"text-align: justify;\">The set-based way of doing it would be:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nUPDATE &#x5B;Products]\r\nSET &#x5B;Price] = &#x5B;Price] * 1.1\r\nWHERE &#x5B;ProductType] = 1;\r\n<\/pre>\n<p style=\"text-align: justify;\">This will have one scan based on the <em>ProductType<\/em>, which will update\u00a0rows matching the <em>ProductType<\/em>, but the query, transaction, and scan are only set up once, and then all the rows are processed, one-at-a-time inside SQL Server.<\/p>\n<p style=\"text-align: justify;\">The difference is that in the set-based way, all the iteration is done inside SQL Server, in the most efficient way it can, rather than manually iterating outside of SQL Server using the cursor.<\/p>\n<h2 style=\"text-align: justify;\">Non-Technical Example<\/h2>\n<p style=\"text-align: justify;\">This explanation involves a similar problem but not involving SQL Server. Imagine you need to acquire twelve 4&#8242; x 8&#8242;\u00a0plywood sheets from your local home improvement store.<\/p>\n<p style=\"text-align: justify;\">You could drive to and from the store twelve\u00a0times, and each time you need to go into the store, purchase the sheet, and wait for a staff member to become available to load\u00a0the\u00a0sheet into your pickup truck, then drive home and unload the sheet.<\/p>\n<p style=\"text-align: justify;\">Or you could drive to the store once and purchase\u00a0all twelve\u00a0sheets in one go, with maybe four staff members making three trips each out to your pickup, carrying one sheet each time. Or even just one staff member making twelve\u00a0trips out to your pickup.<\/p>\n<p style=\"text-align: justify;\">Which method is\u00a0more efficient? Multiple trips to the store or one trip to the store, no matter how many staff members are available to carry the sheets out?<\/p>\n<h2 style=\"text-align: justify;\">Summary<\/h2>\n<p style=\"text-align: justify;\">No-one in their right mind is going to make twelve trips to the home improvement store when one will suffice. Just like no developer should be writing cursor\/RBAR code to perform an operation that SQL Server can do in a set-based manner (when possible).<\/p>\n<p style=\"text-align: justify;\">Set-based operations don\u2019t mean that SQL Server processes the whole set at once \u2013 that\u2019s clearly not possible as most sets have more rows than your server has processors (so all the rows in the set simply *can\u2019t* be processed at the same time, even if all processors were running the same code at the same time) \u2013 but that it can process the set very, very efficiently by only constructing the processing framework (i.e. query plan with operators, scans, etc.) for the operation once and then iterating over the set of rows inside this framework.<\/p>\n<p style=\"text-align: justify;\">PS Check out the technical comment from Conor Cunningham below (Architect on the SQL Server team, and my counterpart on the Query Optimizer when I was a Dev Lead in the Storage Engine for SQL Server 2005)<\/p>\n<p style=\"text-align: justify;\">[Edit 8\/21\/19 PPS What about batch mode operations? These are done using vector-based CPU instructions working on a\u00a0vector of column values (multiple rows from a column) instead of a single row, and going vector by vector instead of row by row. So it&#8217;s still doing one operation at a time, but it&#8217;s a bigger, more efficient operation. Plus there are algorithms used that are optimized for multi-core CPUs and better leverage processor caches than row-based algorithms. Bottom line: batch-mode is much more efficient than row-mode, but still has to go piece-by-piece.]<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Yesterday in class we had a discussion around the conceptual problem of reconciling the fact that SQL Server does set-based operations, but that it does them in query plans that pass single rows around between operators. In other words, it uses iterative processing to implement set-based operators. The crux of the discussion is: if SQL [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[72],"tags":[],"class_list":["post-4615","post","type-post","status-publish","format-standard","hentry","category-query-processor"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Reconciling set-based operations with row-by-row iterative processing - Paul S. Randal<\/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\/paul\/reconciling-set-based-operations-with-row-by-row-iterative-processing\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Reconciling set-based operations with row-by-row iterative processing - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"Yesterday in class we had a discussion around the conceptual problem of reconciling the fact that SQL Server does set-based operations, but that it does them in query plans that pass single rows around between operators. In other words, it uses iterative processing to implement set-based operators. The crux of the discussion is: if SQL [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/reconciling-set-based-operations-with-row-by-row-iterative-processing\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2016-05-05T14:25:40+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-08-21T21:47:29+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/reconciling-set-based-operations-with-row-by-row-iterative-processing\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/reconciling-set-based-operations-with-row-by-row-iterative-processing\/\",\"name\":\"Reconciling set-based operations with row-by-row iterative processing - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2016-05-05T14:25:40+00:00\",\"dateModified\":\"2019-08-21T21:47:29+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/reconciling-set-based-operations-with-row-by-row-iterative-processing\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/reconciling-set-based-operations-with-row-by-row-iterative-processing\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/reconciling-set-based-operations-with-row-by-row-iterative-processing\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Reconciling set-based operations with row-by-row iterative processing\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Reconciling set-based operations with row-by-row iterative processing - Paul S. Randal","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\/paul\/reconciling-set-based-operations-with-row-by-row-iterative-processing\/","og_locale":"en_US","og_type":"article","og_title":"Reconciling set-based operations with row-by-row iterative processing - Paul S. Randal","og_description":"Yesterday in class we had a discussion around the conceptual problem of reconciling the fact that SQL Server does set-based operations, but that it does them in query plans that pass single rows around between operators. In other words, it uses iterative processing to implement set-based operators. The crux of the discussion is: if SQL [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/reconciling-set-based-operations-with-row-by-row-iterative-processing\/","og_site_name":"Paul S. Randal","article_published_time":"2016-05-05T14:25:40+00:00","article_modified_time":"2019-08-21T21:47:29+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/reconciling-set-based-operations-with-row-by-row-iterative-processing\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/reconciling-set-based-operations-with-row-by-row-iterative-processing\/","name":"Reconciling set-based operations with row-by-row iterative processing - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2016-05-05T14:25:40+00:00","dateModified":"2019-08-21T21:47:29+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/reconciling-set-based-operations-with-row-by-row-iterative-processing\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/reconciling-set-based-operations-with-row-by-row-iterative-processing\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/reconciling-set-based-operations-with-row-by-row-iterative-processing\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Reconciling set-based operations with row-by-row iterative processing"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4615","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=4615"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4615\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4615"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4615"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4615"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}