4

I'm doing some work that involves inserting a batch of records into a Sql database. The size of the batch will vary but for arguments sake we can say 5000 records every 5 secs. It is likely to be less though. Multiple processes will be writing to this table, nothing is reading from it.

What I have noticed during a quick test is that using a SqlTransaction around this whole batch insert seems to improve performance.

e.g. 

SqlTransaction trans = Connection.BeginTransaction()
myStoredProc.Transaction = trans;
sampleData.ForEach(ExecuteNonQueryAgainstDB);
transaction.Commit();

I'm not interested in having the ability to rollback my changes so I wouldn't have really considered using a transaction except it seems to improve performance. If I remove this Transaction code my inserts go from taking 300ms to around 800ms!

What is the logic for this? Because my understanding is the transaction still writes the data to the DB but locks the records until it is committed. I would have expected this to have an overhead...

What I am looking for is the fastest way to do this insert.

Albireo
  • 10,977
  • 13
  • 62
  • 96
user630190
  • 1,142
  • 2
  • 11
  • 26
  • If you're only writing to it you can also specify `trans.IsolationLevel = IsolationLevel.Chaos` (the lowest level) so your transaction won't lock other concurrent transactions. – Albireo Feb 23 '11 at 12:58

4 Answers4

9

The commit is what costs time. Without your explicit transaction, you have one transaction per query executed. With the explicit transaction, no additional transaction is created for your queries. So, you have one transaction vs. multiple transactions. That's where the performance improvement comes from.

Daniel Hilgarth
  • 171,043
  • 40
  • 335
  • 443
  • 1
    I thought the commit was cheap, actually; rollback is the expensive one. I expect it is indeed overheads in setting up lots of transactions, but at the *start*, not the *end* - IIRC. – Marc Gravell Feb 23 '11 at 13:09
5

If you are looking for a fast wqay to insert/load data have a look at SqlBulkCopy Class

Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
  • The problem with SqlBulkCopy is you need knowledge of the table structure. Agreed it is the fastest way though by quite a margin. – user630190 Feb 23 '11 at 14:45
3

What you're getting is perfectly normal.

If your working with a usual isolation level (let's say commited or snapshot) then when you don't use transactions the database engine has to check for conflicts every time you make an insert. That is, it has to make sure that whenever someone reads from that table (with a SELECT *) for example, it doesn't get dirty reads, that is, mantain the insertion so that while the insertion itself it's taking place noone else is reading.

That will mean, lock, insert row, unlock, lock, insert row, unlock and so on.

When you encapsulate all that in a transaction what you're effectively achieving is reducing that series of "lock" and "unlock" into just one in the commit phase.

Jorge Córdoba
  • 51,063
  • 11
  • 80
  • 130
  • I guess the downside to this approach is the table is locked for the duration of the transaction? If multiple processes are trying to write to this table would these transaction effectively be queued or would there be no need because these are inserts never updates or deletes to exsiting rows? – user630190 Feb 23 '11 at 14:03
  • There would be a need to lock the table because a select * affects needs all the rows, you can't just interrupt in the middle. Anyway that doesn't mean it will take long. I don't know about any specific implementation but one can imagine you can construct an in memory copy of the table and then just swap if there's enought memory, which will be pretty fast. – Jorge Córdoba Feb 23 '11 at 16:03
1

I've just finished writing a blog post on the performance gains you can get by explicitly specifying where transactions start and finish.

With Dapper i have observed transactions cutting batch insert down to 1/2 the original time and batch update times down to 1/3 of the original time

undefined
  • 33,537
  • 22
  • 129
  • 198