db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Peter Ondruška" <peter.ondru...@gmail.com>
Subject Re: resultSet.next very slow
Date Sun, 09 Nov 2008 20:05:06 GMT
Dear Jim, what Derby version are you running? Have you tried
PreparedStatement instead of Statement? Peter

2008/11/9, James Crow <jcrow@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.
>
>

Mime
View raw message