Post reply

Joining of small & larger tables and query optimization

  • December 5, 2014 at 12:22 pm

    #313645

    Hi,

    I am getting training in query optimization. I do have some doubt

    What is the better way of joining two tables of different sizes?

    Is it better to retrieve the columns from small table and joining bigger table or joining bigger table.

    Not clear how the order of table join will effect on the performance?

  • December 5, 2014 at 12:26 pm

    #1763058

    Unless you force the order (which you shouldn't in most cases), it really doesn't matter which table you use first in your query. SQL Server will decide the best option for joining the tables. Remember that SQL is a declarative language, you should tell the engine what instead of how.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • December 5, 2014 at 1:42 pm

    #1763064

    If you want to know in details how SQL Server is joining tables you have to learn 3 types of joins: Nested loops, merge and hash. You can start here: Advanced Query Tuning Concepts

    Alex Suprun

  • December 6, 2014 at 3:55 am

    #1763114

    Just define the JOIN criteria. Let SQL Server handle the rest. It will be best if there is a foreign key between the two tables and that foreign key is trusted by using the WITH CHECK option when creating the foreign key. Also, foreign key relationships often benefit from indexes on the foreign key column(s). But, other than those things, just make sure you use the key when defining your JOIN criteria and the optimizer will take it from there.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • December 6, 2014 at 7:11 am

    #1763119

    Thanks all

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

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