CODEWITHSUNDEEP
javajdbc
Ventsislav Marinov
Ventsislav Marinov

Reputation: 604

PreparedStatement performance tuning

Is there any way to improve performance of prepared statements? It's about many select queries. I do the queries like this way: 

 String query = "SELECT NAME, ADDRESS " 
                + "FROM USERS "
                + "where ID = ? "
                + "group by NAME, ADDRESS";

PreparedStatement pstmt = connection.prepareStatement(query);

for(long id: listIDs){
   pstmt.setLong(1, id);
   ResultSet rs = pstmt.executeQuery();

   ...
}

The database is MySQL.

Upvotes: 0

Views: 815

Answers (3)

m0skit0
m0skit0

Reputation: 25873

It's the server that prepares the queries (that's why you need a connection). To improve performance of prepared statements you have to tune the DB server itself (indexes, etc...).

Another way, is writing queries that only get the results you want.

Another idea is to cache in client side the data you know you'll be using a lot, this way you won't be querying the DB for the same data again and again.

Upvotes: 2

user507484
user507484

Reputation:

You might also want to investigate whether your JDBC driver supports statement caching. I know oracle's JDBC driver does support.

Upvotes: 0

NPE
NPE

Reputation: 500357

Two suggestions:

  1. Make sure the ID field is indexed.
  2. Combine many small queries into one, for example by using WHERE ID IN (...).

For a more detailed discussion of the latter, see Batching Select Statements in JDBC.

Upvotes: 1

Related Questions