how can we improve query performance in jdbc?

Answer 1

That's a pretty broad question.

We use JDBC and have improved performance of several queries. Steps taken include:

* Writing queries to use more bound variables, so that the same query will remain cached and optimized by the database. If very similar queries keep "looking different," it keeps the database from optimizing the query.

* Analyzing slow-performing queries individually and rewriting them or adding indexes. We put in hooks to dump out the SQL of every query that takes longer than a specified threshold, and review those regularly.

* Input from our DBA, who identified queries that, though they did not take long, consumed a lot of server resources such as CPU.

* Rewriting groups of separate queries to run as single database requests so that there were not as many round-trips to the server.

Answer 2

The main thing to consider is where is your time being lost. Java once loaded runs very quickly. Databases operate very quickly. JDBC is something messaging (bridging) between the two and my money is on that this is where you are losing time. If you are running over a network this is also going to cost you time.

Unless you are using a very very large database or making thousands of queries then don't worry to much about trying to optimize your queries.

However as McFate says reducing the number requests to your database server is going to make a big difference overall. If some data is in constant use and it is not being modified by another user then you can store it in your Java while you are using it.

Java is great for networks and cross platform, though I hate to say it but it may not be the best thing for databases.