Thomas,

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

The thing is, that MAX is a different function than FIRST.

In this particular case, you know yourself that it makes sense
to do the ORDER BY and the FIRST, so yes, it's safe.

I doubt it has anything to do with garbage collection though.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com