Replace SIFT tables with materialised views

colin_leversuch-roberts

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.

ara3n

wouldn't work in accounting system

The problem with materialized views is that some data can be potentially out-of-date.

colin_leversuch-roberts

well there's a thing - I see indexed views will replace sift tables in the next version of Navision.

ara3n

Yep but they are changing it.

bbrown

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)

kine

ara3n wrote:

Yep but they are changing it.

What do you mean by that?

colin_leversuch-roberts

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 !!!

ara3n

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.

kine

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)