Search code examples
sqlsql-servertransactionstransactionalsqltransaction

SQL Server - Any better alternative to improve performance of a lengthy transaction with lot of inserts?

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?

Solution

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