CODEWITHSUNDEEP
javasqldatabasejdbc
user1008893
user1008893

Reputation:

Multiple SQL queries with Java (efficiency)

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.

Upvotes: 2

Views: 609

Answers (2)

Amir Pashazadeh
Amir Pashazadeh

Reputation: 7282

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!).

Upvotes: 1

Jesse Webb
Jesse Webb

Reputation: 45243

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.

Upvotes: 0

Related Questions