Are the posted questions getting worse?

Question

Are the posted questions getting worse?

Viewing 15 posts - 6,121 through 6,135 (of 66,712 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

GilaMonster (6/27/2009)
An interesting response here. Anyone understand what he's asking?
http://www.sqlservercentral.com/Forums/Topic743113-391-1.aspx

The terms being used almost makes it sound like class work in a Modeling class.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

LutzM SSC Guru Points: 107049 More actions · Answer 3

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://www.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....

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 6

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*)

LutzM SSC Guru Points: 107049 More actions · Answer 7

@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...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 8

lmu92 (6/27/2009)
We NEAFL's (NonEnglishAsFirstLanguage) will never know...

ROFL!

Sounds like a Turkish food :hehe:

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

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.

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

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

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)

Paul White SSC Guru Points: 150467 More actions · Answer 13

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jack Corbett SSC Guru Points: 184394 More actions · Answer 15

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