HELP AGAIN! : STILL NEED to greatly speed up writes without a "batch" option !?

Posted on 2006-04-14
Medium Priority
Last Modified: 2010-03-31
Dear Experts,

I already awarded points on this question to someone who was kind enough to explain how to string together a bunch of inserts into a single statement.  However, it turns out to be only 10% faster than what I'm currently doing.  I need something that will be at least %400 faster!  Here is the original post:


I need a quick fix to speed up an application.  I’m doing a read-a-record, write-a-record logic, with the writes going into an existing MS SQL table.  Previously, I had used a PreparedStatement, in conjunction with doing my own .addBatch() and .commit()s every 1000 records.

However, due to larger db changes (which I can’t change), the addBatch() no longer has an effect.  (Technically, the db recovery option was changed to “simple”, so MS SQL ignores autocommit altogether and writes the record immediately when I do .addBatch()).

I need to at least quadruple the current speed.  The table I’m writing to is (uniquely) keyed, but I can be guaranteed that new records will be unique.

The only thing I can think of right now would be to actually write the record temporarily to a text file, do a bulk insert to a staging table, and then do an outer join.  (Did I think of that myself? NO!  I learned it at Experts Exchange!)  

But it seems nuts to have to write the records to a text file in order to take advantage of bulk insert.  I have thought about writing the records directly to an un-keyed staging table, but with “simple” recovery mode, those writes would themselves happen one at a time, which I think would be very slow.  It seems much faster to actually write to a text file.

Is there another way within Java that I could say “Insert these 1000 records all at once”?

Am I missing something here?  Under the circumstances,  what should I do?  HELP!

Question by:BrianMc1958
  • 2
LVL 86

Accepted Solution

CEHJ earned 2000 total points
ID: 16455743
Try dropping the index before insertion and recreating it after commit

Author Comment

ID: 16455984
For anyone reading this, I think I found my answer:

sb.append("INSERT INTO myTable SELECT 'Joe', 123\n ");
for (int i = 0; i < 1000; i++)
  sb.append("UNION ALL SELECT 'Bob', 456 \n ");

In my first test here, it's 1000% faster.  Oddly, I get this message if I increase the number of iterations much beyond 1000:

  The query processor ran out of stack space during query optimization.

Of course, you're making one very long query here...!

For points, I have also heard many times that dropping indexes and recreating them later helps a lot.  Plus the fact that CEHJ has helped me out so many zillions of times in the past...


Author Comment

ID: 16455996
Oops:  In the above code, remove the two "\n".  It will work, but they are not needed.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
In this post we will learn different types of Android Layout and some basics of an Android App.
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
This video teaches viewers about errors in exception handling.
Suggested Courses
Course of the Month14 days, 23 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question