Sandor,


Sandor Szollosi schrieb:

>
> Hi all,
>
> I have two tables TBl_1 (16300 records) and TBL_2 (24300 records),
> TBL_2 contains some spec strings to each TBL_1 record. I use some
> foreign keys and uniques.
>
> The following query takes 32 second.
>
> select TBL_1.ID,
> TBL_1.SMALL_FLD1,
> TBL_1.STRING_FLD1,
> TBL_1.STRING_FLD2,
> TBL_1.STRING_FLD3,
> TBL_1.SMALL_FLD2,
> TBL_1.SMALL_FLD3,
> TBL_1.INT_FLD1,
> TBL_1.SMALL_FLD4,
> TBL_1.NUMERIC_FLD1,
> TBL_1.NUMERIC_FLD2,
> TBL_1.NUMERIC_FLD3,
> TBL_1.NUMERIC_FLD4,
> TBL_1.SMALL_FLD3,
> (select first 1 TBL_2.SPC from TBL_2 where TBL_2.TBL1ID=TBL_1.ID
> order by TBL_2.KOD) FIRST_SPECDATA,
> (select count(TBL_2.ID) from TBL_2 where TBL_2.TBL1ID=TBL_1.ID)

The problem is the select() statement
Due to the Firebird Multi generations architecture FB has got to "walk
the whole table" when doing a count().

I don't know for how much records you're asking in total, but for every
record that's in TBL_1, FB has got to read 24300 records in TBL_2. So if
you're reading 100 recordas from TBL_1 you've got approx. 2.5 millions
(non-indexed!) reads on TBL_2; and this everything but fast!

MSSQL and Oracle handle count() differently but in FB you should avoid
it wherever possible.

Luc.