Hidden RBAR: Triangular Joins

Question

Hidden RBAR: Triangular Joins

Viewing 15 posts - 121 through 135 (of 255 total)

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

Charles Kincaid SSChampion Points: 13593 More actions · Answer 1

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

Charles Kincaid SSChampion Points: 13593 More actions · Answer 2

Further, Grant's book explodes the myth that LIKE always forces a table scan.

ATBCharles Kincaid

Anipaul SSC-Insane Points: 24681 More actions · Answer 3

Anipaul

SSC-Insane

Points: 24681

August 20, 2008 at 1:15 am

#860439

Awesome one. 🙂

Jeff Moden SSC Guru Points: 1003948 More actions · Answer 4

Thanks again, Arniban. 🙂

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

robertm Ten Centuries Points: 1096 More actions · Answer 5

Hey Jeff,

Good article. It's great that you and others are continuing to drive home the fundamentals of good code practices!!

Rob.

leon mckay Grasshopper Points: 16 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1003948 More actions · Answer 7

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

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)

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 8

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

Jeff Moden SSC Guru Points: 1003948 More actions · Answer 9

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

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)

leon mckay Grasshopper Points: 16 More actions · Answer 10

jcrawf02 (1/16/2009)
It's because of a triangular join on the calendar table . . .

nice. 🙂

LeeBear35 SSC Enthusiast Points: 124 More actions · Answer 11

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.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 12

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

BJ Hermsen Hall of Fame Points: 3123 More actions · Answer 13

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 🙂

GSquared SSC Guru Points: 260824 More actions · Answer 14

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

SwePeso SSC-Dedicated Points: 39752 More actions · Answer 15

Articles are sometimes republished.

N 56°04'39.16"
E 12°55'05.25"