Post reply

View and its execution plan

  • May 6, 2010 at 12:35 am

    #140701

    HI,

    I have created a indexed view by joining tabl1 and table2, i have used that view in on stored procedure.

    But in another stored procedure i have directly join the two tables, By examining the execution plan it's using the view.

    what is the reason?

    How can remove a view from execution plan?

    thanks

    🙂

  • May 6, 2010 at 1:10 am

    #1162980

    Why would you want to ?

    The optimizer has done its job and found that using the view will give a good plan.


    Clear Sky SQL
    My Blog[/url]

  • May 6, 2010 at 1:14 am

    #1162983

    I want to know if we are using the two different objects(view and table)

    then why it is taking the same execution plan.

    And i found,

    view_1 -- with more columns( indexed view with table1 and table2 )

    view_2 -- with less columns( indexed view with table1 and table2 )

    i have created one more indexed view by reducing the columns, but it is still using the first view( in execution plan) why it is referencing the first view ( view_1).

    thanks,

    🙂

  • May 6, 2010 at 1:37 am

    #1162988

    did you re compiled the sp

  • May 6, 2010 at 1:39 am

    #1162989

    It could be referencing the first view because its using a cached plan (use DBCC FREEPROCCACHE to clear the cache, note that this is not advisable in a live environment) or that using the first view has given a 'good enough' plan. The optimizer's role is not to return perfect plans but 'good enough' in a short amount of time.


    Clear Sky SQL
    My Blog[/url]

  • May 6, 2010 at 2:26 am

    #1163003

    SQL has the ability to use an indexed view even if the view wasn't explicitly referenced in the query, if by doing so it can produce a cheaper plan and a more optimal query. One place you'll notice this is if your indexes view computes aggregates, then you run a query that computes those aggregates from the base table. Since the aggregates are pre-computed in the view, it's quicker to query the view rather that to query the base table and recalculate all the views.

    Why do you not want SQL using the view? If it's doing so, it believes that the indexed view is faster

    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 6 posts - 1 through 5 (of 5 total)

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