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