As promised in my last post, here is some performance testing to help you determine the performance benefits (if any) of performing your crosstabs at the presentation or code layer, as opposed to forcing SQL Server to do this.

For testing purposes, I have slightly altered my dynamic crosstab procedure by adding a “debug” option, which returns the SQL generated and not the results themselves.  I also made a slight alteration to accomodate data values that contains apostrophes, since that would cause errors in the original code.  You can download the code for the “new and improved” dynamic crosstab by clicking here.

To set this all up, go to your Northwind database and create the crosstab stored procedure.  You also need to create a view we will use for testing as well.

Once that is done, create a new C# console application and paste in the testing application found here.

The test is pretty simple, but the idea is to measure the efficiency of the following 3 techniques:

• Dynamic CrossTab -- This technique consists of executing the CrossTab procedure over and over and allowing it to generate and execute the SQL.  The time taken for the procedure to parse the arguments, generate the SQL statement, and then return the results is all considered.
• Static CrossTab --This technique uses the CrossTab procedure to return the SQL statement needed to produce the results. Once that SQL statement is generated, that statement alone is called over and over and not the CrossTab procedure.  This is a “static” crosstab in effect, where the SQL itself is hard-coded to do the pivot.  We are just using the CrossTab procedure to help us out.  (If you are curious, take a look or print out the SQL statement required to do the crosstab in T-SQL).  Thus, the time taken to call the stored procedure and generate the SQL statement itself is not considered, just the time taken to execute that SQL statement over and over.
• C# Pivot Function -- This is the technique discussed in my last post, in which SQL Server just returns the results in a standard, normalized row/column format (summarized as much as possible, of course) and we do the transformation at the presentation layer using the Pivot() function I provided.  The time taken to execute the SQL statement and for the function to do its work is all measured.

The application will prompt for the number of iterations for which to run each test.  I recommend at least 20-30.  As I mentioned previously, the results at this point are pretty conclusive.  Please send me any feedback, especially possible of ways to make the tests more fair, or any other techniques that perhaps we can measure.

Below are the results from 75 iterations, copied and pasted from my spiffy little program.  (I love writing console applications .... It reminds of the good old days when you write things like “PRESS ENTER TO CONTINUE“)

To make a long story short:  Pivoting at the presentation layer results in clearer, shorter code and much better performance.

Beginning performance test for crosstab techniques.

Please enter the # of iterations to perform:
75

Testing dynamic SQL crosstab procedure....
-- Result: 25 seconds.

Testing static SQL crosstab procedure....
-- Result: 22 seconds.

Testing C# crosstab transformation....
-- Result: 8 seconds.

Testing complete.

Press ENTER to quit.

 see also:

• Keep those SQL Crosstabs flexible!
• .NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable
• ASP and ADO Pivots -- Old School CrossTabs
• Sorting Columns with the C# Pivot Function
• SQL 2005 PIVOT Operator (link)