Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82

    Unanswered: Why does table order in FROM clause affect query plan?

    Hello,

    I run query with 5 tables and if I change order of those 5 tables in from clausule I got different plans...

    But according manual (see link below) I think optimizer should create the same plan? Or not?

    http://infocenter.sybase.com/help/in...ptallbk788.htm

    David
    Last edited by Musil David; 01-15-08 at 10:06.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    I've seen such behavior when the statistics are incorrect (e.g. indicating empty tables)
    Do an update statistics.

  3. #3
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Quote Originally Posted by Musil David
    Hello,

    I run query with 5 tables and if I change order of those 5 tables in from clausule I got different plans...

    But according manual (see link below) I think optimizer should create the same plan? Or not?

    http://infocenter.sybase.com/help/in...ptallbk788.htm

    David
    As the link says, it does it in sets of 4 tables. And you said, you have 5, That could be one of the reason.

  4. #4
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82
    Quote Originally Posted by trvishi
    As the link says, it does it in sets of 4 tables. And you said, you have 5, That could be one of the reason.
    But there is no said, that from clausule order affects selection of these 4 tables!!!

  5. #5
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Quote Originally Posted by Musil David
    But there is no said, that from clausule order affects selection of these 4 tables!!!
    What exactly are the differences you are seeing. The join order or something else?

  6. #6
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82
    Yes, with showplan on there is different order of tables. If I move table that is in from clausule the last (this table appears also last in show plan) and I place this table on first place in from clausule, than this table moves also in showplan (it is now 3rd in show plan)....

    P.S. update statistics doesn´t seem helped....

  7. #7
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Quote Originally Posted by Musil David
    Yes, with showplan on there is different order of tables. If I move table that is in from clausule the last (this table appears also last in show plan) and I place this table on first place in from clausule, than this table moves also in showplan (it is now 3rd in show plan)....

    P.S. update statistics doesn´t seem helped....
    Ok try dbcc traceon 302,310 and 317. It will give you why the optimizer choose a particular join order.

    http://infocenter.sybase.com/help/in...ing/X88753.htm

  8. #8
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82
    I placed my query plans there, there is also output from dbcc traceon(3604, 310) too...

    Look at the table D5...it is last in from clausule and also last in query plan...

    http://sweb.cz/dammieX/1.txt

    Now is table D5 first in from clausule and third in query plan...

    http://sweb.cz/dammieX/2.txt

    True to say, I don´t very well understand output from dbcc...
    Last edited by Musil David; 01-17-08 at 13:53.

  9. #9
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Ok now you believe it takes only 4 tables to consider whats the best outer and inner tables?

    Again, no one really gets into such detail unless it really hurts them performance wise and it is in fact real painful to decipher how the optimiser has done something compared to how its supposed to have deciphered it etc...I will try to go thro the plans and see if I can figure out anything. But you need to post the showplan output too.

    The most important part in trace 310 is the cost of query.. Thats how the optimiser decides the join order.

    Sorry just saw the query plans at the end of ur text file
    Last edited by trvishi; 01-17-08 at 19:51.

  10. #10
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82
    I did know, that 4 tables are selected but these 4 tables are from 5 tables... So it cannot depend on sort order in from clausule because optimizer do permutations of 5 in group of 4!!!

    I don´t think that this is such a detail... I really care for performance, so I would like to know that optimizer is shit and I have to care for such a details as where write another table in from clausule...

  11. #11
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Come on mate. The optimizer is not that bad

    Ok. had a quick look at the showplan and first things first.

    You probably already noticed this and may also know what it means but for the benefit of the forum here goes:

    How many rows are in tables D2,D1 and D3 as its doing table scans?

    Nested loop join goes in a loop for every row in the outer table and refer it to inner table.

    Lets take the first two tables in the join order in your example.

    D2, D1

    So, it will scan D2 once, but for every row in D2 it will do as many table scans of D1.

    Will see if I can get some time to look at the 310 output.

  12. #12
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Cant access the urls anymore.

  13. #13
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82
    Sorry, I created them again...

    Query 1

    http://sweb.cz/dammieX/q1.txt

    and dbcc traceon 310 + shoplan at the end

    http://sweb.cz/dammieX/1.txt

    Query 2 (only table D5 was moved on 1st place in clausule FROM)

    http://sweb.cz/dammieX/q2.txt

    and dbcc traceon 310 + shoplan at the end

    http://sweb.cz/dammieX/2.txt

    + sp_spaceused information added there...

    http://sweb.cz/dammieX/3.txt
    Last edited by Musil David; 01-23-08 at 04:40.

  14. #14
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Thanks ... I will still try to answer your original question if time permits about the join order.... But in this case its useless in my opinion even if we get to to the bottom of your original question.

    Theres not too many times one think about join orders because they go thro the basics first and hence very less opportunities to analyse the advanced stuff..

    So before questioning the optimiser , all your 5 tables are 10 million + rows and you are doing a table scan in 3 of the 5, which really makes me wonder why you are so bothered about the join order.

    You should be worrying more about the table scans first

    Just my 2c.

    --------

    Hmmm the last time I saw it did table scans on 3 tables... But now with the new output, its NOT... Did you create indexes etc.. ??

    If so, you are kinda sending me on a wild goose chase as I almost spent about a hour looking at your old one scratching my heads Remember , join order is only ONE part of why a query plan is what it is...

    Anyways, hope things got better with you creating indexes... If you do want to learn more about the optimizer, I suggest you attend one of the 5 day courses which Sybase provides for ADVANCED SQL. If I do find anything on your recent attachments I will let you know
    Last edited by trvishi; 01-24-08 at 17:39.

  15. #15
    Join Date
    Sep 2006
    Location
    Czech Republic
    Posts
    82
    Thanks for reply. I very appreciate that you´ve paid attention to my problem...

    I apologize for misleading you with indexes... I forgot to mention....

    So, is there possibility that (even if you use indexes) there can be more plans that cost same and so optimizer get (first) one of them (order of plans depends on order behind from clausule...)...?

Posting Permissions

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