Results 1 to 25 of 25

Thread: Question about join order

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Question about join order

    Hi.
    I'm trying to join some large table.
    I have a question though.
    Is the starting table on join the one that a where condition will result in less rows and better filtering or is the starting table the one that will bring less rows when joining with another table?
    I'm a little confused on what table should go first in the Join order.
    Thanks.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Question about join order

    Try both and see what the execution plan looks like in Management Studio, assuming SQL Server.

  3. #3

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Question about join order

    I forgot the (s).
    Join large tables.
    So this isn't as simple a looking at the execution plan as it will go down a couple of thousand of diagrams.
    So I just asked to see if I can get a head start before starting to join.
    So assuming that a where condition on a master table will lead to half the rows returned and a join condition on a master table will again leave half the rows returned.
    What is better? Or it is the same? On the first attempt we are not joining tables but use a filter, on the second we join tables.
    I guess this will also have to do with which column we are joining and how it is indexed but is there a general rule of the thumb?
    Thanks.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  4. #4
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Question about join order

    I always start with the smallest table I can... the one with the least row... the sooner you remove rows the faster the query will be
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Question about join order

    The order the tables appear in the query has no affect at all. The engine will use the statistics to project the most efficient possible execution so you should generally design a single query containing all appropriate where and join clauses - let the engine decide how to best process that.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  6. #6

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Question about join order

    Thanks all.
    I was under the impression that joining order do matter.
    Of course not for simple queries but for complex queries that the engine might not be able to interpret the optimal solution plan due to multiple filter and indexes.
    I saw some mathematical computations (the I had a hard time understanding) that will "push" to the optimal joins in accordance to the index and filtering of the table.
    If that stand as true then I think what Gary suggest is what I also had in my mind but with the twist that the "smallest table" is actually the one that gives the lowest table / per row result in accordance to the filters used. So the "smallest" table might well be the biggest table that is well filtered.
    Is there any truth in this or I am blubbering without reason here?
    Thanks.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  7. #7
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Question about join order

    I think both side are right to a certain extant...the compiler will use statistics to "pick" the order but it doesn't always know best. There are times the join order matters. Here is a nice link reviewing the approaches.

    https://bertwagner.com/2017/11/21/do...tables-matter/

    For those who don't want to view it here is the summary at the end:

    Summary

    Table join order matters for reducing the number of rows that the rest of the query needs to process.

    By default SQL Server gives you no control over the join order – it uses statistics and the query optimizer to pick what it thinks is a good join order.
    Most of the time, the query optimizer does a great job at picking efficient join orders. When it doesn’t, the first thing I do is check to see the health of my statistics and figure out if it’s picking a sub-optimal plan because of that.

    If I am in a special scenario and I truly do need to force a join order, I’ll use the TOP clause to force a join order since it only forces the order of a single join.

    In an emergency “production-servers-are-on-fire” scenario, I might use a query or join hint to immediately fix a performance issue and go back to implement a better solution once things calm down.
    Last edited by TysonLPrice; Jun 12th, 2019 at 07:23 AM.
    Please remember next time...elections matter!

  8. #8
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Question about join order

    I have seen changes in the performance by changing the order of the joins... I went from 1 query taking 2 min to run to completing in under 4 sec just by changing the order... I will say that the query was using a view and that was original first thing in the From Clause, by moving that down in the order and selecting a table with few rows as the starting point it made a large difference in performance. I stopped there as that was a good enough gain for me on performance and didn't tune further
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  9. #9

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Question about join order

    Quote Originally Posted by GaryMazzone View Post
    I have seen changes in the performance by changing the order of the joins... I went from 1 query taking 2 min to run to completing in under 4 sec just by changing the order... I will say that the query was using a view and that was original first thing in the From Clause, by moving that down in the order and selecting a table with few rows as the starting point it made a large difference in performance. I stopped there as that was a good enough gain for me on performance and didn't tune further
    So I take it that the execution plan changed the join order, or it changed other properties like loops calculations etc?
    I think I would have to test these out to see if what Tyson linked to stands.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Question about join order

    I think both side are right to a certain extant
    I'd recommend everyone read that article carefully. I'll summarise (and, admittedly, paraphrase):-

    1. The order in which the engine applies the joins absolutely matters.
    2. The order in which you write the joins in your query has absolutely zero effect on point 1. SQL Server will disregard your ordering in favour of it's own.
    3. You can use index hints and topped sub-queries to force join ordering but that has nothing to do with the order you write the joins in your query.

    I have seen changes in the performance by changing the order of the joins
    I can't explain that without having seen it but I'm pretty sure something else was going on other than just changing the order in which you expressed the tables.
    Last edited by FunkyDexter; Jun 12th, 2019 at 07:49 AM.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  11. #11
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Question about join order

    Quote Originally Posted by FunkyDexter View Post
    I'd recommend everyone read that article carefully. I'll summarise (and, admittedly, paraphrase):-

    1. The order in which the engine applies the joins absolutely matters.
    2. The order in which you write the joins in your query has absolutely zero effect on point 1. SQL Server will disregard your ordering in favour of it's own.
    3. You can use index hints and topped sub-queries to force join ordering but that has nothing to do with the order you write the joins in your query.

    I can't explain that without having seen it but I'm pretty sure something else was going on other than just changing the order in which you expressed the tables.
    I'm pretty sure it makes a difference between inner and outer joins. I'll need to look for verification.
    Please remember next time...elections matter!

  12. #12
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Question about join order

    I'm pretty sure it makes a difference between inner and outer joins
    Not 100% sure what you mean by that.

    Do you mean that using an outer instead of an inner could change the performance? If so, yes. But it could also change the logical result so it's really a different query at that point and the optimiser has to treat it in a completely different way. You couldn't meaningfully compare them.

    Or do you mean that the order of joins would affect performance in a query that contained outers (e.g. swapping the table order and changing a left to a right or vice versa)? In which case the same rules apply as above: in the absence of any hints, etc, the engine will simply disregard your ordering and calculate an execution plan based on the stats.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  13. #13
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Question about join order

    Quote Originally Posted by FunkyDexter View Post
    Not 100% sure what you mean by that.

    Do you mean that using an outer instead of an inner could change the performance? If so, yes. But it could also change the logical result so it's really a different query at that point and the optimiser has to treat it in a completely different way. You couldn't meaningfully compare them.

    Or do you mean that the order of joins would affect performance in a query that contained outers (e.g. swapping the table order and changing a left to a right or vice versa)? In which case the same rules apply as above: in the absence of any hints, etc, the engine will simply disregard your ordering and calculate an execution plan based on the stats.
    Admittedly I'm a little over my head on this...I'm going on "what I've always thought" and haven't found anything contradicting what you are saying. I've spent a few minutes looking but struck out.
    Please remember next time...elections matter!

  14. #14
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Question about join order

    The execute plan exited saying using best found but not optimized changing the order resulted in a better plan that is all I'm saying
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  15. #15
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Question about join order

    I'm looking for an article to back that up ('cause I really hate being proved wrong) and I can't find one.

    I can find some examples where people force the order the engine applies them to prove that it makes a difference. It will. See point 1 above.

    I can find example where folks say that the optimiser gives up before trying all the possible permutations. Which is not what the optimiser typically does. It doesn't "try out" different join orders, it uses heuristics across the stats to prioritise the most selective operations (that's an over-simplification, it accounts for all sorts of other factors like coinciding ordering on available indexes etc).

    The closest I can find is a mention of Grant Fitchley providing an example in SS2008 but there was no link and I can't find the article through google. He really knows his stuff so if I could find that I'd be happy to eat my words.

    @Gary, if you managed a query that forced the optimiser to drop out you could see the result described but the answer there is to work out what's breaking the optimiser. It wouldn't be simple table ordering.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  16. #16

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Question about join order

    From my understanding all but inner joins are affected performance when ordering.
    At least that's what I can make out.
    In most of our big queries there is a 90% left right joins and 10% inner joins. So ordering makes a difference.
    Or I'm confused
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  17. #17
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Question about join order

    @Funky... the optimizer only runs for a period of time... if it doesn't find the best plan it takes one that is the best it could get... Moving the view down the list made the optimizer find a little better plan.. I also tried to force the removal of the use of views for this but was told basically to sit down and shut up... one of the main reasons I left the job
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  18. #18
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Question about join order

    I also tried to force the removal of the use of views for this but was told basically to sit down and shut up
    Ha! Yeah, been there.

    Actually, views, particularly if they're nested more than a single level deep, are one of the worst things I've seen for breaking the optimiser. Surfacing that stuff up into the main query probably would have fixed your problem. There's no accounting for management, though.

    They have their place in the name of code re-use so I wouldn't ban them entirely but I'm a firm believer in keeping them as rare and shallow as possible.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  19. #19

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Question about join order

    We actually started developing views (despite my strong opposition) two years ago on a new Cube -- Analysis project after join selection method.
    Selecting joined tables trough a view by deviating a little from the straight forward order would result in + (do your best guess) minutes from the original table selects.
    The problem gets worse when we need to find and issue and we have to go through views that are joined with other views that are joined with views that are joined with the first view!
    I can't really express my frustration on this but I tend to write down all the paths - labyrinths so I don't have to start a fresh search again and again that will take a couple of minutes before I could run the first select on the core table(s).
    So yeah. I love views!
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  20. #20
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Question about join order

    The thing that finial broke me was the edict: No stored procedures.. We will use all views and functions only. That and of course the Well GUIDS are more secure that integers are, even after showing them the performance hit it's again just sit the F down and shut up... I left within a week after that
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  21. #21

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Question about join order

    I am able to have control or the projects if I can get a head start. So I just swiftly create some core sprocs and selects and eliminate the views potential.
    I was too late on the aforementioned project. The selects would run relatively fine for some tables that have less complex views but other tables can take some time to load.
    This is treated as "expected" time latency but I suspect, in the future, when the tables start to grow, we are going to have serious issues.
    The problem here is even if I say "I told you so", the answer would be "yeah yeah, just fix this now" .
    So, that falls into the original question because I'm trying to get a head start on those tables, without the views. I'll try to join small to larger filter / ratio tables , even if , it does or does not matter, just to be safe and see what I can make out.
    Thanks.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  22. #22
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Question about join order

    Personally I'm not a fan of SPs when they're used to embed business logic in the database which is something I see often. I know that's debatable, though, and a lot of people like that model. Other than that, they're a tool and they provide a benefit. I honestly don't think there's any tool in the sql arsenal that I'd ever say "never use that". Balance the cost vs the benefit in all such things.

    I'm utterly mystified as to where "GUIDs are more secure than integers" came from. Less human readable perhaps but that's not the same thing as secure.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  23. #23
    PowerPoster PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Pontypool, Wales
    Posts
    2,458

    Re: Question about join order

    Quote Originally Posted by FunkyDexter View Post
    Personally I'm not a fan of SPs when they're used to embed business logic in the database which is something I see often. I know that's debatable, though, and a lot of people like that model. Other than that, they're a tool and they provide a benefit. I honestly don't think there's any tool in the sql arsenal that I'd ever say "never use that".

    I'm utterly mystified as to where "GUIDs are more secure than integers" came from. Less human readable perhaps but that's not the same thing as secure.
    The only time I have seen a justification for GUIDs vs Integers on security grounds is when they are visible to the end user, especially if they are part of a URL or similar - integers are often a simple incrementing number and therefore predictable - this can then be the starting point for an Insecure Direct Object Reference exploit. Then again if the security is being done properly and permissions are checked on the underlying objects this shouldn't be an issue anyway....

  24. #24
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Question about join order

    That makes sense. Although I'd say it's a fundamental that PKs never leak out into the outside world. Pretty much every web tutorial I've ever seen did exactly that, though.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  25. #25
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,495

    Re: Question about join order

    If my security is that poor both getting into the database server and into the DB then we probably have more pressing issues to address
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width