Insert with Transaction Significantly Faster than Individual Inserts - SQL Server

Question

Hello,

Could you please explain this? Why insert with Transaction significantly faster than individual inserts in SQL Server?

Answer 1

Yes, when you have a number INSERT statements like this, it's definitely a go-faster button to wrap a transaction around them.

Without a transaction, each INSERT is its own transaction. This means that SQL Server has to wait for the log record for the insert to be written to disk, so that the INSERT can be rolled forward if the server crashes and must restart.

This is not needed when there is a transaction, because if the server crashes the transaction will be rolled back. Therefore SQL Server can move on to the next statement, and the the log record can be written asynchronously in the background. Only a COMMIT TRANSACTION, SQL Server has to wait.

Although, if you are inserting an insane number of rows, let's say many millions, it may be slower with a single transaction. The best may be to commit after each 1000 rows or so.

Answer 2

Why insert with Transaction significantly faster than individual inserts in SQL Server?

May be temporary blocking by other processes?
Sorry, but that' really to less on information, please provide more details.