mibuso.com
Home› SQL Performance
Options

Replace SIFT tables with materialised views

colin_leversuch-robertscolin_leversuch-roberts Member Posts: 113
edited 2007-11-07 in SQL Performance
I'm actually on holiday next week so won't be able to see response, but I was just wondering if anyone had tried comparing the performance of a physical SIFT tables vs a materialised view - I figure the maintenence overhead would be much lower.
Just a thought.

Comments

  • Options
    ara3nara3n Member Posts: 9,255
    wouldn't work in accounting system

    The problem with materialized views is that some data can be potentially out-of-date.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Options
    colin_leversuch-robertscolin_leversuch-roberts Member Posts: 113
    well there's a thing - I see indexed views will replace sift tables in the next version of Navision.
  • Options
    ara3nara3n Member Posts: 9,255
    Yep but they are changing it.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Options
    bbrownbbrown Member Posts: 3,268
    Indexed views are not without potential cost.

    Indexed views can be created and used in any SQL edition but only Enterprise will consider them in query plans. With the other editions they must be explicitly called.

    Developer must decide on the trade-off between updating the indexed view each time the underlying data is changed vs. producing the view on the fly (normal view)
    There are no bugs - only undocumented features.
  • Options
    kinekine Member Posts: 12,562
    ara3n wrote:
    Yep but they are changing it.
    What do you mean by that?
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    colin_leversuch-robertscolin_leversuch-roberts Member Posts: 113
    the usual impact of indexed views is that the data is materialised, but as they will replace physical tables that factor doesn't come into play. You're confusing the use of indexed views for tuning - if you call the view in std edition directly then it will be referenced - to explain my comment it has been a long established fact that to create extra clustered indexes on a table you can create an indexed view, or views, in enterprise edition the query against the base table will use the indexed view instead of the table - the downside was you duplicated data to get your performance gain - using indexed views to replace the SIFT tables is slightly different.
    Getting rid of all those triggers will likely give a 10 - 20 % performance boost out of the box. SQL DBA's know that the easiest way to destroy performance is to put triggers on tables - especially large ones which encroach upon the mem to leave area. Now if they'd just get rid of the cursors !!!
  • Options
    ara3nara3n Member Posts: 9,255
    kine wrote:
    ara3n wrote:
    Yep but they are changing it.
    What do you mean by that?
    in sp1 they'll be replacing sift with indexed views.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Options
    kinekine Member Posts: 12,562
    ara3n wrote:
    kine wrote:
    ara3n wrote:
    Yep but they are changing it.
    What do you mean by that?
    in sp1 they'll be replacing sift with indexed views.

    Ah, ok, my misunderstanding - I understood it that they (MS) are changing plan to replace SIFT by indexed views... 8)
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
Sign In or Register to comment.