Dear Jim, what Derby version are you running? Have you tried
PreparedStatement instead of Statement? Peter
2008/11/9, James Crow <[EMAIL PROTECTED]>:
>
> 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.
>
>
