Post reply

Indexed View - WTF?

  • September 28, 2005 at 11:50 am

    #110483

    I have an indexed view that joins two rather large tables.  When I execute a query against these two tables with a where clause that has only one parameter the execution plan shows that the indexed view is being used (this is a good thing).  However, when I execute a query that has two clauses in the WHERE that are AND'd together, the execution plan shows that it fails to use the indexed view and instead uses indexes on the underlying tables.  Both of the columns in the where are in the View's unique clustered index.  WTF? 

    Any help, suggestions, etc would be appreciated.

     

     



    A.J.
    DBA with an attitude

  • September 28, 2005 at 11:57 am

    #593775

    Can we have the specifics (table and views ddl (with indexes and keys, execution plans of each calls to the view and the queries themselves.)

  • September 28, 2005 at 12:37 pm

    #593783

    Disregard this post, I managed to figure it out.



    A.J.
    DBA with an attitude

  • September 28, 2005 at 12:40 pm

    #593785

    What was it??? I'm figuring wrong indexation or an unexpected convert but I'd like to know for sure .

  • September 28, 2005 at 12:41 pm

    #593787

    I had to re-order the columns in the clustered index on the view, and that seemed to fix my execution plan(s).

     



    A.J.
    DBA with an attitude

  • September 29, 2005 at 2:22 am

    #593956

    Still odd that SQL didn't see fit to use your index, unless the first column had a lot of duplicate data perhaps?

    I wonder what would have happened if you switched the order of your where statements.  I have read posts where ppl claim changing the order of statements in the where clause makes significant differences - in practice I haven't seen it happen.  Would be nice to get a confirmed case of it occurring!

    Could it be that the first column of your old clustered index was unsuitable due to, as Remi said, an unexpected convert, and now that you have switched columns it just happens to work but is only using the former 2nd column?

    For your one parameter query, to which column did it apply?  The first column or the 2nd in your original clustered index?

    Sorry to ask so many questions, just want to understand what you did to fix it - also so when others ask similar question, they may have searched the board and found the fix themselves... 

  • September 29, 2005 at 7:01 am

    #594009

    Yup, those plans would have been nice to see .

Viewing 7 posts - 1 through 6 (of 6 total)

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