improving speed of query processing

Question

hi,

having major issues with my query processing time :( i think it is because the query is getting recompiled evrytime. but i dont see any way around it. the following is the query/snippet of code:

private void readPerformance(String startTime, String endTime,
        String performanceTable, String interfaceInput) throws SQLException, IOException {
    String interfaceId, iDescp, iStatus = null;
    String dtime, ingress, egress, newLine, append, routerId= null;
    StringTokenizer st = null;
    stmtD = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,  
            java.sql.ResultSet.CONCUR_READ_ONLY);
    stmtD.setFetchSize(Integer.MIN_VALUE); 
    BufferedReader interfaceRead = new BufferedReader(new FileReader(interfaceInput));
    BufferedWriter pWrite = new BufferedWriter(new FileWriter("performanceInput.txt"));

    while((newLine = interfaceRead.readLine())!= null){
        st = new StringTokenizer(newLine,",");
        while(st.hasMoreTokens()){
            append = st.nextToken()+CSV+st.nextToken()+st.nextToken()+CSV+st.nextToken();
            System.out.println(append +" ");
            iStatus = st.nextToken().trim();
            interfaceId = st.nextToken().trim();
            append = append + CSV+iStatus+CSV+interfaceId;
            System.out.println(append +" ");
            pquery = " Select d.dtime,d.ifInOctets, d.ifOutOctets from "+performanceTable+"_1_60" +" AS d Where d.id = " +interfaceId 
            + " AND dtime BETWEEN " +startTime+ " AND "+ endTime;
            rsD = stmtD.executeQuery(pquery); 
            /* interface query*/
            while(rsD.next()){
                            dtime = rsD.getString(1);
                    ingress= rsD.getString(2);
                    egress = rsD.getString(3);
                    pWrite.write(append + CSV + dtime+CSV+ingress+CSV+egress+NL);

            }//end while
        }//end while
    }// end while

    pWrite.close();
    interfaceRead.close();
    rsD.close() ;   
    stmtD.close();

}

my interfaceId value keeps changing. so i have put the query inside the loop resulting in recompilation of query multiple times.

is there any betetr way? can i sue stored procedure in java? if so how? do not have much knowledge of it.

current processing time is almost 60 mins (:(()!!! Text file getting generated is over 300 MB Please help!!!

Thank you.

Answer 1

You can use a PreparedStatement and paramters, which may avoid recompiling the query. Since performanceTable is constant, this can be put into the prepared query. The remaining variables, used in the WHERE condition, are set as parameters.

Outside the loop, create a prepared statement, rather than a regular statement:

   PreparedStatement stmtD = conn.prepareStatement(
         "Select d.dtime,d.ifInOctets, d.ifOutOctets from "+performanceTable+"_1_60 AS d"+ 
         " Where d.id = ? AND dtime BETWEEN ? AND ?");

Then later, in your loop, set the parameters:

   stmtD.setInteger(1, interfaceID);
   stmtD.setInteger(2, startTime);
   stmtD.setInteger(3, endTime);
   ResultSet rsD = stmtD.executeQuery();  // note no SQL passed in here

It may be a good idea to also check the query plan from MySQL with EXPLAIN to see if that is part of the bottleneck also. Also, there is quite a bit of diagnostic string concatenation going on in the function. Once the query is working, removing that may also improve performance.

Finally, note that even if the query is fast, network latency may slow things down. JDBC provides batch execution of multiple queries to help reduce overall latency per statement. See addBatch/executeBatch on Connection.

Answer 2

More information required but I can offer some general questions/suggestions. It may have nothing to do with the compilation of the query plan (that would be unusual)

• Are the id and dtime columns indexed?
• How many times does a query get executed in the 60mins?
• How much time does each query take?

If the time per query is large then the problem is the query execution itself, not the compilation. Check the indexes as described above.

If there are many many many queries then it might be the sheer volume of queries that is causing the problem. Using PreparedStatement (see mdma's answer) may help. Or you can try and batch the interfaceIDs you want by using an "in" statement and running a query for every 100 interfaceIDs rather than one for each.

EDIT: As a matter of good practice you should ALWAYS use PreparedStatement as it will correctly handle datatypes such as dates so you don't have to worry about formatting them into correct SQL syntax. Also prevents SQL injection.

Answer 3

From the looks of things you are kicking off multiple select queries (even 100's based on your file size) Instead of doing that, from your input file create a comma delimited list of all the interfaceId values and then make 1 SQL call using the "IN" keyword. You know the performanceTable, startTime and endTime arent changing so the query would look something like this

SELECT d.dtime,d.ifInOctets, d.ifOutOctets 
FROM MyTable_1_60 as d 
WHERE dtime BETWEEN '08/14/2010' AND '08/15/2010'
AND d.id IN ( 10, 18, 25, 13, 75 ) 

Then you are free to open your file, dump the result set in one swoop.