Just an idea (i.e. I havent tested this). Is this any better?

Giulio

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,
count(TBL_2.ID)
from TBL_1
left join TBL_2 on TBL_2.TBL1ID = TBL_1.ID
where TBL_1.ID>0
and TBL_1.SMALL_FLD1>0
group by 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
order by TBL_1.STRING_FLD2

--- In firebird-support@yahoogroups.com, "Sandor Szollosi"
<ssani@f...> wrote:

>
> 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)
> SPECDATA_COUNT
> from TBL_1
> where TBL_1.ID>0
> and TBL_1.SMALL_FLD1>0
> order by TBL_1.STRING_FLD2
>
> Can you help me why is this low speed?
> What should I do to increase speed?
>
> (My friend created this database on MSSQL inserted these records and
> got 3 second! He said there was no index.
> But my FB DB contains indexes too.)
>
> TIA
>
> Sandor