Performance increase for batch transactions?

[brownie124]

Hi,

If I am inserting millions of records (in small batches of 5000 at a time), would I get any measurable performance gain by doing these in a transaction and committing the 5000 all at once as opposed to inserting one at a time. The records themselves are relatively small, 3 columns, 1 primary key and one additional indexed column.

Thanks,
- Michael

 

[AlexCuse]

Yes, there is a substantial performance increase to be had. Because of implicit transactions, there each individual insert is considered its' own transaction, so locks need to be obtained on the table for each one. By placing these inside a transaction, the locks only need to be opened and closed once, resulting in a substantial performance gain.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[brownie124]

Thank you very much. That is what I thought. Do you know where I can possibly see an example of how this might be done in C++ via ODBC or ADO?

Thanks,
- Michael

 

[AlexCuse]

I don't know a thing about C++, although I think the best way you could do it is to have a stored procedure executed from your front end. If you browse FAQ's in this forum or C++ forum I am sure you will find something to point you in the right direction.

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SQLSister]

http://www.tek-tips.com/faqs.cfm?fid=3141

Questions about posting. See faq183-874

 

[brownie124]

Thank you for your help.

- Michael