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