Thank you Set,


It takes 44 sec.

Plan
PLAN SORT ((TBL_1 NATURAL))
PLAN (T2A INDEX (FK_TBL2_TBL1ID))
PLAN (T2B INDEX (FK_TBL2_TBL1ID,UN_TBL2_ID))
PLAN (TBL_2 INDEX (FK_TBL2_TBL1ID))

Adapted Plan
PLAN SORT ((TBL_1 NATURAL)) PLAN (T2A INDEX (FK_TBL2_TBL1ID)) PLAN
(T2B INDEX (FK_TBL2_TBL1ID,UN_TBL2_ID)) PLAN (TBL_2 INDEX
(FK_TBL2_TBL1ID))


Thanks
Sandor


--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:

>
> Hmm, strange that it uses different indexes for the two references

to TBL_2.

> Anyway, try to change your query to
>
> select <skipped many fields>,
> (select t2a.SPC from TBL_2 t2a where t2a.TBL1ID=TBL_1.ID
> and not exists(select * from TBL_2 t2b where t2b.TBL1ID=t2a.TBL1ID
> and t2b.KOD < t2a.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
> or 2=0
> order by TBL_1.STRING_FLD2
>
> The most important bit is the addition of the NOT EXISTS rather than

the SELECT

> FIRST... ORDER BY. I hope this will improve speed, but try and tell

us whether

> it does so or not (reporting the new plan). Note that I assume KOD

to be unique

> for each TBL1ID (if not you have to add a bit to the where clause).

The 'or 2=0'

> part is simply because it is quicker to do a natural than using an

index if all

> records are to be retrieved.
>
> HTH,
> Set
>
> --- In firebird-support@yahoogroups.com, "Sandor Szollosi" wrote:
> >
> > Thanks for quick answer,
> >
> >
> > Plan
> > PLAN SORT ((TBL_1 INDEX (UN_TBL1_ID)))
> > PLAN (TBL_2 ORDER UN_TBL2_ID)
> > PLAN (TBL_2 INDEX (FK_TBL2_TBL1ID))
> >
> > Adapted Plan
> > PLAN SORT ((TBL_1 INDEX (UN_TBL1_ID))) PLAN (TBL_2 ORDER UN_TBL2_ID)
> > PLAN (TBL_2 INDEX (FK_TBL2_TBL1ID))
> >
> >
> >
> > 16300 records have TBL_1.ID>0 and
> > 16300 records have TBL_1.SMALL_FLD1>0
> >
> > Query total returns 16300 records.
> >
> > If I not use (select count(TBL_2.ID) from.... get results 28 sec.
> > If I not use (select first 1 TBL_2.SPC from.... get results 3 sec
> > (select count was writed back this time).
> >
> > Thanks,
> > Sandor
> >
> >
> > --- In firebird-support@yahoogroups.com, Svein Erling Tysvær
> > <svein.erling.tysvaer@k...> wrote:
> > >
> > > Hi Sandor!
> > >
> > > The first thing to do is to examine the plan that is generated. Post
> > it to this
> > > list. Remember that COUNT may be an expensive operation (though
> > you're operating
> > > on small sets, so it shouldn't matter too much). Post part of the
> > results of the
> > > query here and tell us how many records have TBL_1.ID>0 and how many
> > records
> > > have TBL_1.SMALL_FLD1>0 (if there are any indexes on these fields).
> > How many
> > > records in total does this query return?
> > >
> > > Indexes are generally good when used correctly, but they can

slow down

> > > everything considerably if over-used.
> > >
> > > Set
> > >
> > > --- In firebird-support@yahoogroups.com, "Sandor Szollosi" 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