On 12/20/06, diwic2 <diwic2@...> wrote:

> I've got help to avoid the sort with the previous query, however now
> this query suddenly (well, the table size has doubled, so it isn't
> that suddenly) takes over a minute to execute.
>
> Here's the query:
>
> select stationnodeid, max(packetid)+1 as q
> from roireal_messages group by stationnodeid
>
> Here's the plan:
>
> PLAN (MY_MESSAGES ORDER RDB$PRIMARY3)
>
> Here's the table metadata:
>
> CREATE TABLE "MY_MESSAGES"
> (
> "NODEID" INTEGER NOT NULL,
> "PID" INTEGER NOT NULL,
> "MSG" VARCHAR(2000) NOT NULL,
> "VALIDTO" TIMESTAMP,
> PRIMARY KEY ("NODEID", "PID")
> );
>
> The table contains approximately 1 000 000 records, where 99% is for
> only one nodeid. Is there any way I can speed up the query execution
> radically?
>
> I'm using Firebird 1.5.2 and Windows Server 2003.
>
> // David

Have you tried creating a descending index on the field you calculate
the max() for?

Also, if you're doing this to get an auditable series of numbers then
you should be aware that this approach isn't safe in a concurrent
multi-user system.
Have a look here:
http://www.ibobjects.com/TechInfo.html#ti_AuditableSeries
and search the archives of this list.

HTH,
Markus