I was working on a stored procedure the other day, not one that I wrote originally but one that was causing performance issues in out system. The procedure was taking any where from 40 to 60 seconds to run, which is unacceptable in a web service. This in fact was causing the web service to time out.

An inspection of the execution plan showed that one part of the procedure was doing an index scan and that it was this that was taking up over 85% of the execution time. I was totally baffled, it was hitting the index so why wasn’t it faster?

Then I learned three things all at once:

• Implicit Conversion
• non-sargable kills performance
• Index Seek is preferred of Index Scan

Implicit Conversion

In the stored proc a variable was defined as an NVARCHAR(20) but the field in the table it was being compared to was a CHAR(10). This lead to an implicit conversion of the variable to a CHAR(10), which lead into the next issue:

Non-Sargable Kills Performance

Sargable refers to the pseudo-acronym “SARG” – Search ARGument and refers to a WHERE clause that compares a constant value to a column value. The implicit conversion was causing a non-SARGable condition which means the WHERE clause cannot use an index.

Index Scan

Because of the non-sargable condition an Index Scan was being performed. An Index Scan is just as bad as a Table Scan in the SQL realm and should be avoided at all costs.

The solution was simple: Change the variable to a CHAR(10). After doing that the Index Scan became an Index Seek and the whole stored procedure returned in less than a second. Any time I see an order of magnitude improvement like that from one simple change it just boggles my mind.

The Period Matrix table, Pd_Matrix, is used in calculating accounting period formulas. These formulas include current month, quarter or years activity or balance. These formulas can also be created using standard SQL functions but not all functions are common between SQL Server and MS Access. The Period Matrix has the advantage in that it can be used in any DBMS and it also can be used in table pivoting and folding.

Read the rest of this entry »

Pivoting a table on multiple columns with a crosstab query gets messy really fast.  To eliminate the need for complicated Multiple Value Field Queries you can use the lowly IIF() function and the obscure CHOOSE() function to pivot a table.

Read the rest of this entry »