You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by James Crow <jc...@tx.rr.com> on 2008/11/09 20:45:22 UTC
resultSet.next very slow
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.
Re: resultSet.next very slow
Posted by James Crow <jc...@tx.rr.com>.
Dear Jim, what Derby version are you running? Have you tried
PreparedStatement instead of Statement? Peter
I am running Derby 10.3.3.1. I don't know how to use a PreparedStatement but
I will read up on it and get back with you.
Regards, Jim
--
View this message in context: http://www.nabble.com/resultSet.next-very-slow-tp20409995p20421186.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: resultSet.next very slow
Posted by Peter Ondruška <pe...@gmail.com>.
Dear Jim, what Derby version are you running? Have you tried
PreparedStatement instead of Statement? Peter
2008/11/9, James Crow <jc...@tx.rr.com>:
>
> 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.
>
>