It is probably not relevant to your particular query, but you do have an unusual way to write your joins and I believe (though I'm only 80% certain) your 'nesting' style reduces the options for the optimizer (I think it can only choose between TBESTELLUNGEN and TBESTPOS as the first table, though since you probably want TBESTPOS anyway, it is not important unless you decide to add another table). Sometimes (as part of optimizing a query), that can be desirable, but only after finding the optimizer to come up with a bad plan. Hence, I'd recommend that you change your query to (from the FROM part):
FROM TKUNDEN INNER JOIN TBESTELLUNGEN ON TKUNDEN.KUNDENNR = TBESTELLUNGEN.LIEFERANTNR INNER JOIN TBESTPOS ON TBESTPOS.NR = TBESTELLUNGEN.NR AND TBESTPOS.JAHR = TBESTELLUNGEN.JAHR INNER JOIN TTEILE ON TTEILE.TEILENR = TBESTPOS.TEILENR WHERE TBESTPOS.ABGESCHLOSSEN=-1 AND TBESTPOS.TATSLT BETWEEN [forms]![fbestelleingang].[sa] AND [forms]![fbestelleingang].[sb] AND UCase([tteile].[teilenr]) Like nz(UCase([Formulare]![fbestelleingang]![st]) & "*","*") AND TKUNDEN.KUNDENNR Like nz([Formulare]![fbestelleingang]![sl],"*") ORDER BY TBESTPOS.LIEFERTERMIN DESC , TBESTPOS.LIEFERZEIT DESC; The reason I've removed lots of parenthesis, is simply because their main purpose in a query containing only ANDs, is to confuse the reader of the query. I don't understand nz([Formulare]![fbestelleingang]![sl],"*") and similar parts of the query, I take it that it is Access transforming this into some kind of parameter or constant. It can also be desireable to change LIKE to STARTING if appropriate, since STARTING can use an index. HTH, Set 2016-01-20 7:07 GMT+01:00 'checkmail' check_m...@satron.de [firebird-support] <firebird-support@yahoogroups.com>: > > > Hello and good morning, > > > > the situation, firebird classic server 2.1 before, firebird classic server > 2.5 (newest) after. The frontend is Microsoft Access, the tables odbc > linked. Now, after the migration I open a Formular in Access, everything is > fast, in less then one second the Data were displayed. But if I open the > Formular the second time, I must wait 20 seconds. All Fields are indexed. > > > > The sql-code in Access > > SELECT TBESTELLUNGEN.JAHR, TBESTELLUNGEN.NR, TBESTPOS.LIEFERTERMIN, > TBESTPOS.LIEFERZEIT, TBESTPOS.GELIEFERT, TTEILE.TEILENR, > TTEILE.BEZEICHNUNG, TKUNDEN.FIRMA, TBESTELLUNGEN.GELIEFERT AS komplett, > TBESTPOS.ANZAHL, TBESTPOS.GEWOGEN, TBESTPOS.BESTAETIGT, > TBESTPOS.ABGESCHLOSSEN, TBESTPOS.TATSLT, > IIf([einheitnr]=6,[tbestpos].[geliefert],[tbestpos].[geliefert]*[tteile].[gewicht]) > AS gewicht, UCase([tteile].[teilenr]) AS TNRG > > FROM TKUNDEN INNER JOIN (TTEILE INNER JOIN (TBESTPOS INNER JOIN > TBESTELLUNGEN ON (TBESTPOS.NR = TBESTELLUNGEN.NR) AND (TBESTPOS.JAHR = > TBESTELLUNGEN.JAHR)) ON TTEILE.TEILENR = TBESTPOS.TEILENR) ON > TKUNDEN.KUNDENNR = TBESTELLUNGEN.LIEFERANTNR > > WHERE (((TBESTPOS.ABGESCHLOSSEN)=-1) AND > ((TBESTPOS.TATSLT)>=[forms]![fbestelleingang].[sa] And > (TBESTPOS.TATSLT)<=[forms]![fbestelleingang].[sb]) AND > ((UCase([tteile].[teilenr])) Like > nz(UCase([Formulare]![fbestelleingang]![st]) & "*","*")) AND > ((TKUNDEN.KUNDENNR) Like nz([Formulare]![fbestelleingang]![sl],"*"))) > > ORDER BY TBESTPOS.LIEFERTERMIN DESC , TBESTPOS.LIEFERZEIT DESC; > > > > It works fine with fb 2.1, but now.. What has been changed? The last odbc > driver is installed > > > > Best regards > > > > Olaf > > > > > > > >
