Hi,

which of the following two statements would be better or is there
some other statement that I should rather use? There will be in
excess of 500,000 records per store. It seems to me that the "FIRST
1" would be the better one to execute but is it really going to make
that much difference? I have seen the "SELECT MAX" statement take as
long as 16 minutes when the server is under load, I think that this
is due to garbage collection. So could I avoid garbage collection by
using the "FIRST 1"?

Thanks
Thomas Ellis


SELECT MAX( SEQUENCENO ) AS MAXSEQNO
FROM FILESEQUENCE
WHERE STOREID = :STOREID;

or

SELECT FIRST 1 SEQUENCENO AS MAXSEQNO
FROM FILESEQUENCE
WHERE STOREID = :STOREID
ORDER BY STOREID DESC, SEQUENCENO DESC;

-- Table Declaration --
CREATE TABLE FILESEQUENCE (
STOREID INTEGER NOT NULL,
SEQUENCENO INTEGER NOT NULL,
FILENAME VARCHAR(255)
);

ALTER TABLE FILESEQUENCE
ADD CONSTRAINT PK_FILESEQUENCE
PRIMARY KEY (STOREID, SEQUENCENO);

CREATE DESCENDING INDEX IDX_FILESEQUENCE
ON FILESEQUENCE (STOREID,SEQUENCENO)