>select nameklasse, ...
> from rt_identtab
> where nameklasse = 'BERUF' ...
> and bez_std like 'LEHR%%'
> order by bez_std;

In embedded program, do you use exactly the same command,
or do you have some values as parameters ?
i.e. do you have " like 'LEHR%%' " or " like ? " ?

Because you use Order By clause, you can't see all used indexes in plan.
Try to run both variants without Order By and then compare plans.

Ivan

----- Original Message -----
From: "Klement Guenther - Munich-MR - external" <gklement@...>
To: <firebird-support@yahoogroups.com>
Sent: Wednesday, May 19, 2004 9:52 AM
Subject: AW: [firebird-support] Performance differenz by 20 times between gpre and IBOconsole


>
> Do you have IB_Query.FetchWholeRows set to true ?
I don't know where to set this option in IBOconsole.

> Do your data contain blobs ?
No. Table looks as following:
CREATE DATABASE 'slow.fdb' ;
create table rt_identtab (
klasse varchar(40) not null,
kategorie varchar(40) not null,
sprache varchar(40) not null,
bez varchar(80) not null,
bez_std varchar(80) not null,
bez_phon varchar(80) not null,
objekt varchar(40) not null,
flagdiff varchar(40),
flagfirst varchar(40) not null,
z_key varchar(16),
k_key varchar(16),
nameklasse varchar(80) not null,
nameobjekt varchar(80) not null,
namesprache varchar(80) not null,
namediff varchar(80),
namefirst varchar(80) not null,
record_id integer not null
)
;
> Did you run tests several times to ensure the speed difference
> is not caused by cache ?
The cache seems to be not used at all.

Maybe there are too many indices on the table

create index xrt_ident_bez
on rt_identtab( bez );
create index xrt_ident_bez_phon
on rt_identtab( bez_phon );
create index xrt_ident_bez_std
on rt_identtab( bez_std );
create index xrt_ident_k_key
on rt_identtab( k_key );
create index xrt_ident_namobj
on rt_identtab( nameobjekt );
create index xrt_ident_z_key
on rt_identtab( z_key );
create index xrt_ident_objekt
on rt_identtab( objekt );
create index xrt_ident_sprache
on rt_identtab( sprache );
create index xrt_ident_nameklasse
on rt_identtab (nameklasse);
create index xrt_ident_namesprache
on rt_identtab (namesprache);
create index xrt_ident_flgdiff
on rt_identtab (flagdiff);
create index xrt_ident_flgfirst
on rt_identtab (flagfirst);
create index xrt_ident_record
on rt_identtab (record_id);
create unique index xrt_ident_unique
on rt_identtab( klasse, sprache, bez );

/*** Enter this into IBOconsole:

select nameklasse,
kategorie,
namesprache,
bez,
bez_std,
nameobjekt,
namediff,
namefirst,
z_key,
k_key
from rt_identtab
where nameklasse = 'BERUF'
and kategorie = 'BezeichnungWbEKlasse'
and namesprache = 'deutsch'
and bez_std like 'LEHR%%'
and (namediff is null OR namediff <> 'XXX')
order by bez_std;


/*
Maybe it can be tested with only a few records
at least all 70'000 will show it up.
*/
delete from rt_identtab;
insert into rt_identtab values (
'_470','BezeichnungWbEKlasse','SPR-ITALIA','Pittore','PITTORE','PITTORE','_55203','_12','JN-JA','232
04004','','BERUF','Maler/-innen, Tapezierer/-innen','italienisch','g_maennlich','ja','1206592' );
insert into rt_identtab values (
'_470','BezeichnungWbEKlasse','SPR-FRANCE','Ecrémeur','ECREMEUR','ECREMEUR','_55172','_12','JN-JA','
21105040','','BERUF','Kaeser/-innen und
Molkeristen/Molkeristinnen','franzoesisch','g_maennlich','ja','1196963' );
insert into rt_identtab values (
'_470','BezeichnungWbEKlasse','SPR-DEUTSCH','Fellsalzer','FELLSALZER','VLCALCR','_55188','_12','JN-J
A','22301056','','BERUF','Gerber/-innen,
Lederhersteller/-innen','deutsch','g_maennlich','ja','1203281' );

gpre source slow.e not included. Let me know if you need it.

Regard
Guenther