Improving Oracle FIND LAST performance

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)
 

Stefan

Well-Known Member
Further research (including Progress Knowledge Base item P78607) provides the following as an alternative for the FIND LAST:

FIND LAST MyTable WHERE
MyTable.SeqNr = MyTable.SeqNr /* yes this seems like nonsense */
USE-INDEX MyTableIX NO-LOCK.

Now the Oracle database understands what it is supposed to be doing and performs correctly. Note that the nonsense WHERE AND the USE-INDEX need to be included...

Unfortunately this does not help for Microsoft SQL server. Strangely enough the following is the ONLY reasonably fast alternative for Microsoft SQL server:

FOR LAST MyTable FIELDS(SeqNr) NO-LOCK:
END.

All other alternatives perform badly on Microsoft SQL server.
 
Top