Thanks for help to everyone.

Hi Set!

These queries take 4-4 sec!

*************First:
Plan
PLAN SORT ((TBL_1 NATURAL))
PLAN (T2A INDEX (FK_TBL2_TBL1ID))
PLAN (T2B INDEX (FK_TBL2_TBL1ID))
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)) PLAN (TBL_2 INDEX (FK_TBL2_TBL1ID))


*************Second:
Plan
PLAN SORT ((TBL_1 NATURAL))
PLAN SORT ((TBL_2 INDEX (FK_TBL2_TBL1ID)))
PLAN (TBL_2 INDEX (FK_TBL2_TBL1ID))

Adapted Plan
PLAN SORT ((TBL_1 NATURAL)) PLAN SORT ((TBL_2 INDEX (FK_TBL2_TBL1ID)))
PLAN (TBL_2 INDEX (FK_TBL2_TBL1ID))


The count number is mostly 1-3. But always lower than 5.

I don't understand how these plans and indexes work. Up to now I
thounght queries work faster if I use indexes.
But now, after your last answer, I tried a simple query with "ORDER BY
TBL_1.ID+0" and it gets more faster. I am surprised.
Can you explain it to me folks, please?

Sandor


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

>
> Hmm, good!
>
> Then try these two:
>
> 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+0 < 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
>
> and
>
> select <skipped many fields>,
> (select first 1 TBL_2.SPC from TBL_2 where TBL_2.TBL1ID=TBL_1.ID
> order by TBL_2.KOD+0) 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
>
> Even though I expect KOD to be unique in TBL_2, I think it may slow

down your

> queries (at least I suspect it to be guilty of slowing down the one

I wrote,

> that's why I said 'good' above).
>
> But what's the output of this query? Does the select count return a

high number?

>
> Report back again,
> Set
>
> --- In firebird-support@yahoogroups.com, "Sandor Szollosi" wrote:
> > 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))