db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Crow <jc...@tx.rr.com>
Subject resultSet.next very slow
Date Sun, 09 Nov 2008 19:45:22 GMT

I have used Derby for about four months but I am fairly new. I have searched
both the archive and the docmentation with no luck.

The problem is that the resultSet.next() some times takes a very long time
while running the same code against the same database. Normally the
resultSet.next() takes about 2 1/2 seconds for my code and DB but some times
it can take as long as several minutes. I repeat, this is on the same exact
code and the same exact database. This can also happen in a loop running
through the rows of the same DB.

I have tried indexing all of the fields in the query with no help. The
database is not very large ( less then 300 MB) and I have increased the heap
size large enough to contain the entire DB if necessary with no help. I have
also looked at the hard disk statistics while the long time is going on and
no paginging is occuring. In addition the CPU is maxed out.

 Here is some sample code that produces this effect. I can't see anything
wrong with the code.

                try {
                    resultSet = null;
                    resultSet = statement.executeQuery(SQLCode);
                    long start = System.currentTimeMillis();
                    resultSet.next();
                    long end = System.currentTimeMillis();
                    deltaTime = end - start;
                    returnNumber = resultSet.getInt(1);
                    gain = resultSet.getDouble(2);
                } catch (SQLException sqle) {
                    printSQLException(sqle);
                }

Here is an example of the query, long but not too complex:

String SQLCode = "Select COUNT(GAIN004) as returnNumber, AVG(GAIN004) as
averageGain from selectData where not GAIN004 is null and 
CALEARNTOPRICEYIELD > 0.0784 and CALEARNTOPRICEYIELD <= 0.1507 and  HGAIN04
> 0.5302 and HGAIN04 <= 0.6679 and  CLOSEPRICE > 13.7 and CLOSEPRICE <=
23.19 and  VOLUMERATIO > 0.0053 and VOLUMERATIO <= 0.0103 and 
ESTEARNINCREASEFROMLASTQT > 0.0023 and ESTEARNINCREASEFROMLASTQT <= 0.0138
and  MARKETCAP > 5167.0 and MARKETCAP <= 32397.0 and 
DIVINCREASELASTQTTOYEARAGO > -0.0102 and DIVINCREASELASTQTTOYEARAGO <= 0.0";

Any ideas or help would be appreciated.

Regards, Jim
-- 
View this message in context: http://www.nabble.com/resultSet.next-very-slow-tp20409995p20409995.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.


Mime
View raw message