MS SQL Bulk update\insert

Question

Some time ago we faced with the problem of bulk insert\update into MS SQL database.

Task was pretty simple - retrieve data from DB, perform data transformation and update parent table and fill out child table. Volume of data is huge, that's why we were interested in most performant way to do this. Do everything in DB (stored procedures, file processing, etc) is not applicable, since we need to get piece of information from third-party application.

Although in internet you can find a lot of articles of how to disable autoCommit and perform batch inserts\updates, anyway it will send requests one-by-one. This is applicable for majority free jdbc drivers, including most popular one - net.sourceforge.jtds.jdbc.Driver (http://sourceforge.net/p/jtds/discussion/129584/thread/8e89906c/). Probably DataDirect supports such functionality, but it's not free. FIY - List of JDBC drivers for SQL Server 2008 (comparison).

Please share with experience in solving similar problems.

Best Regards,
Alex

Answer 1

We solved this problem in other way. Since we need to perform 2 pieces of data modification (insert new child record and update existing parent with some fields) in bulk we rewrote queries to do it like this:

Update:

 UPDATE ParentTable SET DeviceInformation = dual.DeviceInformation FROM ParentTable ptb 
  JOIN ( values (1, 'info1'), (2, 'info2')) as dual (ParentTableID, DeviceInformation) 
  ON  ptb.ParentTableID = dual.ParentTableID

The same idea for INSERT:

    INSERT INTO dbo.ChildTable 
      (ChildId, ChildNumber, Info) 
      values (1, 1, 'some text'), (2, 2, 'some text2')

It gave us significant performance improvement, since most of the time spent on connection establishment.

I hope it will be helpful.