SQL query takes too long to execute?

[Alex Kravets]

I am trying to select data from a table that has 4,630 records. Now, when I select this data from UNIX shell it takes less than a second, from JDBC it take good 3-4 seconds. Is such difference in execution time normal?

 public static String doSelect2(String conName, String sql){
                Statement stmt  = null;
                ResultSet rs    = null;
                Connection con  = null;
                Vector data     = null;
                String record[] = null;
                int cnt = 0;
 
                try{
                        Context ctx = new InitialContext();
                        DataSource ds = (DataSource)ctx.lookup( "java:/"+conName );
                        con = ds.getConnection();
                        stmt = con.createStatement();
                        rs = stmt.executeQuery(sql);
                        ResultSetMetaData rsmd = rs.getMetaData();
                        int numOfCol = rsmd.getColumnCount();
                        while(rs.next()){
                                cnt++;
                        }
                }
                catch (NamingException ne){
                        closeConnection(con, stmt, rs);
                }
                catch (Exception e){
                        closeConnection(con, stmt, rs);
                }
                finally{
                        closeConnection(con, stmt, rs);
                }
                return String.valueOf(cnt);
        }

[Julian Kennedy]

Hi Alex,

I don't think that's normal, but perhaps not surprising as there's quite a lot going on. Try the following code to determine where the time is going:

Date start = new Date().getTime();
System.out.println("start: "+ new Date().getTime() - start); // or log it
Context ctx = new InitialContext();
System.out.println("InitialContext: "+ new Date().getTime() - start);
DataSource ds = (DataSource)ctx.lookup( "java:/"+conName );
System.out.println("ctx.lookup: "+ new Date().getTime() - start);
con = ds.getConnection();
System.out.println("getConnection: "+ new Date().getTime() - start);
stmt = con.createStatement();
System.out.println("createStatement: "+ new Date().getTime() - start);
rs = stmt.executeQuery(sql);
System.out.println("executeQuery: "+ new Date().getTime() - start);
ResultSetMetaData rsmd = rs.getMetaData();
System.out.println("getMetaData: "+ new Date().getTime() - start);
int numOfCol = rsmd.getColumnCount();
System.out.println("getColumnCount: "+ new Date().getTime() - start);
while(rs.next()){
    cnt++;
}
System.out.println("end: "+ new Date().getTime() - start);

You can also use the Unix time command to accurately time your query in Unix. I'm assuming that this is just test code, otherwise you should rewrite your query to use "SELECT COUNT(*)" Also, are you aware that you close the connection twice for each exception (catch & finally)?

Hope this helps.

Jules

[Alex Kravets]

I tried it, and it seems that results vary. One time it will take 4 sec, another 3 sec and sometimes 1 sec. And it all happens at execute().
Also, I close my connection twise if there is an exeption, I can't have finally() without catch() right?

[Julian Kennedy]

Wrong. You can have either and you should have both. But only close the connection in the finally. You should be logging the exceptions in some way in the catch clauses.

Is there a network between your database and your Java process? That could be the cause of the variance. The speed of transfer will depend on the network bandwidth and load, and also how busy your DB server is. I'm not really sure what you can do about that. It might be worth putting it to the boys in the performance forum, but essentially I don't think it's Java performance.

Jules

[Ernest Friedman-Hill]

Originally posted by Alex Kravets:
I can't have finally() without catch() right?

You can, actually. try/finally without catch is a fairly common idiom for doing something that needs cleaning-up after.

[Jeanne Boyarsky]

I agree with Jules. It is due to the network transfer. It is best to request specific columns from a query and not return any data that isn't needed.

If you post the query, we can give more specific suggestions.