Sql-server – SQL Indexed View for Datamart Reporting with Transactional Replication

The simplest approach I can think of is to have one fact table for order items, and have two price columns in that fact table: original price, and discounted price. Original price would be the price as listed on the line item. Discounted price would be calculated by totaling the price of all line items on the order, calculating the percent difference between that and the total stated on the order header, then scaling down the prices of the line items using that percentage.

OK I found the solution, and I am writing it here not just to help others that encounter a similar problem, but it is also something worth for everyone that works with indexed views.

First some points on indexed views.

Indexed Views

1. An index view although it is also an "index" it is also a "view", thus it might benefit when the system is updating the indexed view from non-clustered indexes on the base table (and even covering indexes and statistics).

   (To prove that point I have added indexes on base tables in a database that wasn't used besides getting replication data, and still the dm_db_index_usage_stats system view showed user seeks, and the only one use it was the indexed view.)

   And it appears that the best indexes to add for the benefit of an index view would be for seeking a single value or a small range of values, since this is what the indexed view is basically doing when being used locally or on a subscriber to transactional replication.

2. Although conditional joins are not prohibited by the indexed view restrictions, still it is a good idea to avoid them (just as in regular queries).

   Consider the following join:

   FROM a
   JOIN b ON b.id = a.id
   JOIN c ON c.id = a.id
   JOIN final ON final.id = b.FinalID OR (b.finalID IS NULL AND final.id = c.FinalID)
   

   This can cause performance bottlenecks, instead it is better split it up into two extra indexed views and then have a view that combines both using "UNION ALL".

3. While and indexed view is also a "view" it is also an "index" which in other words that inserts/updates/deletes also has to update the indexed view, thus if the view has performance problems the update will take longer, and in the case of replication if each transaction takes to long then they might pile up after each other, and eventually bring replication to an halt

Please also take in mind when working with replication, that reinitializing the subscriber causes all of the indexes and statistics that have been added at the client to be dropped (if pre_creation_cmd has been set to drop which is the default), and only indexes that are part of the replication itself will be recreated, and one has to recreate all of the subscriber specific indexes manually.

So in my situation I have first added new indexed views that turned out to need better non-clustered indexes, and also some that used conditional joins, but after I reinitialized I didn't bother recreating first the base tables non-clustered indexes and first tried to create the indexed views which caused bottlenecks for the replication process.