March 4, 2009 at 10:37 am
Bob Hovious (3/4/2009)
Some other priceless pearls from the "Mangled" SQL Blog....21. In case using GROUP BY without an aggregate function try using DISTINCT instead
What you have to love about all these rules are the total absence of examples, or explanations about "why". It's possible that he really doesn't have the depth of understanding to look at "truisms' with a critical eye. That's one thing I love about this site, when I get my head handed to me, I at least understand why.
What a coincidence. In a thread earlier today in which this poster was involved, I suggested doing the exact opposite, for no other reason than GROUP BY implies intent and deliberation. He didn't mention it.
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
March 4, 2009 at 10:39 am
Bob Hovious (3/4/2009)
What you have to love about all these rules are the total absence of examples, or explanations about "why". It's possible that he really doesn't have the depth of understanding to look at "truisms' with a critical eye. That's one thing I love about this site, when I get my head handed to me, I at least understand why.
Maybe he just got all these points from reading other peoples best practices and did not bother to make a case study??
-Roy
March 4, 2009 at 10:39 am
Bob Hovious (3/4/2009)
21. In case using GROUP BY without an aggregate function try using DISTINCT instead
To my knowledge, I have never used a GROUP BY without an aggregate function.
Not to mention that DISTINCT generally performs through aggregation... but hey.
I like this one
22. Avoid using variables in a WHERE clause in case the query is located in a batch-file.
What does it mean? Is he referring to parameter sniffing?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 4, 2009 at 10:47 am
Chris Morris (3/4/2009)
21. In case using GROUP BY without an aggregate function try using DISTINCT instead
What a coincidence. In a thread earlier today in which this poster was involved, I suggested doing the exact opposite, for no other reason than GROUP BY implies intent and deliberation. He didn't mention it.
I usually use DISTINCT when I want to eliminate duplicates and GROUP BY when I want aggregates because they make it clear what I'm doing in each case. In most cases DISTINCT will perform the same as a GROUP BY without any aggregates (yes, I've tested it) and generate the same exec plan
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
March 4, 2009 at 10:47 am
Having spent my lunch hour perusing his blogsite, it's very tempting to do a parody of it, but we've already spent more time unloading on him than he's worth. This is truly shooting ducks on the pond.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 4, 2009 at 10:50 am
First, boy has this put him under the microscope. Second, I'll have to wait until I get home to look at these posts of his as our web filter blocks his site.
Seems like a good thing from what I'm hearing.
March 4, 2009 at 10:53 am
I'm glad you said that, Gail. I did a single test on that a while back because I figured the internal logic had to be the same, but for the aggregation functions. I often do Group By, if only to take counts, when eliminating duplicates because creeping requirements often make me wish I had more information available than just a distinct list.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 4, 2009 at 11:00 am
Another one. This was interesting. If you spot something wrong with my tests, let me know. This is going up as a blog post, with names changed (and grammar fixed a bit) to protect the innocent (or whatever).
12. In case using IN try to order the list of values so that the most frequently found values are placed first.
So I tried this:
/*
ProductIDRowCount
8704688 rows
8771327 rows
972380 rows
823148 rows
72352 rows
8972 rows*/
DBCC FReeproccache()
DBCC dropcleanbuffers()
GO
SELECT sod.ProductID
,sod.SalesOrderDetailID
FROM Sales.SalesOrderDetail AS sod
WHERE sod.ProductID IN (870, 877, 972, 723, 897)
GO
DBCC FReeproccache()
DBCC dropcleanbuffers()
GO
SELECT sod.ProductID
,sod.SalesOrderDetailID
FROM Sales.SalesOrderDetail AS sod
WHERE sod.ProductID IN (897, 723, 972, 877, 870)
GO
DBCC FReeproccache()
DBCC dropcleanbuffers()
GO
SELECT sod.ProductID
,sod.SalesOrderDetailID
FROM Sales.SalesOrderDetail AS sod
WHERE sod.ProductID IN (972, 870, 877, 897, 723)
It resulted in three identical execution plans, every one of them a nonclustered index seek. In the three tests, the most frequently found values are first, last & mixed. All three queries had I/O that looked like this:
(6449 row(s) affected)
Table 'SalesOrderDetail'. Scan count 5, logical reads 26, physical reads 7, read-ahead reads 37
And the execution times were:
Query Compile Run
1 45ms 47ms
2 14ms 28ms
3 4ms 30ms
I then ran all three again, in reverse order:
Query Compile Run
3 34ms 52ms
2 25ms 46ms
1 5ms 25ms
[/quote]
The times scattered around the same values. No change in the order of the data affected the query positively or negatively. So where does this tip come from?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 4, 2009 at 11:12 am
Grant Fritchey (3/4/2009)
Another one. This was interesting. If you spot something wrong with my tests, let me know. This is going up as a blog post, with names changed (and grammar fixed a bit) to protect the innocent (or whatever).12. In case using IN try to order the list of values so that the most frequently found values are placed first.
The times scattered around the same values. No change in the order of the data affected the query positively or negatively. So where does this tip come from?
I was suspicious of that one, but without having tested (and without enthusiasm to load up SQL and check since I'm busy writing a configuration analysis report) I didn't mention it.
Do you think the 'order in a where clause' is worthy of a blog post? I did one way back, but may be worth revisiting in more detail.
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
March 4, 2009 at 11:16 am
try to order the list of values so that the most frequently found values are placed first
Grant, I've read somewhere a while back that this is actually true for MySQL. However, I always understood it to mean put the ones with the fewest expected hits first. IF you are basically doing a table scan and filtering, then logically the quicker you can decide to toss a row out, the fewer tests you have to run, and you can save a couple of nanos. Any validity to this is destroyed by the optimizer's ability to construct execution plans based on index statistics.
Again, the man doesn't seem to understand the why behind the rules. That would make him a poster child for what Jeff calls "SQL Clones".
From that my mind just jumped to "Attack of the Clones" and all of a sudden the theme from "The Empire Strikes Back" is running through my mind. Go figure.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 4, 2009 at 11:21 am
GilaMonster (3/4/2009)
Grant Fritchey (3/4/2009)
Another one. This was interesting. If you spot something wrong with my tests, let me know. This is going up as a blog post, with names changed (and grammar fixed a bit) to protect the innocent (or whatever).12. In case using IN try to order the list of values so that the most frequently found values are placed first.
The times scattered around the same values. No change in the order of the data affected the query positively or negatively. So where does this tip come from?
I was suspicious of that one, but without having tested (and without enthusiasm to load up SQL and check since I'm busy writing a configuration analysis report) I didn't mention it.
Do you think the 'order in a where clause' is worthy of a blog post? I did one way back, but may be worth revisiting in more detail.
That is a tip that comes up pretty regularly.
BTW, it actually can affect the execution plan when the execution plans are from really complex and extreme queries. I've seen it. I doubt I could reproduce it. I'm pretty sure it was an artifact caused by the complexity of the plan resulting in it bailing out of the optimizer prior to the optimizer completing it's work. We had some really horrific 80 table joins on one of our systems about five years ago.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 4, 2009 at 11:24 am
Bob Hovious (3/4/2009)
try to order the list of values so that the most frequently found values are placed first
Grant, I've read somewhere a while back that this is actually true for MySQL. However, I always understood it to mean put the ones with the fewest expected hits first. IF you are basically doing a table scan and filtering, then logically the quicker you can decide to toss a row out, the fewer tests you have to run, and you can save a couple of nanos. Any validity to this is destroyed by the optimizer's ability to construct execution plans based on index statistics.
Again, the man doesn't seem to understand the why behind the rules. That would make him a poster child for what Jeff calls "SQL Clones".
From that my mind just jumped to "Attack of the Clones" and all of a sudden the theme from "The Empire Strikes Back" is running through my mind. Go figure.
It might make sense in an EXISTS statement, get the hit as soon as possible, but for an IN clause, it just doesn't make any sense.
I've been running a loop of "I love living in the city" by the Fear in my head for days now, so, I'd almost welcome the Star Wars theme, even the 70's disco version.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 4, 2009 at 11:35 am
Grant Fritchey (3/4/2009)
BTW, it actually can affect the execution plan when the execution plans are from really complex and extreme queries. I've seen it. I doubt I could reproduce it. I'm pretty sure it was an artifact caused by the complexity of the plan resulting in it bailing out of the optimizer prior to the optimizer completing it's work.
It was probably a case of 'good enough plan found'
What I'm getting at is that putting a condition first (or last) won't make SQL evaluate it first or last.
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
March 4, 2009 at 11:44 am
GilaMonster (3/4/2009)
Grant Fritchey (3/4/2009)
BTW, it actually can affect the execution plan when the execution plans are from really complex and extreme queries. I've seen it. I doubt I could reproduce it. I'm pretty sure it was an artifact caused by the complexity of the plan resulting in it bailing out of the optimizer prior to the optimizer completing it's work.It was probably a case of 'good enough plan found'
What I'm getting at is that putting a condition first (or last) won't make SQL evaluate it first or last.
I've heard that one before, and assume it comes from someone giving an example like
WHERE leastCriterion = TRUE
AND ( secondLeast = TRUE
AND ( thirdLeast = TRUE)
)
)
and the user didn't understand the parentheses that are forcing the order, and just writes
WHERE leastCriterion = TRUE
AND secondLeast = TRUE
AND thirdLeast = TRUE
But I could be wrong. Couldn't hurt for folks to read why it isn't true.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
March 4, 2009 at 11:48 am
Grant Fritchey (3/4/2009)
I've been running a loop of "I love living in the city" by the Fear in my head for days now, so, I'd almost welcome the Star Wars theme, even the 70's disco version.
You think that's bad, ever hear the bonus track on Blink-182's 'Take off your pants and jacket'?
"When you f*d Grandpa,"... etc.
Was funny the first time I heard it, then I found myself humming the damn thing at various times over the next week.
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Viewing 15 posts - 2,206 through 2,220 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply