Will View Increase the Performance of a query?

  • kk.86manu

    Ten Centuries

    Points: 1268

    Hi All,

    I just wanted to know whether retrieving data from a view will be faster than directly querying from table.

    Say suppose if a view is created by joining a number of fact tables(Records greater than 100 million) with dimensional tables.

    Will selecting the records be faster than querying from the actual tables.

    Request your views on the same.

  • Kingston Dhasian

    SSCoach

    Points: 19794

    No. The performance will remain the same as SQL Server will break the query into individual parts before executing.

    If the view in indexed, you might see performance benefit as the table is actually materialized.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Anjan@Sql

    SSCrazy

    Points: 2256

    If it is Materialised then i thought it will defenetly increase the performance.

  • HowardW

    One Orange Chip

    Points: 29109

    Nope, exactly the same. The first thing the optimiser will do when you execute the query against the view is expand the underlying definition and run that.

    You can however create indexes on views if they meet the stringent requirements, then it can be a lot faster, at the expense of inserts/updates to the base tables also having to update this index. If you're not using Enterprise Edition, you also need the WITH(NOEXPAND) hint to make use of indexed views.

  • Gail Shaw

    SSC Guru

    Points: 1004484

    Ananth@Sql (6/18/2013)


    If it is Materialised then i thought it will defenetly increase the performance.

    I wouldn't say 'definitely'. Depends on what the query does, what the view is and what's making it slow. Plus there are all sorts of potential performance effects on other queries from a materialised view and lots and lots of restrictions to the creation of indexes on a view.

    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
  • kk.86manu

    Ten Centuries

    Points: 1268

    Thank you All for your suggestions.

    If there are no performance difference between a view and querying from a fact table then What are the advantages of creating views in database?

    Thanks in Advance

  • DiverKas

    SSCrazy

    Points: 2049

    kk.86manu (6/18/2013)


    Thank you All for your suggestions.

    If there are no performance difference between a view and querying from a fact table then What are the advantages of creating views in database?

    Thanks in Advance

    I rarely use views, but when I do, its usually for the reason to encapsulate situations that are often used. I.E. The join between Sales Order Header and Sales Order Detail.

    Or, power users doing select queries to make it easier for them.

    But again, its pretty rare.

    Obviously I dont buy into the old DBA theory that EVERYONE must access a DB through a view, circa 1995.

  • free_mascot

    One Orange Chip

    Points: 27168

    It depend on the query(what you are executing?). But definitely if table is having index and view is having huge data and you run a query without index on views performance will be slow. Creating index on view improves query performance. I suggest you do side by side comparision in test environment before making any conclusion.

    Well View will help to reduce the number of joins and provide consolidated result/output.

    HTH

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • DiverKas

    SSCrazy

    Points: 2049

    free_mascot (6/18/2013)


    It depend on the query(what you are executing?). But definitely if table is having index and view is having huge data and you run a query without index on views performance will be slow. Creating index on view improves query performance. I suggest you do side by side comparision in test environment before making any conclusion.

    Well View will help to reduce the number of joins and provide consolidated result/output.

    HTH

    It gives the appearance of reducing joins. In actuality it does not. It does make it easier for those less knowledgeable to access the data.

Viewing 9 posts - 1 through 9 (of 9 total)

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