Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

transaction size and commit

 
Rolf Johansson
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi:
I need to perform a commit after N records have been inserted. After each insert, I increase a counter, then call commit() if N records are inserted. Is this the most efficient way, or does JDBC have an "internal" way of knowing that N records have been inserted, then "automatically" committing them?
Right now, I have this:



Is there a need for me to keep track of the number of inserts I have done, or can JDBC do that for me and perform the commit for me?
Thanks.
- Rolf.
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rolf,

is really your requirement to commit after every n records.
What about transactions.

Suppose you have hundred record and you commit after
every 10 records and just after inserting 55 records
there is an error and there would be 50 records
commited.

how will you keep track of such records.

won't you rollback all inserted statements ???

thanks
Shailesh
[ January 24, 2005: Message edited by: Shailesh Chandra ]
 
Rolf Johansson
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In this case I don't need to rollback. The source data can be re-processed any number of times without damage.

So, my question is, does JDBC have an internal method of automatically committing N records, or do I need to keep track of the number of records inserted and explicitly perform a commit?
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I dont think there is such facility in JDBC whick commits at every N records.

But in your case I will recommend to use execute batch after you add N records in your statements/prepared statement.

thanks
Shailesh
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
JDBC now has an executeBatch() method that will increase performance by sending the whole batch to the database at once. I haven't used it (only indirectly through Hibernate), but I bet it's fairly easy to use. It's fairly recent, so depending on your JDK you may not have access to it.

Otherwise, there is no internal mechanism to do what you're doing. Besides, what you're doing (counting insert statements and issuing commit() when you hit the limit) is not inefficient in itself, so I wouldn't worry about it.

What I would definitely do is use PreparedStatements. They will allow the DB to parse the SQL syntax once and then reissue the same statement, substituting new values each time. You prepare the statement with ? placeholders for the parameters outside the loopand then set the parameters and execute it for each iteration.
[ January 25, 2005: Message edited by: David Harkness ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic