Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How many Statements/ResultSets should be used per one Connection

 
ahmed yehia
Ranch Hand
Posts: 424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Howdy,
I have a code that creates one Connection object and does many queries on that particular object, using one Statement and one ResultSet.
My pseudo code looks like this:

My question is how efficient is this code, and is it best practice to use one Statement and one RS throughout the Connection.
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It depends how often you're hitting the database. If you're runnning a web application with many users you will almost certainly be better off with a Connection Pool
 
Bauke Scholtz
Ranch Hand
Posts: 2458
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sounds like a case for the SQL JOIN clause so that you need only one query.

Are you familiar with it and if so, have you considered its applicability in this case?
 
ahmed yehia
Ranch Hand
Posts: 424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's a web App and needs to query DB several times on every page, even for contents that might look static to users. so many hits are being made to the DB.
It might not sound like good design, but that's how the application is designed! and I want to optimize it to better performance and resource management.

For eg. the App is designed to retrieve most of its contents from the DB i.e page body, side links etc. as you see selections are being made from different locations(Tables) I'm not sure JOIN clause can help here.

Ultimately I'd default to Connection pooling and that wont require much change as only getting the connection differs, so the concept of querying DB apply anywhere.

 
Balu Sadhasivam
Ranch Hand
Posts: 874
Android Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's a web App and needs to query DB several times on every page, even for contents that might look static to users. so many hits are being made to the DB.


If you use Statements to execute same query when web app access DB several time in a single request , then opt for Prepared Statement as it caches the query in DB and helps in "executin plan".
 
Paul Clapham
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually you are creating three ResultSets in that code.

You only have one variable to assign them to, though, so only the last of the three gets closed properly. It's true that the API document for Statement says "All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists", so that should mean that the Statement will close the first ResultSet when it creates the second one. However it's possible that JDBC drivers might not implement that rule correctly, so you ought to close each ResultSet after you finish using it.
 
Paul Campbell
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You may want to consider creating a database view that reconciles the relationships between the 3 query statements and then execute a single query for your application.
 
ahmed yehia
Ranch Hand
Posts: 424
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

If you use Statements to execute same query when web app access DB several time in a single request , then opt for Prepared Statement as it caches the query in DB and helps in "executin plan".

Yea I think Prepared Statements would fit much better here with lots of equivalent queries.


Actually you are creating three ResultSets in that code.
....

Absolutely correct, though I'd expect that MySQL driver properly implements Statement's Interface methods, but still something to keep in mind.


You may want to consider creating a database view that reconciles the relationships between the 3 query statements and then execute a single query for your application.

Sounds like good idea!

Thanks All.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic