Solved

Performance Limitations with using JDBC addBatch()

Posted on 2002-05-31
2
864 Views
Last Modified: 2007-12-19
I've yet to find an article that explains possible performance hits when using the addBatch() functionality.  I'm iterating over around a million records and I was wondering if there is a difference between me using addBatch() for all the records, or whether I should call executeBatch() at certain intervals.  Basically I'm wondering if executeBatch is going to somehow free up memory resources.
0
Comment
Question by:nixj14
2 Comments
 
LVL 19

Accepted Solution

by:
Jim Cakalic earned 50 total points
ID: 7047407
You're right. There isn't much in the way of batch size recommendations. This is likely because choosing an appropriate batch size depends, I think, on a number of variables:

- size of the statement. The larger the statement and its associated context (statement text itself and bound parameter values) the greater the memory requirements per statement.

- transaction size. Since autoCommit should be disabled before constructing and executing a batch, all statements in the batch are performed in a single transaction. The larger the transaction, the greater the potential overhead in the database engine and the possibility of running out of transaction log space in the middle.

- concurrency. Directly related to transaction size. If you're performing a large number of inserts say on a single table then there will be a large number of row locks held against the table for the life of the transaction. Many database engines automatically upgrade row locks to table locks at some threshold. If this happens, then other writers to the table and possibly readers will be queued while the transaction completes.

- jdbc driver. This always has a large effect on performance.

- database vendor. Specifically with respect to transaction handling.

Ultimately, an optimal batch size needs to take into consideration these variables which are related to your application and its execution evironment. Oracle tends to recommend batch sizes between 5 and 30 statements. In testing that I've done, this seems to be a good recommendation. The batch size is large enough to provide significant performance gains over single-row inserts (even when autoCommit is disabled) but not so large as to cause concern of the negative side effects mentioned above. This range of batch size conforms to Oracle's own non-standard batching implementation recommendations so I'm fairly comfortable with that. The performance increase isn't necessarily linear as the batch size increases which is also an indicator that application-specific testing should be done.

I would suggest that inserting all 1,000,000 rows in a single batch is likely to be a poor decision regardless of your driver/database. I'd start perhaps with batching perhaps 10 inserts and incrementally increase the size until you are comfortable with performance.

Best regards,
Jim Cakalic
0
 
LVL 1

Author Comment

by:nixj14
ID: 7047559
I appreciate your help.  Your numbers were a lot more realistic than I was thinking.  I'll run some tests and see what kind of performance kicks I get.

Thanks
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
table example 4 32
Tagging and Merging on Branch 1 30
restrict decimal places for double datatype 10 22
junit as external jar or library 7 24
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
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…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
This video teaches viewers about errors in exception handling.

830 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