{"id":36205,"date":"2020-04-21T11:17:57","date_gmt":"2020-04-21T18:17:57","guid":{"rendered":"https:\/\/sqlespresso.com\/?p=2231"},"modified":"2020-04-21T11:17:57","modified_gmt":"2020-04-21T18:17:57","slug":"coding-standards-gone-bad-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.dcac.com\/2020\/04\/21\/coding-standards-gone-bad-in-sql-server\/","title":{"rendered":"Coding Standards Gone Bad in SQL Server"},"content":{"rendered":"

Knowing your data is very important when it comes to writing code. Now I\u2019ll admit that I am very far from being a developer, however as a DBA, I spend much of my day\u2019s performance tuning code. In doing so, I get to see many ways code can introduce excess database reads.,<\/p>\n

One of the most recent things I have come across has to do with NULLs. The environment I was working in had no default values in their table design, so it was riddled with NULL values. Over the years they had implemented coding standards to try and mitigate these NULL values within their code.<\/p>\n

In every column search or join they would start with (VALUE ISNULL or VALUE IS NOT NULL and VALUE = @parameter) as one example. Adding this syntax to a query reduces SARGAbility causing a lot of unnecessary reads and overhead. This is especially true when the column definition is set to NOT NULL and the check was just put in place as part of a standard. For clarification SARGAbility just means searchability and the ability to seek within an index for faster performance.<\/p>\n

Another practice I\u2019ve seen lately is the use of LTRIM and RTRIM functions as a standard clean up method. \u00a0First, I highly recommend doing this on the application side or upon whatever insert method you use as the data is being populated into your tables. \u00a0It is a much better practice to clean up the data once rather than coding for the clean up in every WHERE, JOIN or CASE statement in each query you write.<\/p>\n

Let\u2019s look at a simple query that incorporates both of these methods. Note: we will be turning on STATISTICS IO, TIME to look at our performance improvements.<\/p>\n

First let\u2019s look at our table design and note a few things. We will be querying WorldWideImporters Sales.Invoices table using SalesPersonID which is an INT set to NOT NULL, IsCreditNote a BIT NOT NULL and ConfirmedRecievedBy which is a nvarchar(4000) that allows NULLs.<\/p>\n

\"\"<\/a><\/p>\n

After looking at our table let\u2019s take note of the WHERE clause. The first check is validating if IsCreditNote<\/em> IS NOT NULL, then we check on SalespersonPersonID<\/em> for a specific value and lastly, we a cleaning up our free form field ConfirmedRecievedBy<\/em> for a specific person\u2019s name.<\/p>

SET STATISTICS IO, TIME ON\r\n\r\nSELECT BillToCustomerID, OrderID, InvoiceDate\r\n\r\n\u00a0 FROM [WideWorldImporters].[Sales].[Invoices]\r\n\r\n\u00a0 WHERE IsCreditNote IS NOT NULL\u00a0\r\n\r\n\u00a0 AND SalespersonPersonID = 7\r\n\r\n\u00a0 AND LTRIM(RTRIM(ConfirmedReceivedBy)) ='Aile Mae' ;<\/pre>
Let\u2019s run this and see what the Stats and Query plans shows us.<\/p>\n
\"\"<\/a><\/p>\n
As I do with any performance tuning the first things that I note is CPU time and elapsed time then Ill note table scans and reads. We will use these metrics to measure our improvements. I would like you to take note of the tables it uses to complete the query. There is the Invoices table along with a Workfile and a Worktable it needed to use in order to return the desired results.<\/p>\n
Next let\u2019s look at our execution plan and note our seeks and scans. You can clearly see we are getting, and Index seek on the Primary Key as well as a Scan on our index for ConfirmedRecievedBy<\/em>. It\u2019s also letting us know that we have a missing index but let\u2019s ignore that for now.<\/p>\n
\"\"<\/a><\/p>\n
Now that we have some performance data, let\u2019s look at some changes we can implement to make it behave better.<\/p>\n
Since we looked at our table design, we can comment out the IsCreditNote IS NOT NULL check simply because does not allow NULL values so no need to check for them.<\/p>
SELECT BillToCustomerID, OrderID, InvoiceDate\r\n\r\n\u00a0 FROM [WideWorldImporters].[Sales].[Invoices]\r\n\r\n\u00a0 WHERE --IsCreditNote IS NOT NULL\u00a0\r\n\r\n\u00a0 --AND\r\n\r\n\u00a0 SalespersonPersonID = 7\r\n\r\n\u00a0 AND LTRIM(RTRIM(ConfirmedReceivedBy)) ='Aile Mae' ;<\/pre>
Executing the query again, you\u2019ll note that the plan remains the same, but we see a significant reduction in CPU time and elapsed time just by removing unnecessary checks.<\/p>\n
\"\"<\/a><\/p>\n
\"\"<\/a><\/p>\n
Finally, we will execute the query again this time assuming our application managed the cleanup of our values prior to insertion for the ConfirmedRecievedBy<\/em> column. We will remove the LTRIM & RTRIM functions allowing the optimizer to seek on our index instead of scanning all the values.<\/p>
SELECT BillToCustomerID, OrderID, InvoiceDate\r\n\r\n\u00a0 FROM [WideWorldImporters].[Sales].[Invoices]\r\n\r\n\u00a0 WHERE --IsCreditNote IS NOT NULL\u00a0\r\n\r\n\u00a0-- AND\r\n\r\n\u00a0 SalespersonPersonID = 7\r\n\r\n--AND LTRIM(RTRIM(ConfirmedReceivedBy)) ='Aile Mae' ;\r\n\r\n\u00a0AND ConfirmedReceivedBy ='Aile Mae';<\/pre>
Now, you can see a huge improvement. We now are only doing 2 scans with 50 logical reads verses 422 and CPU time is now 0 compared 47ms. Our elapsed time even dropped from 230 to 127 which is over 100ms less. Furthermore, note that the missing index suggestion is now gone. It is important that you don\u2019t just add indexes because the optimizer suggests it, wait until you are done tuning your code before addressing you index needs. Lastly note by making these changes the optimizer no longer requires the use of worktables nor workfiles.<\/p>\n
\"\"<\/a><\/p>\n
\"\"<\/a><\/p>\n
There is still one last thing we can do to this to improve performance, which is out of the scope and purpose of this blog but let\u2019s do it anyway. That is get rid of the Key Lookup. You can read more about what those are in my blog here<\/a>. After adding the required included columns, you can see how much cleaner and faster the simple query now runs. We now run with only 20 logical reads, 0 CPU time and 90ms elapsed timed. That’s a 264.44% performance improvement.<\/p>\n
\"\"<\/a><\/p>\n
\"\"<\/a><\/p>\n
This was a simple tuning exercise to show how implementing coding standards without making sure they are applicable to your data can cost you performance. As a developer you should always know your data. What works with one set of data may not work with another, it is very important to know the differences and code for those.<\/p>\n","protected":false},"excerpt":{"rendered":"
Knowing your data is very important when it comes to writing code. Now I’ll admit that I am very far from being a developer, however as a DBA, I spend much of my day’s performance tuning code. In doing so, I get to see many ways code can introduce excess database reads., One of the most recent things I have come across has to do with NULLs. The environment I was working in had no default values in their table design, so it was riddled with NULL values. Over the years they had implemented coding standards to try and mitigate… Continue Reading<\/a><\/p>\n","protected":false},"author":52,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[551,106,412,49],"tags":[],"class_list":["post-36205","post","type-post","status-publish","format-standard","hentry","category-blog","category-indexes","category-performance-tuning","category-sql-server"],"yoast_head":"\nCoding Standards Gone Bad in SQL Server – Denny Cherry & Associates Consulting<\/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.dcac.com\/2020\/04\/21\/coding-standards-gone-bad-in-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Coding Standards Gone Bad in SQL Server – Denny Cherry & Associates Consulting\" \/>\n<meta property=\"og:description\" content=\"Knowing your data is very important when it comes to writing code. Now I’ll admit that I am very far from being a developer, however as a DBA, I spend much of my day’s performance tuning code. In doing so, I get to see many ways code can introduce excess database reads., One of the most recent things I have come across has to do with NULLs. The environment I was working in had no default values in their table design, so it was riddled with NULL values. Over the years they had implemented coding standards to try and mitigate… Continue Reading\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dcac.com\/2020\/04\/21\/coding-standards-gone-bad-in-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"Denny Cherry & Associates Consulting\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DennyCherryAssociatesConsulting\/\" \/>\n<meta property=\"article:published_time\" content=\"2020-04-21T18:17:57+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/i0.wp.com\/sqlespresso.com\/wp-content\/uploads\/2020\/04\/Wrong1.png?resize=399%2C489&ssl=1\" \/>\n<meta name=\"author\" content=\"Monica Rathbun\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dcacco\" \/>\n<meta name=\"twitter:site\" content=\"@dcacco\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Monica Rathbun\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dcac.com\/2020\/04\/21\/coding-standards-gone-bad-in-sql-server\/\",\"url\":\"https:\/\/www.dcac.com\/2020\/04\/21\/coding-standards-gone-bad-in-sql-server\/\",\"name\":\"Coding Standards Gone Bad in SQL Server – Denny Cherry & Associates Consulting\",\"isPartOf\":{\"@id\":\"https:\/\/www.dcac.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dcac.com\/2020\/04\/21\/coding-standards-gone-bad-in-sql-server\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dcac.com\/2020\/04\/21\/coding-standards-gone-bad-in-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/i0.wp.com\/sqlespresso.com\/wp-content\/uploads\/2020\/04\/Wrong1.png?resize=399%2C489&ssl=1\",\"datePublished\":\"2020-04-21T18:17:57+00:00\",\"dateModified\":\"2020-04-21T18:17:57+00:00\",\"author\":{\"@id\":\"https:\/\/www.dcac.com\/#\/schema\/person\/5f9dfc9f9dd0cdc69d5e4e13f62e01f3\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dcac.com\/2020\/04\/21\/coding-standards-gone-bad-in-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dcac.com\/2020\/04\/21\/coding-standards-gone-bad-in-sql-server\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dcac.com\/2020\/04\/21\/coding-standards-gone-bad-in-sql-server\/#primaryimage\",\"url\":\"https:\/\/i0.wp.com\/sqlespresso.com\/wp-content\/uploads\/2020\/04\/Wrong1.png?resize=399%2C489&ssl=1\",\"contentUrl\":\"https:\/\/i0.wp.com\/sqlespresso.com\/wp-content\/uploads\/2020\/04\/Wrong1.png?resize=399%2C489&ssl=1\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dcac.com\/2020\/04\/21\/coding-standards-gone-bad-in-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.dcac.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Coding Standards Gone Bad in SQL Server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dcac.com\/#website\",\"url\":\"https:\/\/www.dcac.com\/\",\"name\":\"Denny Cherry & Associates Consulting\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dcac.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dcac.com\/#\/schema\/person\/5f9dfc9f9dd0cdc69d5e4e13f62e01f3\",\"name\":\"Monica Rathbun\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dcac.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/9be6a59a1612fbf4185a3bdd473b296a?s=96&r=x\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/9be6a59a1612fbf4185a3bdd473b296a?s=96&r=x\",\"caption\":\"Monica Rathbun\"},\"url\":\"https:\/\/www.dcac.com\/author\/monicadcac-co\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Coding Standards Gone Bad in SQL Server – Denny Cherry & Associates Consulting","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.dcac.com\/2020\/04\/21\/coding-standards-gone-bad-in-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"Coding Standards Gone Bad in SQL Server – Denny Cherry & Associates Consulting","og_description":"Knowing your data is very important when it comes to writing code. Now I’ll admit that I am very far from being a developer, however as a DBA, I spend much of my day’s performance tuning code. In doing so, I get to see many ways code can introduce excess database reads., One of the most recent things I have come across has to do with NULLs. The environment I was working in had no default values in their table design, so it was riddled with NULL values. Over the years they had implemented coding standards to try and mitigate… Continue Reading","og_url":"https:\/\/www.dcac.com\/2020\/04\/21\/coding-standards-gone-bad-in-sql-server\/","og_site_name":"Denny Cherry & Associates Consulting","article_publisher":"https:\/\/www.facebook.com\/DennyCherryAssociatesConsulting\/","article_published_time":"2020-04-21T18:17:57+00:00","og_image":[{"url":"https:\/\/i0.wp.com\/sqlespresso.com\/wp-content\/uploads\/2020\/04\/Wrong1.png?resize=399%2C489&ssl=1","type":"","width":"","height":""}],"author":"Monica Rathbun","twitter_card":"summary_large_image","twitter_creator":"@dcacco","twitter_site":"@dcacco","twitter_misc":{"Written by":"Monica Rathbun","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.dcac.com\/2020\/04\/21\/coding-standards-gone-bad-in-sql-server\/","url":"https:\/\/www.dcac.com\/2020\/04\/21\/coding-standards-gone-bad-in-sql-server\/","name":"Coding Standards Gone Bad in SQL Server – Denny Cherry & Associates Consulting","isPartOf":{"@id":"https:\/\/www.dcac.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dcac.com\/2020\/04\/21\/coding-standards-gone-bad-in-sql-server\/#primaryimage"},"image":{"@id":"https:\/\/www.dcac.com\/2020\/04\/21\/coding-standards-gone-bad-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/i0.wp.com\/sqlespresso.com\/wp-content\/uploads\/2020\/04\/Wrong1.png?resize=399%2C489&ssl=1","datePublished":"2020-04-21T18:17:57+00:00","dateModified":"2020-04-21T18:17:57+00:00","author":{"@id":"https:\/\/www.dcac.com\/#\/schema\/person\/5f9dfc9f9dd0cdc69d5e4e13f62e01f3"},"breadcrumb":{"@id":"https:\/\/www.dcac.com\/2020\/04\/21\/coding-standards-gone-bad-in-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dcac.com\/2020\/04\/21\/coding-standards-gone-bad-in-sql-server\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dcac.com\/2020\/04\/21\/coding-standards-gone-bad-in-sql-server\/#primaryimage","url":"https:\/\/i0.wp.com\/sqlespresso.com\/wp-content\/uploads\/2020\/04\/Wrong1.png?resize=399%2C489&ssl=1","contentUrl":"https:\/\/i0.wp.com\/sqlespresso.com\/wp-content\/uploads\/2020\/04\/Wrong1.png?resize=399%2C489&ssl=1"},{"@type":"BreadcrumbList","@id":"https:\/\/www.dcac.com\/2020\/04\/21\/coding-standards-gone-bad-in-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dcac.com\/"},{"@type":"ListItem","position":2,"name":"Coding Standards Gone Bad in SQL Server"}]},{"@type":"WebSite","@id":"https:\/\/www.dcac.com\/#website","url":"https:\/\/www.dcac.com\/","name":"Denny Cherry & Associates Consulting","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dcac.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dcac.com\/#\/schema\/person\/5f9dfc9f9dd0cdc69d5e4e13f62e01f3","name":"Monica Rathbun","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dcac.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/9be6a59a1612fbf4185a3bdd473b296a?s=96&r=x","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/9be6a59a1612fbf4185a3bdd473b296a?s=96&r=x","caption":"Monica Rathbun"},"url":"https:\/\/www.dcac.com\/author\/monicadcac-co\/"}]}},"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/posts\/36205","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/users\/52"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/comments?post=36205"}],"version-history":[{"count":32,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/posts\/36205\/revisions"}],"predecessor-version":[{"id":41719,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/posts\/36205\/revisions\/41719"}],"wp:attachment":[{"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/media?parent=36205"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/categories?post=36205"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/tags?post=36205"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}