Post reply

table order in joins and performance

  • April 14, 2011 at 9:33 am

    #145078

    I've got a query that uses 10 tables. Some of these tables are very small - 10 records while four of these tables are very large - thousands of records. When joining these together, should I start with the largest first or the smallest? I'm assuming it does matter or is it just a matter of having the proper indexes?

  • April 14, 2011 at 9:55 am

    #1311703

    On SQL Server the order of your joins doesn't matter, because query engine will reorganize JOIN order depending on statistics for indexes and other things.

    To prove it to yourself, what you wnat to do is compare execution plans.

    Get the actual execution plan and look at it, then change the order of your joins and run it again; when you compare the execution plans, they will be identical, because, like I said, SQL Server will reorder to it's own liking.

    After that, If you want to see if it really makes an impact or not.Add OPTION (FORCE ORDER) to the query and you will see that it matters.

    AS a general rule of thumb, JOIN order should be with table of least records on top, and most records last, as some DBMS engines the order can make a difference, as well as if the FORCE ORDER command was used to help limit the results.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply