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?
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.