SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Hidden RBAR: Triangular Joins


Hidden RBAR: Triangular Joins

Author
Message
sing4you
sing4you
Right there with Babe
Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)

Group: General Forum Members
Points: 778 Visits: 2093
That was an excellent article. I look forward to seeing the workaround!

Thanks.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207416 Visits: 41961
Hi Jeff,

thanks for this article.

A solution to this problem is to use procedural code (at least for SQL2K).
Probably not the intention of the author... BigGrin


Thanks for the feedback and the code, Christian. Heh, yeah, as you can see, the WHILE loop is much, much faster in this case... using triangular joins would cause a run that you'd probably cancel after 20 minutes...

I'll be sure to include some "timings" in the upcoming article(s).

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207416 Visits: 41961
"Interesting, but where's the hint as to how to avoid/mitigate the problem?"...
In case you're interested, a simplified explanation of problem is invoices that get queries raised.

Yeaup.... that's where I first ran into similar problems... millions of invoices...

I look forward to seeing your later articles.

Thanks, Derek... sorry for the "teaser".

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207416 Visits: 41961
Heh... that makes a pretty cool sounding acronymn, too... "SbS" BigGrin

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207416 Visits: 41961
Jason Selburg (12/6/2007)
Jeff Moden (12/5/2007)
....... I finally got 2k5 and will have to load it before comparing the methods to the RANK methods for running counts performance wise.


Well it's about time ! w00t


Heh... that what it's always been about... "time"... Tongue

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)SSC Guru (207K reputation)

Group: General Forum Members
Points: 207416 Visits: 41961
sing4you (12/6/2007)
That was an excellent article. I look forward to seeing the workaround!
Thanks.


Thank you for the both the feedback, and the encouragement!

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31077 Visits: 8669
Nicely done Jeff! The graphics will really help even neophyte SET-BASED devs to understand what is really going on.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
peterzeke
peterzeke
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1890 Visits: 1766
Jeff:

Great article. I'm looking forward to your next article that includes the solutions to avoiding triangular joins and RBAR!

--Pete



Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28385 Visits: 18999
Nice one Jeff - I can see it's been a little while in the making (given all of the handy graphics...)

You might care to edit your first post in this thread (where the link to the article is supposed to appear). Looks like the link got "et" (southern for "eaten").

Link is Hidden RBAR: Triangular Joins

Not paying attention to the cardinality of a query is definitely an issue. Even the best of set processors will eventually choke on sets with polynomial growth factors.

And - welcome to the SQL2005 family (at last). I'm curious to see if your view of it changes at all.....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Jason Selburg
Jason Selburg
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6730 Visits: 4115
Matt Miller (12/6/2007)
.....And - welcome to the SQL2005 family (at last). I'm curious to see if your view of it changes at all.....



Yes Jeff, come to the dark side! Alien

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search