Subject Re: [firebird-support] Speed difference 2.5.3 - 2.5.7
Author liviuslivius
Probably difference in query plans.
Show query plan for both server versions

Karol Bieniaszewski

-------- Oryginalna wiadomość --------
Od: "André Knappstein Knappstein@... [firebird-support]" <>
Data: 12.11.2018 12:01 (GMT+01:00)
Temat: [firebird-support] Speed difference 2.5.3 - 2.5.7


Hello all,

for a local conference, I am preparing a session for a
Firebird beginners' group, to demonstrate the basics of Stored
Procedures and their usage from different clients.

One scenario is about transforming a *slightly* complex sql query into
a selectable stored proc which will yield the same result.

I stumbled over something strange:
Query and SP are running with similar same speed on 2.5.7, which I
have in production systems. But query is *MUCH* slower than stored
proc on different(!) 2.5.3 systems (more rarely used machines, I
failed to upgrade so far).

2.5.7. systems: ~ 3 seconds for Query and SP
2.5.3. systems: ~ 3 seconds for SP, 15 minutes(!) for Query
always tested from a fresh restored database with starting results
normalized to 0,00.

It does not look like a difference in the machines' hardware or even
the used antivirus or OS can cause this big a difference.

Just out of curiosity - because all production systems should be
updated to at least 2.5.7 anyway: has there been such an
improvement between sub releases?

I checked the change logs:
and checked all between 2.5.3 and 2.5.8, but found nothing that
matches this experience.

Query is pretty much straigthforward; one nested subquery to update
outer stream, no UDF or other built-in functions

best regards,