| Author |
Topic |
|
MuadDBA
628 Posts |
 Posted - 2009-11-16 : 12:41:37
|
| At PASS, I had the pleasure of attending most of a session by Itzik Ben-Gan, who always has some great tips and tricks for using T-SQL. One of his tips was how to force SQL Server to use a bushy join plan, something the optimizer is capable of but will never choose because it always picks "good enough" plans, and bushy join optimization requires more analysis. Unfortunately, I had to leave that session before it completed, and I did not see if he gave any tips on when a bushy join is a good thing, and when it might be bad. Google is not helping me out either, so if anyone canlink me to, or provide a brief primer on, bushy joins, I would appreciate it. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
MuadDBA
628 Posts |
Posted - 2009-11-16 : 13:03:06
|
| Well if the PASS recordings were posted I would link you to one, but they basically allow you to directly join the product of a join to another join product....it would be easier to explain using a picture, but they have a brief explanation of it in wikipedia. Here it is:One can classify query-plans involving joins as follows:[4]left-deep using a base table (rather than another join) as the inner operand of each join in the plan right-deep using a base table as the outer operand of each join in the plan bushy neither left-deep nor right-deep; both inputs to a join may themselves result from joins |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-11-16 : 13:46:28
|
quote:
Originally posted by X002548
I'm sure we can find lots of references to bushy
you mean like this?Be One with the OptimizerTG |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
MuadDBA
628 Posts |
Posted - 2009-11-16 : 13:53:15
|
| This is not a joke, though I know I wrote a funny topic title. Even the "left deep, right deep" stuff has me chuckling, but it is dead serious. Perhaps I am going to have to e-mail Itzik himself?Here's a link to the code on Itzik's site that includes bushy joins:http://www.solidq.com/insidetsql/books/source_code/T-SQL%20Tips%20&%20Tricks.zip |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-11-16 : 13:55:00
|
| In my experience, bushy joins look like a great idea at first, then they get a bit messy (although fun), evenutally the constraint goes in, then out, then in, evenutally it looses interest and never calls the tuple back. |
|
|
|
MuadDBA
628 Posts |
Posted - 2009-11-16 : 14:00:37
|
quote:
Originally posted by tkizer
I'm still a little unclear, but it sounds like it may involve derived tables.Tara Kizer
Nope, no derived tables. Just a different join type that no one sees because the optimizer will never choose it unles forced. Yes, tee, hee, forcing a bushy join is not a good idea...ahem. The optimizer only "optimizes" until it finds a plan that is "good enough" it does not evaluate all options in order to save time. Therefore it never gets to the level of evaluating this more advanced style of join. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2009-11-16 : 14:16:57
|
quote:
Originally posted by crazyjoe
This is not a joke, though I know I wrote a funny topic title. Even the "left deep, right deep" stuff has me chuckling, but it is dead serious. Perhaps I am going to have to e-mail Itzik himself?Here's a link to the code on Itzik's site that includes bushy joins:http://www.solidq.com/insidetsql/books/source_code/T-SQL%20Tips%20&%20Tricks.zip
Already did that and gave him a link to this postBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-11-16 : 17:25:44
|
| Here are shots of the example plans:Non-bushy: http://imgur.com/Ht7hW.pngBushy: http://imgur.com/fGFWR.pngBased on the query hint and the parentheses, the bushy join makes the optimizer work on 2 joins at a time, instead of 1 at a time. The plan shows how that would parallelize more easily than the non-hinted version. This is mentioned in the Wikipedia article. |
|
|
|
MuadDBA
628 Posts |
Posted - 2009-11-16 : 20:02:53
|
| Thanks for the images, Rob. One thing to note, however, is that the parentheses are not strictly necessary. They make it easier to comprehend, but if structured correctly, no parentheses need to be used. |
|
|
|
TallCowboy0614
Starting Member
17 Posts |
Posted - 2009-12-01 : 14:02:08
|
| I think the Brazilian plan renders Bushy Joins obsolete._________________________________aka "Paul"Non est ei similis. "He's not the Messiah. He's a very naughty boy!" - Brian's mum |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2009-12-02 : 12:53:36
|
quote:
Originally posted by TallCowboy0614
I think the Brazilian plan renders Bushy Joins obsolete._________________________________aka "Paul"Non est ei similis. "He's not the Messiah. He's a very naughty boy!" - Brian's mum
Unless it's a LEFT BUSHY JOINBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2009-12-02 : 13:26:37
|
I prefer the old school +1 post count pumper myself  http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
|
What are the benefits of a bushy join?
