How long process executeQuery() method?

Question

I have new simple SELECT query in Oracle.

If i run on SQL tool it takes 1~3 second.

But on java jdbc using executeQuery() method, it takes 30~40 second

Why its takes so long? what I consider to solve this problem?

please help for newbie

i just upload my java source but cannot query upload it is too long fo upload

    pstmt   =   con.prepareStatement(Query);
    pstmt.setString(1,  model.B_BasicDate);
    pstmt.setString(2,  GL_bfMonth);
    pstmt.setString(3,  GL_bfDay);
    **rs = new DmdResultSet(pstmt.executeQuery());  // it takes 35 seconds**
    int  colCount       =   rs.getColumnCount();
    Vector  innV        =   null;
    while(rs.next()) {
        innV        =   new Vector();
        for (int i = 1 ; i <= colCount; i++) {
            innV.addElement(rs.getString(i)) ;
        }
        outV.addElement(innV);
    }

Can you please post your code and sql query you want to execute. :) — Noixes, Nov 03 '17 at 09:04
@kitonlee edit your answer and post your code there, the comment section is not the right place to do that — Angry Red Panda, Nov 03 '17 at 09:15
The SQL you want to execute would be helpful as well .. Or are all statements slow? — Noixes, Nov 03 '17 at 09:24

score 0 · Answer 1 · answered Nov 03 '17 at 09:47

There are some screws like setFetchSize and autocommit/transactional mode. However first try running the application with more memory (java -Xmx=...), as you are doing bulk work.

As on efficiency:

Declaration as close to their first use; inside a loop does not change its storage.
Vector is a very old class. Use (Array)List. It is faster.
SELECT many fields is inefficient, especially for CLOB and such.

So:

// The normal I use, but there are faster ones
connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
connection.setReadOnly(true);

List<List<String>> outV = new ArrayList<>();
while (rs.next()) {
    List<String> innV = new ArrayList<>(colCount);
    for (int i = 1 ; i <= colCount; i++) {
        innV.add(rs.getString(i)) ;
    }
    outV.add(innV);
}

By the way method, variable and field names should start with a small letter and be camelCase, without underscores. This is a rather strict convention by the java family.

thank you for your help i'll try this thank you so much – kiton lee Nov 03 '17 at 13:12 — kiton lee, Nov 03 '17 at 13:12

How long process executeQuery() method?

1 Answers1