Hi Emmanuel, With the fixed SQL I didn't see any warnings about open transactions, but the consistency check took 5 minutes to complete for only 3200 rows. I guess that Derby is doing table scans for the inner join....
I'm trying to tune the Derby page size to see if that helps as the columns sizes are so large (2 x 8K plus some more). I'll let the list know if this helps at all. Many, many thanks for the assistance! Nick On Wed, 2008-01-30 at 16:47, Emmanuel Cecchet wrote: > Nick, > > > Many thanks for the input. After a lot of head-scratching it seems that > > Derby 10.3.2.1 (the *latest* version) gets fuddled with the SQL > > statement > > in the Recovery Log consistency check. The join below returns rows that > > *do* have a 'commit' for the transaction!! > > > > // Look for open transactions > > stmt = getDatabaseConnection().prepareStatement( > > "SELECT * FROM " + getLogTableName() + " WHERE " > > + getLogTableSqlColumnName() + " LIKE ? AND " > > + "transaction_id not in (SELECT transaction_id" + " FROM > > " > > + getLogTableName() + " WHERE " + > > getLogTableSqlColumnName() > > + " = ? OR " + getLogTableSqlColumnName() + " = ?) "); > > > > stmt.setString(1, BEGIN + "%"); > > stmt.setString(2, COMMIT); > > stmt.setString(3, ROLLBACK); > > > Looks to me that the String comparison should alway use LIKE, most databases > have different ways to interpret '=' on Strings. > What happens if you replace the query by: > stmt = getDatabaseConnection().prepareStatement( > "SELECT * FROM " + getLogTableName() + " WHERE " > + getLogTableSqlColumnName() + " LIKE ? AND " > + "transaction_id not in (SELECT transaction_id" + " FROM > " > + getLogTableName() + " WHERE " + > getLogTableSqlColumnName() > + " LIKE ? OR " + getLogTableSqlColumnName() + " LIKE ?) "); > > > Thanks for your feedback, > Emmanuel > -- > Emmanuel Cecchet - Research scientist > EPFL - LABOS/DSLAB - IN.N 317 > Phone: +41-21-693-7558 > > _______________________________________________ > Sequoia mailing list > [email protected] > https://forge.continuent.org/mailman/listinfo/sequoia _______________________________________________ Sequoia mailing list [email protected] https://forge.continuent.org/mailman/listinfo/sequoia
