What is best way to handle transaction in sql server when inserting in 15 to 20 tables in one batch 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
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.