An optimizing experience

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 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
2. Tips for Using SQL Server Query Analyzer
3. The Best SQL server performance tuning tips from SQLTeam (some amazing stuff here!)
4. Identifying performance issues using SQL Server Profiler
5. SQL Server Performance - Common Symptoms and Tools (Part I)
6. Inside SQL Server: Parse, Compile, and Optimize (intro to the query processor)