2

I know that indexes hurt insert/update performance, but I'm trying to troubleshoot and determine the right balance between query performance and insert/update performance.

We've created a number of views (about 20) for some really really complicated queries. They're really slow for seeking by keys (can take 20 seconds to scan for 5 to 10 keys).

Indexing these views (with both clustered and non-clustered indexes on the various key columns) speeds up their performance in the area of 80x to 100x. It also hurts insert/update performance to the point that a script which inserts about 100 rows into various related tables takes about 45 seconds to run instead of being instantaneous.

I'd prefer not to go the OLAP route for these views (it would add a whole new layer of complexity....and the views are currently updatable, which would pose a reverse synchronization problem)...so I'm trying to figure out how to balance query performance with insert/update performance.

Can someone please suggest how to diagnose the specific problem indexes - and potential ways of reducing their impact on inserts/updates?

I've already tried using covering indexes, indexes with INCLUDEs and composite clustered indexes as alternatives to see if it makes a difference (it doesn't really).

Thanks.

Jeff
  • 35,755
  • 15
  • 108
  • 220
  • This is quite a general question. Have you reviewed the checklist for slow queries? http://msdn.microsoft.com/en-us/library/ms177500.aspx In particular, have you looked at the execution plans for your queries and have you tried using the Database Engine Tuning Advisor with a representative workload to see what it suggests? If you want help with specific queries, then you should post details of the views/tables, queries and especially the execution plan. – Pondlife Mar 15 '12 at 12:06
  • Yes, I've reviewed the checklist - but bear in mind the queries run quite fast, it's the inserts/updates that run slowly. I've looked at the database tuning advisor and it recommends I drop all my indexes on my views (I'm assuming because my test workload doesn't include the noexpand option, so it doesn't use the view indexes). The advisor doesn't really offer any other useful information. There are about 20 large queries in question and they involve intricate subject matter knowledge...so I'm not sure how useful it would be to post them. I'm looking for general advise. – Jeff Mar 15 '12 at 15:06
  • As the checklist notes, those points are valid for both queries and inserts/updates. If the general advice from the checklist isn't helpful and if the details are too specific/numerous to share then I'm not sure what else anyone can suggest. If you really can't improve the performance with your current setup, you may have to look into something more radical, such as partitioning tables or remodelling the database, or hiring a specialized consultant to help investigate further. – Pondlife Mar 16 '12 at 14:57
  • Are you doing your inserts through the VIEWS or are you hitting the tables directly for the inserts? Are there any triggers on the underlying tables? – Matt Feb 18 '14 at 18:18

1 Answers1

0

For this scenario please use single column else filtered indexes and avoid composite ones which have more than two columns.

Ahmed Mustafa
  • 19
  • 6