We have a stored procedure that UPDATES a table based on some conditions. And in the same stored procedure, INSERTS into the same table on some other conditions. Now this destination table has a bunch of associated indexed views which slows down the updates and deletes. What we are doing now is to disable the indexes on the views prior to the load and rebuilding them after. The rebuild takes close to half hour but if we don't disable this, the indexed views are rebuilding once for the update and once for the insert.
My questions:
The updates and inserts, do they recreate the views per row or for all the rows impacted in the UPDATE/INSERT
Is there a way to batch the insert and the updates so that the indexed view gets triggered only once after all the
INSERTand all theUPDATEThe indexed views accesses one of the columns in the table that is being updated/inserted. Now does this indexed view get recreated even if that particular column itself did not change but some other column in the table was updated.