Bulk inserts into InterBase

Posted by on in Programming

I was recently asked by a customer for tips on making the fastest bulk insert. Here are my thoughts on the subject

  • Turn off inserts and check constraints. This one is by far the most important.
  • Perform a commit approximately every 10,000 rows. 10,000 is no absolute magic number, but is a good water mark.
  • Don't prepare every insert statement. It needs to be prepared just once.
  • Use the Batch API as discussed here: http://docwiki.embarcadero.com/InterBase/XE7/en/Using_Batch_Updates_to_Submit_Multiple_Statements
  • Break the work into multiple pieces and have multiple clients do the inserts concurrently. I would suggest having the number of clients equal to the number of logical cores you have would be optimal. This item is definitely theoretical and worth testing to make sure it makes sense for your situation.

Gold User, Rank: 35, Points: 77
Support guy since 3/18/85. It was dBASE II/III back then.


Check out more tips and tricks in this development video: