Hi Giulio,

It takes 31 sec too.

Thanks
Sandor

--- In firebird-support@yahoogroups.com, "donjules2k" <giulio@g...> wrote:
>
> 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