Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Question

Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Viewing 15 posts - 46 through 60 (of 130 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

rbarryyoung (12/4/2008)
mtassin (12/4/2008)
I'm not sure if it's Business Object's best practice, but my best practices for Crystal Reports are to develop stored procedures to retrieve the data in exactly the format I want/need. I use Crystal simply for formatting.
Good lord, yes. Never let Crystal process data, it's awful at that.

Heh no kidding. A few months ago I was given a goodie bag of a dozen Crystal reports which were performing poorly. One of them was running for 25 minutes before crashing the SQL Server. All of them were finance reports from Lawson, and all used the Crystal data environment to join tables, even the parameterised ones. That meant millions of rows were shifted across the network then processed by Crystal. In one or two cases there was a 5 minute wait before the parameters sheet came up.

I changed them to use sprocs for the parameters and the data, and they now run in seconds because Crystal only plays with the data it needs.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

That's part of the reason why people still use cross-tabs on the server side... "Respect the pipe!" 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

WOW,

Not sure if it's even worth giving my praise to this master piece of an article after all the praise that has been given already...

But I just can't help myself..

Jeff that was amazing, when I first saw the title I thought ok cool let me read the first article , at last someone might shed light on why pivots might be better than cross-tabs. WRONG he he he

Not only did I learn that in most cases Cross-tabs are better, but also how to make the already simple and better cross-tab work better than how most people including myself use it.

Not only did I learn more about Cross-tabbing in this article, but I learnt about some new approaches to variable swapping (which I must say I really like and will start using today in some code I have to write).

Also I learnt all about rollups and cubes.

I must stop now, but Jeff you might be surprised but I think just the way that you shared some of you own style and techniques in this article really opened up my knowledge based to a lot of things, and not just what I think you indeed for us to learn.

I can't wait for what you write next 🙂

thanks

Chris

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Judy Why Hall of Fame Points: 3656 More actions · Answer 4

Very very good article. From here I learn how to use Tally table, QuoteName, Grouping.

Thank you!:D

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

Christopher Stobbs (12/5/2008)
you might be surprised but I think just the way that you shared some of you own style and techniques in this article really opened up my knowledge based to a lot of things, and not just what I think you indeed for us to learn.
I can't wait for what you write next 🙂
thanks
Chris

Heh... no... not surprised... amazed would be the better word. It's a rare thing (well, except on this good forum, apparently! 🙂 ) for me to find people that will sit down and study such an article to learn more than just the object of the article. Well done, Christopher and thank you for the wonderful feedback!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

Judy (12/5/2008)
From here I learn how to use Tally table, QuoteName, Grouping.
Thank you!:D

Thank YOU! I absolutely love feedback like this. I love to teach and I love to think that I might have turned a light or two on for someone. Nice job and keep up the good work, Judy.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

steve dassin SSCrazy Points: 2396 More actions · Answer 7

Nice job and write up Jeff. For what it's worth, here's your sample data using RAC 🙂

http://beyondsql.blogspot.com/2008/12/sql-server-dynamic-crosstabs-by-jeff.html

best,

steve

www.beyondsql.blogspot.com

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 8

Very cool... 2 seconds huh? That was going to be my next question. What kind of hardware are you running? I'm running a 6 year old P4 1.8GHz with 1GB RAM with SQL Server 2k5 Dev Edition SP2.

I guess my other question would be... I only see versions for SQL Server 7 and 2000... do you have anything for 2k5 and 2k8? After seeing a nice comparison like that, people might wanna know.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

steve dassin SSCrazy Points: 2396 More actions · Answer 9

Jeff Moden (12/6/2008)
Very cool... 2 seconds huh? That was going to be my next question. What kind of hardware are you running? I'm running a 6 year old P4 1.8GHz with 1GB RAM with SQL Server 2k5 Dev Edition SP2.
I guess my other question would be... I only see versions for SQL Server 7 and 2000... do you have anything for 2k5 and 2k8? After seeing a nice comparison like that, people might wanna know.

I'm running a 4 yr old vanilla P4 3GHz 1gb ram with XP sp2 and S2k5 Dev edition sp2:) Yeah Rac plays nice with S2k5 and should with 2k8 too. (The Rac sites a tad behind:)

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 10

Nice Article Jeff

Here is mine

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 11

Madhivanan (12/10/2008)
Nice Article Jeff
Here is mine
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

:hehe: I was waiting for you to respond to this one. :hehe:

Jeff says he thinks his way is going to end up faster than yours.

It would be really cool if you could do a performance comparison between the two methods.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 12

I already did such a comparison in Part 1 of this series, Goldie. 😉 Of course, I don't mind if someone else does a head-to-head comparison.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 13

Madhivanan (12/10/2008)
Nice Article Jeff
Here is mine
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Thanks for the feedback and the link, Madhivanan.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 14

Jeff Moden (12/10/2008)
I already did such a comparison in Part 1 of this series, Goldie. 😉 Of course, I don't mind if someone else does a head-to-head comparison.

Jeff, in your first article you compared a static pivot to a static cross-tab and found the cross tab to be faster. I'm just wondering if the results would be the same with a dynamic cross-tab and a dynamic pivot.

I've been meaning to test it out, but I've been too busy at work and my personal machine is pretty much hosed. 🙁

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 15

ggraber (12/10/2008)
Jeff Moden (12/10/2008)
I already did such a comparison in Part 1 of this series, Goldie. 😉 Of course, I don't mind if someone else does a head-to-head comparison.
Jeff, in your first article you compared a static pivot to a static cross-tab and found the cross tab to be faster. I'm just wondering if the results would be the same with a dynamic cross-tab and a dynamic pivot.
I've been meaning to test it out, but I've been too busy at work and my personal machine is pretty much hosed. 🙁

Dynamic SQL wouldn't make either faster or slower.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)