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