Query in Java App taking a very long time
Hey Guys,
I have this query shown below along with code. When I run it in Toad, it comes back in 2 seconds with about 15000 rows - but when I run it in Java app - it takes unusually long upto 9 mins for the same number of records.
conn = getConnection();
String query ="SELECT TO_CHAR(ie.ENTRY_DATE,'MM/DD/YYYY') AS entryDate,ie.ic_entry_id AS entryID,iav.IC_HEADER_ID AS headerID,iav.ATTRIBUTE_VALUE AS value FROM IAV iav,IE ie WHERE ie.IC_ENTRY_ID=iav.IC_ENTRY_ID AND ie.IC_REPORT_ID=?";
pstmt = conn.prepareStatement(query);
pstmt.setInt(1,1002 );
rs = pstmt.executeQuery();
while (rs.next())
{
String eDate=rs.getString(1);
Integer eID=new Integer(rs.getInt(2));
String headerID=rs.getString(3);
String attributeValue=rs.getString(4);
}
This is the oracle driver I'm using.
oracle.jdbc.OracleDriver
I'm sure there is something serioulsy wrong but i'm not able to figure it out. Any help or guidance from you guys will be really appreciated.
Thanks
Gublooo
[1224 byte] By [
gubloooa] at [2007-11-27 10:05:49]
# 1
Here are some suggestions:
1) make sure the field IC_ENTRY_ID is a primary key and indexed in both database tables. This will speed things up. (the index has to be done in the database, its not done on the java side.
2) Make sure your're using a connection pool. This will speed things up.
3) add additional filters to your 'where' clause in your sql. Your end-users cant work with 15000 records at once. Give him the option of adding additional filters on the JSP page. For instance, only returning those records where last Name ends in A through Z. Try to limit the records your end user needs at any time to less than 100 via the filters.
4) make sure you close your connection via a try/catch/finally block properly.
# 2
Thanks for your Response George. I've included my responses in bold.
1) make sure the field IC_ENTRY_ID is a primary key and indexed in both database tables. This will speed things up. (the index has to be done in the database, its not done on the java side.
It has already been done.
2) Make sure your're using a connection pool. This will speed things up.
Yes I'm using connection pool. I also used ibatis framework and even that did not improve things.
3) add additional filters to your 'where' clause in your sql. Your end-users cant work with 15000 records at once. Give him the option of adding additional filters on the JSP page. For instance, only returning those records where last Name ends in A through Z. Try to limit the records your end user needs at any time to less than 100 via the filters.
Well the requirement is to extract all the data and write to an excel file. So I really cant use filters to reduce the number of rows.
4) make sure you close your connection via a try/catch/finally block properly.
Yes it is being done
Thanks
# 3
So, what's different between running your SQL through Toad vs. JDBC? One difference
is that you are using a placeholder for a value when run through JDBC. From Oracle's
perspective that means you are running completely different SQL through Toad than you
are running through JDBC. Try hard coding the value where you currently have a
placeholder (?). I'm guessing that will allow Oracle to choose a different and perhaps
more efficient path (similar to what you get when you run through Toad).
If you find that hard coding the value speeds up your query when run through JDBC, that
is not the fix; only proof of what is going on. If you are working with a DBA, ask for
help in optimizing the PreparedStatement (with the placeholder) since it is the Oracle
optimizer (not Java or JDBC) is taking the wrong path and slowing down the query.
