Without an index on pid, the server will have to sort 247,500 records to find the first 200 ...
John

> -------Original Message-------
> From: diwic2 <diwic2@...>
> Subject: [firebird-support] Re: Optimize first clause in select query
> Sent: 15 Dec '06 01:21
>
> Could add that the FB version is 1.5.2 and the OS is Windows Server
> 2003.
>
> // David
>
> --- In [LINK: mailto:firebird-support%40yahoogroups.com]
> firebird-support@yahoogroups.com, "diwic2" <diwic2@...> wrote:
> >
> > First the problem, the query takes about 30 seconds to execute,
> which
> > is way too long for my application. Under those 30 seconds fbserver
> > eats 100% CPU and takes 100 MB in RAM (the RAM stuff is not a
> problem,
> > but the time is).
> >
> > I have this query:
> >
> > select first 200 msg, pid from my_messages
> > where nodeid = 104000007 and pid >= 3000
> > order by pid
> >
> > Here's the plan:
> >
> > PLAN SORT ((MY_MESSAGES INDEX (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 250000 records, where 99% is for
> > nodeid 104000007. Is there any way I can speed up the query
> execution
> > radically?
> >
> > // David
> >
>
>