2

What is best way to handle transaction in SQL Server when inserting in 15 to 20 tables in one batch.

Currently I am using single transaction for multiple insert statements in stored procedure.For this I am passing table type object to the stored procedure which does multiple table insert and updates. However, it is taking too much time.

CREATE PROCEDURE InsertRecurringInvoice]    
(  

    @InvoiceItems tabletype  object

    @InvoiceCredits table type 
    @InvoiceShipmentAddress table type
    @InvoiceNotes table type
    @InvoiceAccountingEnteries table type
    @InvoiceAccountingEnteryRows table type
    ......15 table types

)
as  

Begin

  begin transaction 

  INSERT INTO [dbo].[InvoiceAccountingEnteries]
  SELECT * from InvoiceAccountingEnteries 

  INSERT INTO [dbo].[InvoiceAccountingEnteryRows]
  SELECT * from InvoiceAccountingEnteryRows 

  ...15 tabless

END
2
  • 1
    You think wrapping a transaction around this is what's causing it to take a long time? Apr 25, 2014 at 0:03
  • Thanks from replying , i am not sure but when i checked the database for slow queries these insert statement are taking time. Do you think my way is good or bad. Do i need to create shorter transaction by splitting the stored procedure and maintain transaction the from code behind
    – user24461
    Apr 25, 2014 at 2:47

2 Answers 2

2

Transaction size is unlikely to be the primary cause of import slowness. There are legitimate reasons to break up transactions, even within a single table or to import a whole set of tables in a single transaction as you do.

Also, for most requirements, large or small depends on the number of rows, not the number of tables.

Reasons for larger transactions:

  • Easier to keep data consistency.
  • Can be simpler to write and maintain.

Reasons for smaller transactions:

  • Usually a smaller transaction log is required.
  • Less blocking time. Other SQL processes can be blocked, waiting for the insert to finish.
  • Smaller chunks are easier for the replication log reader to handle.

But none of these have much to do with insert speed. Every row is going to have to be written to the data table and to the log, whether it is in a large transaction or small one. (with standard options)

I would first look for other causes of slowness.

  • Are there non-clustered indexes? They get updated with the insert and can slow things down.
  • Is the clustering key such that the data will get added to the end of the table? That's going to be faster than making inserts at random places. You can also create a clustered index on the temp tables to get SQL to import them in a favorable order.

There are more advanced options for performance, but they usually come with tradeoffs, So I'd check the basics first.

1
  • > large or small depends on the number of rows Well, I think the width of the row is important, too. 8 billion rows that are 4 bytes wide is much different from 8 billion rows that are 8k wide. Apr 25, 2014 at 16:34
2

As a matter of logical transactions @AlwaysLoadingData has you covered. But, have you tuned your log-files?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.