SQL Server Performance

Writing a good query!! (WIERD QUES)

Discussion in 'Performance Tuning for DBAs' started by lpname, Sep 5, 2004.

  1. lpname New Member

    HI ALL!

    Posting a wierd question regarding a good query. I want to know if i am joining 5-6 tables with huge data, is there any sequence i should put my table names? e.g.
    select asd, sdf, fgh, from a, b, c
    where Valid condition.

    In the above example C is having 9871000 records, A is having 871000 records and B is having 4568900 records.


    Thanks!!
  2. lpname New Member

    hi gurus,

    any suggestions?
  3. Adriaan New Member

    I think the rule-of-the-thumb is to start with the join that returns the smallest number of rows. So if you join A to B and then B to C, and the join on B and C returns less rows than the one between A and B, then you start with B and C. (By the way, you ARE using joins, right?)

    Also, use INNER joins instead of LEFT or RIGHT joins if you include criteria on the outer table or if there must be a match in the outer table.
  4. satya Moderator

    http://www.sql-server-performance.com/hints_join.asp for reference in addition to what Adriaan referred.

    If you're using SQL enterprise edition then can take advantage of Indexed views.
    Using hints in your queries has its place; however most uses of hints should be limited & Performance adding hints can help when no other solution can be found.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. lpname New Member

    quote:Originally posted by Adriaan

    I think the rule-of-the-thumb is to start with the join that returns the smallest number of rows. So if you join A to B and then B to C, and the join on B and C returns less rows than the one between A and B, then you start with B and C. (By the way, you ARE using joins, right?)

    Also, use INNER joins instead of LEFT or RIGHT joins if you include criteria on the outer table or if there must be a match in the outer table.

    Thanks for the reply!! Is there any white paper where this is stated? I think this works, but i need more info/support as this is a production change.

    i hope you understand!!
  6. Adriaan New Member

    Perhaps I should have started my reply stating that I don't qualify as a 'guru' here [<img src='/community/emoticons/emotion-2.gif' alt=':D' />] ... Sorry, it was really an IMHE type of response.<br /><br />Anyway, you should always test this sort of change in a development environment. In most cases, SQL will already have figured out the best execution plan as to the order in which to process the joins, but it can't hurt to try this way.<br /><br />It can also depend on any ad-hoc criteria that you supply. If you really want to force it one way or the other, you could use derived tables to handle specific joins before others.
  7. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Thanks for the reply!! Is there any white paper where this is stated? I think this works, but i need more info/support as this is a production change.<br /><br />i hope you understand!!<br /><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Can you get your hands on Inside SQL Server 2000 by Kalen Delaney? <br />Has a _very_ detailed explanation about JOIN procesing.<br /><br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />--<br />--Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
  8. chopeen Member

    Another advice I often find very useful.

    Let's say that there is a join like this:
    tableA A
    INNER JOIN tableB B ON A.id = B.tableA_id
    and you know that there's a lot rows in tableB with NULLs in tableA_id column that you are not going to need.

    Then it is always a good idea to include this information in the query:
    tableA A
    INNER JOIN tableB B ON A.id = B.tableA_id AND B.tableA_id IS NOT NULL

    --

    Marek 'chopeen' Grzenkowicz, MCP
    Poland
  9. Twan New Member

    I'd say that you shouldn't worry about the order of the tables in the from clause. It shouldn't make any difference if you are using inner joins... The optimiser doesn't take the order of the tables into consideration unless there is a large number of tables (I can't remember the exact breakpoint number...) after which you want to make the order of the tables make logically sense... if you're using the new ansi format then that means making sure that the columns in the ON clause relate to the tables being joined

    e.g.

    from table1 a
    inner join table2 b
    on a.col1 = b.col
    inner join table3 c
    on b.col2 = c.col2

    should be rewritten as

    from table1 a
    inner join table2 b
    inner join table3 c
    on b.col2 = c.col2
    on a.col1 = b.col


    since the second inner join only relates to table2 and table3

    Cheers
    Twan

Share This Page