Home Forums SQL Server 2005 Administering Composite Index - Two Different Tables - Order By RE: Composite Index - Two Different Tables - Order By

  • I guess my intitial response would be...

    1. Why are you doing a sort in a view? In other words, why is the sort required and what/where will the result set be used for? Sorts in a view are generally considered to be a "worst practice" although, like everything else, there are exceptions.

    2. Do you know how to read the "Actual Execution Plan"? And, it's ok to say "No".

    3. How often does the data in the underlying tables change?

    4. I could be wrong but it seems obvious that you're having performance issues. Is it possible to comply with the article at the second link in my signature line below?

    As to your general question, a view will use the indexes of the underlying tables just as if you had written a stored procedure or even just a script with the same query in it. If the indexes aren't being used correctly for the script version of the query, then the indexes won't be used correctly for the View. Using an indexed view to materialize the data may be one workaround but that has some serious implications for tables that suffer a good number of inserts or updates. Generally speaking (there are, of course, exceptions but they're more rare than not), the creation of an indexed view for performance is a poor substitute for correct tuning because, ironically, the indexed view will perform better if its code is tuned and the tuned (or just properly written) code may totally negate the need for an indexed view.

    --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)