> 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;

but why are you making this query at all?
if you used generators instead (one per store), this query would not even be
necessary?
Alan

>
> 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)