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

Posted on 2006-04-14
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
    LVL 86

    Accepted Solution

    Try dropping the index before insertion and recreating it after commit

    Author Comment

    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

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

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Suggested Solutions

    Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
    Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
    Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
    This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now