Probably difference in query plans.Show query plan for both server
versionsRegards,Karol Bieniaszewski
-------- Oryginalna wiadomość --------Od: "André Knappstein
[email protected] [firebird-support]"
<[email protected]> Data: 12.11.2018 12:01 (GMT+01:00) Do:
[email protected] 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:
https://www.firebirdsql.org/file/documentation/release_notes/Firebird-2.5.8-ReleaseNotes.pdf
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,
André
