Stefan
Well-Known Member
As documented in the Oracle DataServer manual the FIND FIRST / FIND LAST does not perform. Basically the Oracle DBMS is building up a complete result set and the DataServer then using the first / last record.
We have a table that has a unique index consisting of a sequence number (we could probably use sequences but we do not).
As a work-around I discovered that the following seems to supply a fantastic (2000%) performance increase using an Oracle database:
DEFINE VARIABLE iSeqNr LIKE MyTable.SeqNr NO-UNDO.
SELECT MAXIMUM(SeqNr) INTO iSeqNr FROM MyTable.
Instead of taking 9.9 seconds for 100 iterations of a FIND LAST MyTable NO-LOCK it now takes 0.5 seconds to get the highest sequence number. Note that on a Progress or DB2/400 database the FIND LAST is MUCH more efficient then the SELECT MAXIMUM.
My question is: are there any downsides to this approach? (apart from having database platform specific code, since we support Progress, Oracle, DB2/400 and MS SQL server)
Other options (requiring database changes) are:
1. use sequences
2. add a table that stores highest sequence numbers per table (manually updating with create / write / delete database triggers)
We have a table that has a unique index consisting of a sequence number (we could probably use sequences but we do not).
As a work-around I discovered that the following seems to supply a fantastic (2000%) performance increase using an Oracle database:
DEFINE VARIABLE iSeqNr LIKE MyTable.SeqNr NO-UNDO.
SELECT MAXIMUM(SeqNr) INTO iSeqNr FROM MyTable.
Instead of taking 9.9 seconds for 100 iterations of a FIND LAST MyTable NO-LOCK it now takes 0.5 seconds to get the highest sequence number. Note that on a Progress or DB2/400 database the FIND LAST is MUCH more efficient then the SELECT MAXIMUM.
My question is: are there any downsides to this approach? (apart from having database platform specific code, since we support Progress, Oracle, DB2/400 and MS SQL server)
Other options (requiring database changes) are:
1. use sequences
2. add a table that stores highest sequence numbers per table (manually updating with create / write / delete database triggers)