Hi Charles!

So, your question is actually: 'Help me make my Firebird 2.1 queries run
quicker without changing anything'?

I'm happy to say that there is no default 'slow mode' of Firebird, and
that it always executes as quickly as it thinks it can. Unfortunately,
this also means that there's no magic way for your query to become
quicker without changes.

As for changing your query, Firebird 2.1 has a new feature you could
try, that may look more similar to your subselect than a JOIN would do:

with Master10 as
(select first 10 recno from master m
where m.recno > 1 and m.recno < 500 order by recno)
select d.*
from detail d
join Master10 m on d.master = m.recno

Tell us whether this is quicker or not, I'm curious because I've never
tried.

HTH,
Set

Charles McAllister wrote:

> i'm using FB version 2.1.1.17910; classic version
>
> i have two simple tables defined, "master" and "detail"
> each has a primary key on column "recno", and table "detail" has an
> index on a column named "master"
>
> i've loaded the "master" table with about 5000 rows, and the "detail"
> table with 100 rows per each master -- so a total of 500k rows
>
> this query executes within milliseconds:
>
> select * from detail where detail.master in
> (select recno from master where master.recno > 1 and master.recno <
> 500 order by recno)
>
> whereas this query executes in over 10 seconds
>
> select * from detail where detail.master in
> (select first 10 recno from master where master.recno > 1 and
> master.recno < 500 order by recno)
>
> i need the second query to execute much faster.
> i know i can accomplish something similar using join, but i really
> need this to work for sub-select as i have a lot of source code
> already written for sub-selects.
>
> help is greatly appreciated!
>
> here's the ddl to create the tables:
>
> CREATE TABLE MASTER (
> RECNO INTEGER NOT NULL);
>
> ALTER TABLE MASTER ADD PRIMARY KEY (RECNO);
>
> CREATE TABLE DETAIL (
> RECNO INTEGER NOT NULL,
> MASTER INTEGER);
>
> ALTER TABLE DETAIL ADD PRIMARY KEY (RECNO);
>
> CREATE INDEX IDX_DETAIL ON DETAIL(MASTER);