SQLServer jdbc batch execute performance

I am using following pattern to execute jdbc batch on the SQLServer 2008 R2. The batch execute is simple MERGE statement on one table with proper indexes as per MERGE join clause.

batchSize = 50;
for(.....){
...
...
...

    //add to batch
    ps.addBatch();
    if (count >= batchSize) {
        result = ps.executeBatch();
        count = 0;
    }
}
if (count > 0) {
    result = ps.executeBatch();
    count = 0;
}

I have very large data pumping into for-loop. What i have observed is initially execution of batch of size 50 takes approx. 150 msec, and then it increases exponentially to 2.5 miunutes when reaches to counts of 400k records!

Is there some sqlserver specific tunning needed?

Answers


I got it to @30 minutes time for Insert data volume of 400k!!! I have to do following:

  • Change MERGE to DELETE/INSERT (If kept MERGE it takes 1.5 hrs)
  • Increase batch size to 1k
  • Commit after every batch execute
  • Call ps.clearBatch() after batch execute

That is it... :)


Need Your Help

Improve compression ratio with Delphi 6 app that uses the Windows AVIFile functions?

delphi winapi video-encoding avi

I have a Delphi 6 app that makes movies from an incoming video and audio stream from a robot. The PC receives the video stream as a series of JPEG frames and the audio as blocks of PCM audio data....

Why is my website throwing POST error in python?

python json api flask python-requests

I'm sending request to League of Legends API for JSON file. In 3 others tries it worked for me, but in last it doesn't. I have no idea why and can't find any mistakes.