Post reply

Indexed View Vs Temp Table

  • May 26, 2009 at 9:43 am

    #384653

    I am working on ROLAP database, on the perfomance point of view which would be better - Indexed View Vs Temp Table.

    Many thanks

    Zulfi

  • May 26, 2009 at 12:16 pm

    #1000092

    I would say use the temp table if it used a lot in the ROLAP build and is not run frequently.

    If this query is run a lot from different procedures whether it is for rolap or an application then the indexed view would work.

    Have you tested out which gives you the largest performance gain?

    Here is a link that may help you in the decision

    http://www.sql-server-performance.com/tips/indexed_views_p1.aspx

  • May 27, 2009 at 2:28 am

    #1000401

    Whether you choose aggregation tables or indexed views depends on a number of factors - not least whether the database serving the MDX-to-SQL translated queries is exclusively used by SSAS or not.

    If the database is being used by other connections which may change data, indexed views can be problematic. For example, when a data-changing operation like INSERT, UPDATE or DELETE modifies a table referenced by an indexed view, the updates to the view form part of the query plan. This part of the plan can be difficult to optimize for larger operations, and always runs internally at the SERIALIZABLE isolation level. This has obvious consequences for concurrency, and the isolation level means that row-level versioning will not help.

    If the database is exclusively for SSAS, indexed views may (or may not) provide a small performance benefit, for some types of query.

    There really is no substitute for modelling and testing I'm afraid.

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

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

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