--- title: "An optimizing experience" url: "https://osherove.com/blog/2003/12/25/an-optimizing-experience.md" canonical: "https://osherove.com/blog/2003/12/25/an-optimizing-experience.html" published: "2003-12-25T21:19:00" updated: "2003-12-25T21:19:00" tags: ["Other"] author: "Roy Osherove" description: "We recently encountered the task of optimizing a stored procedure that was taking very long to perform its business. It wasn’t that complicated but it was operating on a large table (350,000 records). The table has…" usage: "Licensed under CC BY 4.0 — reuse, quote and adapt with attribution (Roy Osherove, link to the original page)." license: "https://creativecommons.org/licenses/by/4.0/" --- # An optimizing experience By Roy Osherove December 25, 2003 In [Other](https://osherove.com/blog/category/other) **Historical article** · Originally published December 25, 2003; technical details may be outdated. We recently encountered the task of optimizing a stored procedure that was taking very long to perform its business. It wasn’t that complicated but it was operating on a large table (350,000 records). The table has indexes on the columns in the select clauses but the procedure still took about 19 seconds to do its work. The first thing I did was open the procedure up in the query analyzer and show it’s execution plan(CTRL-K) (see more about how to use this tool on the bottom). It was clear that there were two tables on which doing the selection on was taking about 89€ of the total work, so that’s where I went to work. Although I’m **not** and SQL guru, I do _know people_ who know their stuff when it comes to these things. Optimizing proceduresis something I almost never had the pleasure of doing and so I contacted [Addy Santo](http://weblogs.asp.net/asanto) along with all the gory details. Thanks Santo, you saved the day. Anyway, Addy was able to point out where to start looking for performance problems: In short, he found two major problems in the procedure: 1. using the **Coalesce** function was the most problematic feature Use of  this function in the procedure prevented the query optimizer in SQL server from doing its job. In general, we need to stay clear of doing function on column names inside SELECTs because then the query cannot use an index to do the searching, and it will be much slower. The function was used because one could choose to send or not send any parameters to the procedure (which was a report based on countries and so on). If no parameter was sent, the report would bring back data from all the countries in the database. **Fix:** Changing this coalesce call into a simple IF statement made all the difference. Here’s an example: WHERE       fwu.budgetno = 1 AND fwu.country = coalesce(@country, fwu.country) **Turns into** WHERE       fwu.budgetno = 1 AND **(@country IS NULL OR fwu.country = @country)** **Changing all the coalesce functions in the procedure into this syntax reduced the execution time from 17 seconds to about 5 seconds.** 2. The procedure was using a lot of right() and left() functions inside the SELECT clause. Using the _right_ and _left_ functions to calculate values out of columns at query time is also a performance hit and can prevent the query optimizer from using the clustered indexes on the table if they exist. There are a few places in which the left part or the right part of the period column is checked against a different column’s left or right evaluations. This is an expansive procedure and should be minimized. Addy came up with a good way to avoid this: Use a calculated column which is also indexed in the table. **Fix:** ·        Add a calculated column to the _table_ o       Make the formula of the column int (left(\[_ColumnName_\],4)) o       Add this column into the clustered index ·        After you have added the columns, replace each reference to the left or right functions on these columns into a simple check using the new column names. ·        Verify that the cluster statistics for the new columns are generated (this adds speed to the query) o       Open query analyzer and point to the database on which the query is located o       Make sure the “Show Execution plan” option is selected (Query->show execution plan) o       Run the procedure in the query analyzer and open the execution plan tab on the bottom o       Look for text in the plan that is written in RED LETTERS o       If you do not see such text, that means all is well. o       If you do find such text, hover your mouse over the icon. You will see a message telling you to update the missing statistics for the table o       Right click and select “add missing statistics” o       Selected the new columns you have just created and click update ·        After all these steps you should gain another 2 seconds or so. ### Resouerces Here are some resources to learn how to use the execution plan feature of SQL server: 1.   [SQL Server execution plan analysis](http://www.sql-server-performance.com/query_execution_plan_analysis.asp) 2. [Tips for Using SQL Server Query Analyzer](http://www.sql-server-performance.com/query_analyzer_tips.asp) 3. [The Best SQL server performance tuning tips from SQLTeam](http://www.sql-server-performance.com/best_sql_server_performance_tips.asp) (some amazing stuff here!) 4. [Identifying performance issues using SQL Server Profiler](http://vyaskn.tripod.com/analyzing_profiler_output.htm) 5. [SQL Server Performance - Common Symptoms and Tools (Part I)](http://www.sqljunkies.com/Tutorial/7F8518F9-FDAA-4FF3-8FC5-25E8946C8D0C.scuk) 6. [Inside SQL Server: Parse, Compile, and Optimize (intro to the query processor)](http://www.sqlmag.com/Articles/Index.cfm?ArticleID=7446) ## More in Other [All posts in Other](https://osherove.com/blog/category/other) - Feb 17, 2004 ### [Make your .Net application support scripting - a practical approach](https://osherove.com/blog/2004/2/17/make-your-net-application-support-scripting-a-practical-appr.html) - Jan 17, 2004 ### [Learning from mistakes: the Don Quixote pattern](https://osherove.com/blog/2004/1/17/learning-from-mistakes-the-don-quixote-pattern.html) - Oct 30, 2003 ### [Easy Proxy handling through the ProxyFactory and ProxyInfo classes](https://osherove.com/blog/2003/10/30/easy-proxy-handling-through-the-proxyfactory-and-proxyinfo-c.html) - Sep 9, 2003 ### [My favorites in OPML](https://osherove.com/blog/2003/9/9/my-favorites-in-opml.html) --- ## About the author **Roy Osherove**: Senior Solutions Architect at AWS working on AI for production and AI-driven development; author of The Art of Unit Testing and Elastic Leadership, with about 20 years of consulting and training on unit testing, TDD, team leadership and continuous delivery. ### Sites - [RobotPaper](https://robotpaper.ai/): This blog: AI patterns and anti-patterns, AI and coding agents, and engineering leadership. - [osherove.com](https://osherove.com): Roy's personal site. Author of The Art of Unit Testing and Elastic Leadership; consulting and training on unit testing, TDD, team leadership and continuous delivery, plus about 20 years of blog posts. - [The Art of Unit Testing](https://www.artofunittesting.com): The Art of Unit Testing, 3rd Edition (Manning): readable, maintainable, trustworthy unit tests, plus TDD workshops in C#, JavaScript, Java and Kotlin. - [5whys.com](https://www.5whys.com): Roy's writing on software team leadership: self-organizing teams, surviving chaos, learning mode and the Elastic Leadership model. - [Pipeline Driven](https://pipelinedriven.org): Continuous delivery and DevOps practices: build patterns, pipeline-friendly testing, DevOps metrics and enterprise DevOps. - [Elastic Leadership](https://www.elasticleadership.com): Elastic Leadership, Roy's framework for software team leaders (survival, learning and self-organizing modes). ### Social - X: https://x.com/royosherove - LinkedIn: https://www.linkedin.com/in/osherove/ ### Contact - Contact form: https://forms.gle/jQSx5KoXvrVA1JCK7 ### License Licensed under CC BY 4.0 — reuse, quote and adapt with attribution (Roy Osherove, link to the original page). License: https://creativecommons.org/licenses/by/4.0/