and these:

TBL_2.TBL1ID = TBL_1.ID

are indexed?

Giulio

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

>
> 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