File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes JDBC Code Optimization Question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of JavaScript Promises Essentials this week in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC Code Optimization Question" Watch "JDBC Code Optimization Question" New topic
Author

JDBC Code Optimization Question

Andres Delrotti
Ranch Hand

Joined: Aug 11, 2005
Posts: 139
Hi all,

I have this piece of code from a method of a JDCB DAO object. The method queries data from different tables. Rather than have a quite complicated single sql statement with a lot of subqueries, we've decided to separate some subqueries as separate private methods in the DAO. Problem is, it really takes a long time before the whole query to be completed (it's about 10 mins). I was just wondering if my code may still need some optimization of some sort.

For you to better understand what I am saying, see the portion of the code below.


public class MyDAO {

private Connection con;

public MyDAO(Connection con){
this.con = con;
}

public ArrayList methodOne(FilterData filter) throws SQLException {
ResultData data = new ResultData();
PreparedStatement ps = null;
ResultSet rs = null;
StringBuffer sb = new StringBuffer();
// construct sql statement in StringBuffer sb
ps = con.prepareStatement(sb.toString());
rs = ps.executeQuery();

while(rs.next()){
data.setValue1(rs.getString("value1"));
data.setValue2(rs.getString("value2"));
// and so on for the other data attributes
method2(data); // to fill up other attributes
method3(data); // of the data object
method4(data);
method5(data);
}

}finally{
if(rs != null){rs.close();}
if(ps != null){ ps.close();}
}
}


public void method2(ResultData data){
PreparedStatement ps = null;
ResultSet rs = null;
StringBuffer sb = new StringBuffer();
// construct sql statement in StringBuffer sb
ps = con.prepareStatement(sb.toString());
rs = ps.executeQuery();

if(rs.next()){
data.setValue3(rs.getString("value3"));
data.setValue4(rs.getString("value4"));
}
}finally{
if(rs != null){rs.close();}
if(ps != null){ ps.close();}
}
}

// same goes for method3 , method4 and method5


}


Is that the "most optimized" version that could possibly be? Or are there better ways to do queries like that?

Another question. Where do you really need to close a Datasource Connection? Is it be closed in the dao itself? the DataSourceProxy object? or should it be closed in the business object itself which calls the dao?
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

First off, its really hard to judge performance without seeing the SQL statement.

In most cases, a single sql statement is better than smaller statements iff you can write the statement in a correct manner and make use of proper indexes.

You mentioned subqueries, which I'm taking to imply nested queries. In general, nested queries are very bad for performance and should be avoided as much as possible since the database system cannot optimize them as well as it could for queries with multiple joins and no nested subqueries.

Finally, some amount of database tuning may be required to make the single query work properly. Thats what DB admins get paid big bucks for and can be a very platform-dependent decision.


My Blog: Down Home Country Coding with Scott Selikoff
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

Oh, and to answer your last question, yes always close the database connection if you are done with it for the transaction. It doesn't matter where you close it (although in the business object is a bad practice) but it should *always* be closed.

There's a lot of long answers about why but the short answer is, your performance and system can be very unstable if you don't.
Andres Delrotti
Ranch Hand

Joined: Aug 11, 2005
Posts: 139
Originally posted by Scott Selikoff:
First off, its really hard to judge performance without seeing the SQL statement.

In most cases, a single sql statement is better than smaller statements iff you can write the statement in a correct manner and make use of proper indexes.

You mentioned subqueries, which I'm taking to imply nested queries. In general, nested queries are very bad for performance and should be avoided as much as possible since the database system cannot optimize them as well as it could for queries with multiple joins and no nested subqueries.

Finally, some amount of database tuning may be required to make the single query work properly. Thats what DB admins get paid big bucks for and can be a very platform-dependent decision.


Ok so you're saying it's better to have separate Java methods for subqueries rather than including subqueries in the main SQL query? and just iterate these methods for subqueries for each record in the result set. So what I did was right?

Disregarding the SQL content of the DAO, from a Java coding perspective...was the DAO pattern I created optimized?
Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

No, I think he said it was better to have one big query than having smaller queries which is going to translate into more calls on the db. I agree, not from personal experience but because I assume that a db can optimize a big query on its own. Especially if its prepared.

I don't close my database connection after my transactions because my connections take about 5-10 seconds to open. But I'm not on the web so its not an issue for me.

Finally, your code is bad as it is because if you throw an exception in rs.close() your statement will remain open until the JVM gets around to collecting its garbage, or the connection is directly closed. You need two try-finally blocks. One for the Statement, one for the ResultSet.

Didnt see any try statement at all which I assume is an error from your attempt to make it clean for web posting.
 
wood burning stoves
 
subject: JDBC Code Optimization Question