Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Hidden RBAR: Triangular Joins Expand / Collapse
Author
Message
Posted Thursday, December 6, 2007 6:52 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, August 29, 2014 6:54 AM
Points: 499, Visits: 1,994
That was an excellent article. I look forward to seeing the workaround!

Thanks.
Post #430155
Posted Thursday, December 6, 2007 6:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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... :D


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #430160
Posted Thursday, December 6, 2007 7:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
"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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #430165
Posted Thursday, December 6, 2007 7:08 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
Heh... that makes a pretty cool sounding acronymn, too... "SbS" :D

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #430170
Posted Thursday, December 6, 2007 7:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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 !


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


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #430171
Posted Thursday, December 6, 2007 7:11 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #430173
Posted Thursday, December 6, 2007 7:38 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 7:50 PM
Points: 4,351, Visits: 6,167
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 at GMail
Post #430189
Posted Thursday, December 6, 2007 7:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, July 27, 2014 8:47 PM
Points: 316, Visits: 1,483
Jeff:

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

--Pete



Post #430208
Posted Thursday, December 6, 2007 8:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 5:24 PM
Points: 7,139, Visits: 15,191
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?
Post #430214
Posted Thursday, December 6, 2007 8:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 2,717, Visits: 3,844
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!


______________________________________________________________________

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
Post #430222
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse