August 18, 2008 at 8:30 am
kathyoshea (8/15/2008)
I've never really gotten a handle on execution plans and was hoping to use the code in the article to understand them a bit more. However, the code in the last part of the article (about the execution plan) didn't run. Did I miss something?Also does anyone know of a great resource for understanding execution plans?
Thanks!
Grant Fritchey (this forum) has a new e-book out called "Dissecting SQL Server Execution Plans". So far it's a good read and full of useful stuff. It's offered up by Red Gate.
ATBCharles Kincaid
August 18, 2008 at 8:41 am
Further, Grant's book explodes the myth that LIKE always forces a table scan.
ATBCharles Kincaid
August 20, 2008 at 1:15 am
Awesome one. 🙂
August 20, 2008 at 6:29 pm
Thanks again, Arniban. 🙂
--Jeff Moden
January 16, 2009 at 4:51 am
Hey Jeff,
Good article. It's great that you and others are continuing to drive home the fundamentals of good code practices!!
Rob.
January 16, 2009 at 7:07 am
I liked this article the first time I read it, but why is it being presented as a new article with today's date?
lm
January 16, 2009 at 7:14 am
Hmmm, not sure... on the home page, it's listed as "By Jeff Moden 2009/01/16 (first posted: 2007/12/06)"... not sure why the date of the actual article changed.
Steve? If you see this, can you explain?
--Jeff Moden
January 16, 2009 at 7:17 am
It's because of a triangular join on the calendar table . . .
---------------------------------------------------------
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."
January 16, 2009 at 7:18 am
robertm (1/16/2009)
Hey Jeff,Good article. It's great that you and others are continuing to drive home the fundamentals of good code practices!!
Rob.
Glad you liked it, Robert. It was supposed to be listed as a "republished" article and it kinda was on the home page, but not within the article itself. Looks like I wrote it today when it actually first published on 2007/17/06.
--Jeff Moden
January 16, 2009 at 7:22 am
jcrawf02 (1/16/2009)
It's because of a triangular join on the calendar table . . .
nice. 🙂
January 16, 2009 at 7:29 am
Your original query is:
USE NorthWind
SELECT
[x].[OrderID],
[x].[Freight],
(
SELECT
SUM([y].[Freight])
FROM [dbo].[Orders] [y]
WHERE [y].[OrderID] <= [x].[OrderID]
) AS [RunningTotal],
(
SELECT
COUNT([y].[Freight])
FROM [dbo].[Orders] [y]
WHERE [y].[OrderID] <= [x].[OrderID]
) AS [RunningCount]
FROM [dbo].[Orders] [X]
ORDER BY
[x].[OrderID]
This would result in 2 triangular joins, if you did the following you could at least cut the internal row set in half.
SELECT
USE NorthWind
SELECT
[x].[OrderID],
[x].[Freight],
SUM([y].[Freight]) AS [RunningTotal],
COUNT([y].[Freight]) AS [RunningCount]
FROM [dbo].[Orders] [X]
CROSS JOIN [dbo].[Orders] [Y]
WHERE [y].[OrderID] <= [x].[OrderID]
GROUP BY
[x].[OrderID],
[x].[Freight]
ORDER BY
[x].[OrderID]
[/code]
I look forward to your solution.
January 16, 2009 at 7:37 am
Jeff:
1) For your follow-on articles, when you demonstrate the 'tsql trick' solution, please make sure you caveat the hell out of it about how and why you can get incorrect results.
2) Because of 1 above, include other solutions (both set-based and cursor/while loop), preferably with benchmarks. Lets get the article to be THE one on the web to point users to for these types of solutions.
3) "Heh... lost leader for one or two articles coming up... " should be "Heh... loss leader for one or two articles coming up... " 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 16, 2009 at 7:40 am
Look on the bright side... after 14 pages of responses... The article shoudl almost be compiled. You will have all kinds of do's and don'ts and should be almost set 🙂
January 16, 2009 at 7:47 am
An interesting addendum to this article would be data on the proper and improper use of Cross/Outer Apply. That, by definition, is pretty much a guaranteed triangle join, but sometimes it's the exact thing you need.
I'm not sure how others are using the Apply "join", but I bet it's got enough confusion on it to warrant some hints and tips.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 16, 2009 at 8:02 am
Articles are sometimes republished.
N 56°04'39.16"
E 12°55'05.25"
Viewing 15 posts - 121 through 135 (of 255 total)
You must be logged in to reply to this topic. Login to reply