2

I have a Java program that connects to a SQL Server 2008 database and performs modifications. If I have a million records I would like to modify, is it bad practice to do as follows:

for(all of the records I need to modify) {
    PreparedStatement pst = conn.prepareStatement(someQuery);
    // set record specific parameters for pst
    // execute pst
}

Or should I build a single query and execute it? Will it make a difference? Does it depend on whether it is an UPDATE, INSERT, or DELETE? My SQL knowledge is quite basic.

Improve this question

2 Answers 2

Reset to default
1

If the query is the same for all of your iterations, create the PreparedStatement before the iteration, and in the end of iteration call PreparedStatemetn.executeBatch() as Jesse Webb suggested.

I recommend to commit your transaction after a couple of iterations (may be after each 1000 iterations), because when updating or deleting a record without committing the transaction, there will be locks on mutating records which can cause problem for other users of the database (if you are not the only client of those database objects!).

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

+1 for suggesting batch commits on transactions. For a batch processing job I worked on, we decided to use batches of 100 to allow the least amount of locks for the biggest performance gain. You will have to experiment to find out what works best for you.
0

For large amounts of UPDATEs, it is best to use Statement.executeBatch().

Try Google'ing for "java executebatch example" for examples.

You will most likely want to also make sure you use Transactions properly, a lot of the overhead of queries come from implicit Transaction (one for every query) where using a single Transaction for many statements can be much more efficient.

Improve this answer

3 Comments

Okay thanks, just what I needed. So if I'm doing multiple SELECTs, executeBatch won't help me (?) so how would I use Transactions properly in that case?
@xcross you obviously need some knowledge in database and SQL before coding. Have you a DB Master around you that can help you dig in SQL World ? Or try to find a tutorial on google.
No, executeBatch() will only work with INSERT, UPDATE, & DELETE statements, not SELECTs. As for transaction management, that will be up to you to figure out based on the behavior you desire from your code. I suggest trying to learn about Transactions in general and then trying to apply that knowledge to your specific problem. If yu run in to problems, you can of coarse search SO for similar Q&As or ask another question about the problem you encounter. Glad I could help!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.