Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

.NET CrossTabs versus SQL Server CrossTabs

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:

Print | posted on Thursday, May 12, 2005 10:01 AM | Filed Under [ T-SQL CrossTabs / Pivoting Data .NET (C# / VB) Efficiency Report Writing Code Library - C# / VB.NET ]

Feedback

Gravatar

# ASP and ADO Pivots -- Old School Cross Tabs

5/15/2005 9:40 PM | Welcome to Jeff's Blog
Gravatar

# re: .NET CrossTabs versus SQL Server CrossTabs

I think you forgot to mention in your post the bandwidth cost of returning the entire resultset to the client.

Showing the cost of returning the entire resultset plus the cost of creating the pivot query would in both methods will i think strenthen the result of your conclusion.
6/21/2005 5:45 AM | Little Monstoer
Gravatar

# re: .NET CrossTabs versus SQL Server CrossTabs

Little Monster -- don't forget, the client isn't doing any summarization at all; only fully summarized rows are returned to the client which simply pivots them.

For example, if you have a 10,000 row transaction table which will be summarized into data for 10 clients over 12 months, only 120 fully aggregated rows should be returned to the client which are then pivoted into the final 10 by the client-side code. A T-SQL pivot would indeed only return 10 rows (with 11 more columns of course), but at the huge expense (as shown) of having SQL Server do all the work and forcing presentation code into your database layer.

So the key point is that the client is not doing any pivot calculations or aggregating at all; it is simply changing the format of data that is returned. Bandwith isn't really a consideration, since we are working with raw *data* in its native, summarized form.
6/21/2005 8:54 AM | Jeff
Gravatar

# re: .NET CrossTabs: Transforming a Normalized DataReader into a Pivoted DataTable

7/14/2006 8:21 AM | Welcome to Jeff's Blog
Gravatar

# re: .NET CrossTabs versus SQL Server CrossTabs

I have the following data
AcCode Month Value
AA01, Jan, 20
AA01, Feb, 30
AA01, Mar, 40
AA02, Jan, 10
AA02, Feb, 10
AA02, Mar, 10
AA03, Jan, 20
AA03, Feb, 50
AA03, Mar, 40
AA04, Jan, 20
AA04, Feb, 20
AA04, Mar, 20

How can I use your procedure to get something like this
CodeRange Jan, Feb , Mar
AA01-AA02, 30, 40, 50
AA03-AA04, 40, 70, 60

I need an urgent help.
Thanks
10/1/2008 4:16 AM | Richard Nunoo
Gravatar

# re: .NET CrossTabs versus SQL Server CrossTabs

ID S_ID DIVISION
1 2 II
2 2 I
3 3 IV
4 3 III
5 4 II
6 5 I
7 5 III
8 6 0
9 6 IV
10 6 I
I NEED TO COUNT DIVISION AND CROUP BY S_ID TO GET SOMETING LIKE THIS
S_ID I II III IV 0
2 1 1 0 0 0
3 0 0 1 1 0
4 1 0 0 0 0
5 1 0 1 0 0
6 1 0 0 1 1
12/5/2008 3:16 AM | arnold
Gravatar

# re: .NET CrossTabs versus SQL Server CrossTabs

Richard Nunoo, you probably solved this by now, but you need to get the database to return your results with the range already applied

Something like (note this oracle sql - youll have to translate to sqls):

SELECT
CASE WHEN MOD(REPLACE(AcCode, 'A', ''), 2) = 1 THEN 'odd number, append the plus one of it
AcCode || '-AA' || TO_CHAR(REPLACE(AcCode, 'A', '') + 1, 'FM00')
ELSE 'even number, prepend the minus one of it
TO_CHAR(REPLACE(AcCode, 'A', '') + 1, 'FM00') || '-AA' || AcCode
END as AcCode

We replace the A with nothing. AA01 -> 01
We MOD '01' by 2, number conversion implicit -> 1
When this result is 1, the AA0x number is ODD, so we append the even number:
'AA01' append '-' append (remove A, add 1, implcit conversion to number, convert back to char with specified format 00)

Result:
AA01 -> AA01-AA02
AA02 -> AA01-AA02
AA03 -> AA03-AA04

etc
6/25/2009 3:29 AM | Matt
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET