June 27, 2009 at 10:03 am
GilaMonster (6/27/2009)
An interesting response here. Anyone understand what he's asking?http://qa.sqlservercentral.com/Forums/Topic743113-391-1.aspx
The terms being used almost makes it sound like class work in a Modeling class.
June 27, 2009 at 10:14 am
Could well be. Should I reply with some relational theory?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 27, 2009 at 11:43 am
Jeff Moden (6/26/2009)
Florian Reischl (6/26/2009)
...@Jeff
Just saw your post here
Maybe you missed this post by Lutz. There is a case where PIVOT seems to be much faster than cross-tab here.
Not if you do it right... 😉 See the following article and then revisit Lutz's post.
I was offline yesterday, so I'm sorry for catching up late...
@Flo:
In the post you referred to I wasn't claiming that a PIVOT would be faster than cross-tab. I have no authority whatsoever to claim anything like that without any coded proof. 🙂
The rough measurements done by the OP were almost identical (both slightly over 2min). So I was wondering if there is any benefit to PIVOT at all...
If someone will follow the thread mentioned above there is a more interesting part to it: http://qa.sqlservercentral.com/Forums/FindPost741794.aspx
The table Chris (OP) provided shows, that he's getting that poor performance regardless of the kind of index he's applying.
To see a 2 1/2 min query (with no index) being improved by only 20% when trying different index combinations didn't fit in my current experience at all. I've asked the OP for actual execution plans with and without index (not available yet).
Meanwhile the discussion moved on comparing the different options on how to do a pivot....
June 27, 2009 at 2:22 pm
GilaMonster (6/27/2009)
Could well be. Should I reply with some relational theory?
Sounds like a plan to me. I couldn't, it's been so long I just barely remember anything beyond 3NF, and even then some of that is rusty.
June 27, 2009 at 2:30 pm
Lynn Pettis (6/27/2009)
GilaMonster (6/27/2009)
Could well be. Should I reply with some relational theory?Sounds like a plan to me. I couldn't, it's been so long I just barely remember anything beyond 3NF, and even then some of that is rusty.
Transitive dependencies are 3NF, or, more specifically to get a table into 3NF, transitive dependencies should be eliminated. The example the guy has there is a classic case of a table that's in 2NF, but not 3NF. One of the non-key columns depends on another non-key column.
I'm just not sure what exactly he wants to know, to prove that it's a transitive dependency, to remove it or something else.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 27, 2009 at 2:47 pm
lmu92 (6/27/2009)
Jeff Moden (6/26/2009)
Florian Reischl (6/26/2009)
Maybe you missed this post by Lutz. There is a case where PIVOT seems to be much faster than cross-tab here.@Flo:
In the post you referred to I wasn't claiming that a PIVOT would be faster than cross-tab. I have no authority whatsoever to claim anything like that without any coded proof. 🙂
The rough measurements done by the OP were almost identical (both slightly over 2min). So I was wondering if there is any benefit to PIVOT at all...
I not intended to say that you called called PIVOT faster than cross-apply. Sorry, if it sounded like this. Nevertheless I have to admit that I miss-read the thread and I thought PIVOT was faster...
Flo
(Sometimes I wish to have a "[ quote ]"-management tool *sigh*)
How to Post Data/Code to get the best Help How to Post Performance Problems
June 27, 2009 at 3:01 pm
@Flo:
I always enjoy to watch two Germans arguing about an english statement made by one of them... - especially if I'm directly involved... 😀 I'm not sure whether I clearly phrased what I was intended to say... 😉 So: never mind!
We NEAFL's (NonEnglishAsFirstLanguage) will never know...
June 27, 2009 at 3:12 pm
lmu92 (6/27/2009)
We NEAFL's (NonEnglishAsFirstLanguage) will never know...
ROFL!
Sounds like a Turkish food :hehe:
How to Post Data/Code to get the best Help How to Post Performance Problems
June 27, 2009 at 3:14 pm
GilaMonster (6/27/2009)
Lynn Pettis (6/27/2009)
GilaMonster (6/27/2009)
Could well be. Should I reply with some relational theory?Sounds like a plan to me. I couldn't, it's been so long I just barely remember anything beyond 3NF, and even then some of that is rusty.
Transitive dependencies are 3NF, or, more specifically to get a table into 3NF, transitive dependencies should be eliminated. The example the guy has there is a classic case of a table that's in 2NF, but not 3NF. One of the non-key columns depends on another non-key column.
I'm just not sure what exactly he wants to know, to prove that it's a transitive dependency, to remove it or something else.
3NF -- The Key, The Whole Key, and nothing but The Key.
June 27, 2009 at 3:34 pm
Lynn Pettis (6/27/2009)
3NF -- The Key, The Whole Key, and nothing but The Key.
Didn't realise anyone else knew that mnemonic. I learnt it at university as an easy way to remember the first 3 normal forms.
1NF - The data shall depend on the key (no repeating groups)
2NF - the whole key (no partial key dependencies)
3NF - and nothing but the key (no inter-data dependencies)
It's interesting, I'm working of an 'introduction to performance tuning' course for a client. I put in Normalisation as a topic and the client complained, saying that wasn't necessary because he and all his developers knew normalisation. I met him for supper a week or so later (as he's an old friend as well as a client) and it turned out that what he thought was 3NF was actually 1NF. Normalisation is still on the course layout.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 27, 2009 at 4:25 pm
GilaMonster (6/27/2009)
Lynn Pettis (6/27/2009)
3NF -- The Key, The Whole Key, and nothing but The Key.Didn't realise anyone else knew that mnemonic. I learnt it at university as an easy way to remember the first 3 normal forms.
1NF - The data shall depend on the key (no repeating groups)
2NF - the whole key (no partial key dependencies)
3NF - and nothing but the key (no inter-data dependencies)
It's interesting, I'm working of an 'introduction to performance tuning' course for a client. I put in Normalisation as a topic and the client complained, saying that wasn't necessary because he and all his developers knew normalisation. I met him for supper a week or so later (as he's an old friend as well as a client) and it turned out that what he thought was 3NF was actually 1NF. Normalisation is still on the course layout.
Okay, you were between 6 and 10 when I learned that in University. Not exactly when or which school I was in when I had my first database class.
June 27, 2009 at 7:16 pm
Florian Reischl (6/27/2009)
I read your really good article and did my own tests. If multi-aggregated results are required the cross-tab solution is much faster than pivot. But in case of single aggregations (e.g. Amount in your test environment) pivot seems to runs at least same performance - sometimes faster - than a cross-tab solution.
Agreed... and thanks for the feedback, Flo.
Since Pivot and Cross-tabs are neck and neck on the simple stuff in such cases, I'll still continue to recommend Cross-Tabs simply for their readability.
--Jeff Moden
June 28, 2009 at 1:29 am
Florian Reischl (6/27/2009)
I read your really good article and did my own tests. If multi-aggregated results are required the cross-tab solution is much faster than pivot. But in case of single aggregations (e.g. Amount in your test environment) pivot seems to runs at least same performance - sometimes faster - than a cross-tab solution.I tried on SQL Server 2005 and SQL Server 2008 with 100,000 and 1,000,000 rows.
Disclaimer: PIVOT sucks.
Right, now that is out of the way, take a look at my lastest post on that thread. It references a way of doing multiple-aggregations with PIVOT just as fast as the 'pre-aggregated' cross-tab, and shows an interesting change in behaviour from 2005 to 2008 which I choose to believe is a bug.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 28, 2009 at 2:51 am
Lynn Pettis (6/27/2009)
Okay, you were between 6 and 10 when I learned that in University. Not exactly when or which school I was in when I had my first database class.
😀
You're the first person I know (outside of my 2nd year CS class) that also knew that mnemonic.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 28, 2009 at 11:03 am
GilaMonster (6/28/2009)
Lynn Pettis (6/27/2009)
Okay, you were between 6 and 10 when I learned that in University. Not exactly when or which school I was in when I had my first database class.😀
You're the first person I know (outside of my 2nd year CS class) that also knew that mnemonic.
I've heard it before, but probably wouldn't have quoted it. I think I heard Celko say it a seminar, but I'm not sure.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 6,121 through 6,135 (of 66,000 total)
You must be logged in to reply to this topic. Login to reply