0

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:

  1. The updates and inserts, do they recreate the views per row or for all the rows impacted in the UPDATE/INSERT

  2. Is there a way to batch the insert and the updates so that the indexed view gets triggered only once after all the INSERT and all the UPDATE

  3. The 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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user_me
  • 21
  • 4
  • question, why do the views have indexes on them, and thus are materialized? To be more specific, was this a choice made out of an assumption or are you certain you need them? – S3S Aug 29 '18 at 13:07
  • We needed them. – user_me Aug 29 '18 at 17:16
  • That’s pretty broad. Can you elaborate? – S3S Aug 29 '18 at 17:17
  • We have a page that loads some aggregations which has millions of rows. We are in the process of moving this out to a separate reporting server. In the meantime these need to stay as indexed views to enable the page to be loaded in time. – user_me Aug 29 '18 at 17:55

0 Answers0