Jeanne Boyarsky wrote:While it's ok to keep connections open a long time (although you really should use a connection pool instead of doing it yourself), you still should close the PreparedStatement. The overhead isn't in creating the prepared statement object for each query.
Closing the result set isn't strictly necessary if you close the prepared statement, but it is a good idea in case the driver doesn't.
Roger F. Gay wrote:
I did discover today that I don't need to close the result sets at all. (My concern was initialized by someone asking what would happen with SQLException while trying to close them.) After reading about their closure in the Java 7 API doc, it looks like there's no chance of run-away leakage. They close on their own. The prepared statements aren't closed, but they are reused; which closes any old related result sets that may be open.
Jeanne Boyarsky wrote:
Roger F. Gay wrote:
.... They close on their own. ....
No. The resources are only closed if you are using try with resources syntax. Your code uses the pre-Java 7 coding style so you don't get the auto closure.
Note: A ResultSet object is automatically closed by the Statement object that generated it when that Statement object is closed, re-executed, or is used to retrieve the next result from a sequence of multiple results.
Roger F. Gay wrote:RE: the PreparedStatements, it seems like there is some overhead to preparing the statements. In fact from what I've read, it's not worth using prepared statements when the preparation is done on each demand for data unless you fetch a lot of data ... i.e. there's a big enough number of loops on the prepared statements. I don't know how many loops I'll have. It could be fairly high, but often, it will be only once ... for one item.
As I was testing today, I noticed that the first instance of fetching a data set was slower than the rest. I fixed that by setting con.setAutoCommit(true) in initialize(). (I know it's the default, but just want to be sure.) Then the first instance became just as fast as the rest.
Another thing I'm looking into is the possibility of using batch processing. With Statements, it's said you can't do that with SELECT. Perhaps it's just an oversight, but the Java 7 PreparedStatement doc doesn't say that.
Martin Vajsar wrote:The addBatch() method doesn't return a resultset, so unless the Java 7 makes it accessible somehow, I don't see any possibility to obtain the results of that select. My guess (again) is that the JDBC allows you ro eun SELECTs this way if you don't mind you cannot read their results. You might want to do that with SELECT FOR UPDATE queries.
Martin Vajsar wrote:As you've noted, autocommit is the default. I'd guess that you were seeing the speedup because the database has cached some data after the first run; again, this is common oversight when assessing DB performance.
Roger F. Gay wrote:Not sure I understand that. Maybe I do. Like, it would create handles to the particular tables of interest?
Martin Vajsar wrote:It's hard to tell with certainty without more knowledge about your data model. But if rs1 and rs2 are expected to contain one row each, then an inner join could probably be used to join all three tables together and return them as a single resultset. That would of course decrease the number of DB roundtrips significantly. (If you take a look, you'll find that Jeanne has advised you to use joins in her first response.)
If you don't know how a join looks like, I'd suggest reading an SQL tutorial at this point. You'll need that knowledge if you want to develop database applications. I can't recommend any particular SQL tutorial, but I'd say t should be possible to find several using Google and just start there. Feel free to come back and ask questions if you get stuck!