Subject Re: [firebird-support] Performance Problem after Migration fb 2.1 cs to fb 2.5 cs both X86
Author Svein Erling Tysvær
Post date 2016-01-20T08:55:57Z
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_mail@... [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