3

I have a scenario where a user action on screen results in new records getting created in about 50 different tables, real-time. The design of the use case is such that the new records that are created as a result of a user action - is required immediately for the user to make changes. So no possibility of offline or delayed creation.

Having said that, the obvious problem is - the insertion statements (along with some additional manipulation statements) are inside a transaction, which makes it a really lengthy transaction. This runs for about 30 seconds and often results in timeout or blocks other queries.

Transaction is required for atomicity. Is there a better way I can split the transaction and still retain the consistency? Or any other ways to improve upon the current situation?

Lalman
  • 946
  • 2
  • 11
  • 27
  • 30 seconds for just a few hundred records in 50 tables? That would be catastrophically slow. Find out the execution plan, update statistics, and add some indexes or rewrite the query to make it a fraction of a second. – Jirka Hanika Mar 22 '12 at 05:39
  • We reviewed the indexes on the primary tables and will give it another look. The tables in which records are inserted are all "primary tables" frequently used in several other pages in the app . The main problem seems to be that the insert queries are waiting on other (mostly select) queries that are running in parallel at that moment. Since the transaction is lengthy it blocks at one table or the other. – Lalman Mar 22 '12 at 05:43
  • If you see no nested loops in your execution plan, then yes, the other traffic may be responsible. One relatively easy course of action is to watch for slower queries in among that other traffic and either optimizing the worst offenders, or rewriting them to use specialized indexed views instead of the primary tables. – Jirka Hanika Mar 22 '12 at 09:17

2 Answers2

5

insert queries are waiting on other (mostly select) queries that are running in parallel at that moment

You should consider using a row versioned based isolation level, aka. SNAPSHOT, because under row-versioned based isolation levels the reads don't block writes and writes don't block reads. I would start by enabling READ_COMMITTED_SNAPSHOT and test with that:

ALTER DATABASE [...] SET READ_COMMITTED_SNAPSHOT ON;

I recommend reading the article linked for an explanation of implications and trade-offs implied by row-versioning.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
1

Based on the comments exchange, I believe that you have to look at both the insert transaction and on the concurrent queries at the same time. You want to accommodate their load without losing transactional integrity. The available optimization techniques include:

  1. Adding access indexes whenever you notice slow constructs (for example, nested loops) over large data sets in execution plans of frequently seen or slowly executing queries.

  2. Adding covering indexes. These indexes contain additional columns in addition to lookup columns and they make it possible for a particular query to avoid a trip to a table at all. This is especially efficient when the table is wide and the covering index narrow, but it may also be used to avoid locking issues between UPDATEs and SELECTs on different columns of the same rows.

  3. Denormalization. For example, switching some of the queries to access indexed views as opposed the physical tables, or secondary tables fed with triggers upon updates to the primary tables. These are costly and double edged techniques and should only be considered for resolving the proven top bottlenecks.

Make only those changes where the speed-up measured is very large as none of these techniques come for free in terms of performance. Never optimize without doing performance measurements at each step.

This following is trivial, but let's mention it for completeness - keep your statistics up to date (ANALYZE, UPDATE STATISTICS,... as per you database engine), both while you analyze the execution plans, and in production use.

Jirka Hanika
  • 13,301
  • 3
  • 46
  • 75
  • 1
    You are right and We had already done away with nested loops which improved the situation. We also have changed a lot of indexes (including addition of Covering indexes) for other OLTP transactions, though not for the particular "insert transaction" that inserts into 50 tables, since it involves all columns for each table (insert into...select * from). I would like to stay away from triggers but indexed views might be something we would like to try in the upcoming performance test cycle. Thanks Jirka, for your thoughts. – Lalman Mar 26 '12 at 04:13