Solved

Performance Limitations with using JDBC addBatch()

Posted on 2002-05-31
2
851 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
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:

771 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

12 Experts available now in Live!

Get 1:1 Help Now