0

I have a view that created with 'WITH SCHEMABINDING' and it has one unique clustered index, three different non-clustered indexes. The problem is that before this view created, update or delete operation (affecting more than 1000 rows) takes less than 2 seconds however after I created the view, delete or update operation takes about 930 seconds.

I searched about that and when I altered view without using 'WITH SCHEMABINDING' parameter and it fixed problem, delete or update operation cost about 1 or 2 seconds. Hovewer I need to use 'WITH SCHEMABINDING' in order to create indexes.

I don't want to alter the view before every delete or update operations. Is there a way that I can disable this view or what is the practical solution for this issue?

yenicead
  • 15
  • 5
  • OT - belongs on [dba](https://dba.stackexchange.com/). An effective solution will likely revolve around the definition of the view, the index you defined on that view, and the indexing of the table it uses. And perhaps any triggers that execute as a result of your operations. – SMor Jul 23 '19 at 15:47
  • Please show us the schema, the view definition and indexes defined. 2 seconds vs 930 seconds seems very strange. – Alex Jul 24 '19 at 05:39
  • View uses 5 different tables, 2 of them have about 15M records, others have less than 5000 records. There is an stored procedure which deletes and updates some data from these 2 referenced tables. This sp executed less than 2 seconds without indexes and about 15 min. with indexes. Unique clustered index in the view uses Id column which view getting it from the table that has lot of data. – yenicead Jul 24 '19 at 10:20

1 Answers1

0

The problem seems to be not with the schemabinding option.

When you have a view with index and update underlying table then index on the view has to be updated as well - same as if you had this index on the table.

So you either speed up the reads from view and have index there and lose write performance or vice versa. There is no other simple option :(

Consider removing some of the four indexes over the view and try to find some performance balance that would meet the requirements.

ivanochkah
  • 83
  • 7
  • Removing indexes solved the problem, but this time querying the view and getting some records takes much more time as you said so I added indexes again. Next time, I will disable all the indexes and make update/delete operation after that I will enable all the indexes. I hope this will work. – yenicead Jul 24 '19 at 10:09
  • enabling/disabling indexes also will take time in the strategy you described. please consider that. – ivanochkah Jul 24 '19 at 12:28