On Wednesday 19 March 2008 18:25, Martijn Tonies wrote:

> Hello Bud,
>
> > We have an application that executes this statement on a table having 2
> > million records (PK is an integer type and indexed):
> >
> > SELECT MAX(ALL PK) FROM MyTable;
> >
> > It takes quite a long time - maybe 10 minutes or so.
> >
> > I've tried this in isql:
> >
> > SELECT FIRST 1 * FROM MyTable ORDER BY PK DESC;
> >
> > "SELECT FIRST 1" doesn't seem to limit the query, only the result. This
>
> takes
>
> > the same amount of time as the SELECT MAX (ALL PK)
> >
> > So... what's an efficient way to get the maximum PK that doesn't require
> > scanning every record?
>
> Creating a DESC index on it.

I will try that and populate the database again tonight.

> Why do you want to get the max number?

It's a generated id. When the app starts we want the largest one, and we keep
incrementing for new records.

- Bud