Post reply

proc on a view quicker than the same proc with table joins

  • March 1, 2011 at 3:33 pm

    #231960

    hi,

    i was working on a huge complicated proc and later realised that may be i can use base tables directle instead of using view (because this view will select all rows in the tables). So I flipped the views with tables and did run profiler to chk performance.

    I was surprised to know that proc with view in the from caluse was 1 sec quicker than same proc with base table.

    Does anybody have any idea ?

    Thanks

  • March 1, 2011 at 3:35 pm

    #1293263

    Is the view and indexed view?

    Also, is your logic between the view and the table joins the same (same filters in the where applied etc)?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • March 1, 2011 at 3:45 pm

    #1293268

    yes view is indexed and tables have indexes too (exectly the same). and infact, view has extra filter 'user_name () = user'

    ....same number of rows in result set.

  • March 1, 2011 at 3:55 pm

    #1293272

    With the view being indexed, you have essentially materialized the data into a CI and thus you will see those performance gains because the data is in the CI and the query to the view is pulling the results from there rather than the underlying tables.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

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

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