Post reply

Composite Index - Two Different Tables - Order By

  • August 9, 2013 at 8:22 am

    #275967

    I have a view which is performing a order by on three columns a/b/c, a/b are indexed and is from table 1, c is from table2. How are the indices used in this case? How does sql use two different indices on two different tables? I am trying not to use indexed views or is indexing the view only way? This is more of a general question.

  • August 12, 2013 at 10:10 pm

    #1640776

    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)

  • August 13, 2013 at 1:35 am

    #1640815

    Honestly, the question is a little too vague to answer. Depends on any filtering done, the joins and a whole lot more.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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