Post reply

All Joins at once, or only when View is queried?

  • July 16, 2010 at 3:55 pm

    #224163

    I have a view which brings together a bunch of data such as employee ID, Date, Result, ContaminantID, etc. It is used to populate a table to store the results for contaminants for a given date. I also use this view to run several reports off of. Some of the reports require extra information about the employee which is held in tables that have relations to the employees table.

    So question is this, should the first view join to all the tables, including the employee related tables from the start so we can use simple queries to get the data from 1 unique place, or should I leave the view alone and when it is needed for the report, just query the view and join it to the required tables to get the rest of the information the report requires?

    The view starts at a low level, then works itself to the top layer using derived tables. I guess I just want to know if one way of the other would improve performance. I.e. I would think gathering all the relavent data at the lowest level would be ideal, because then when you query the view it would only pull the data that is required and wouldnt even make the other joins if they weren't needed to get the final result set.

    Opinions? Help?

  • July 16, 2010 at 8:50 pm

    #1194452

    My answer would be... try it. Check the actual execution plans of both methods and the load it puts on resources using SQL Profiler.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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