Livius, thank you. And: cool! You obviously have hit *something*. I learned before on different groups and seminars, that when comparing different execution times you better start by looking at the plans... only... I usually don't :-)
So, the question needs to be changed from: "Was there such a change between subreleases" to: "what have I done or omitted - unknowingly - to get different plans?" I don't manipulate query plans, in fact I never touch them, or any settings related to them because in my case surely the risk of breaking more than I would be fixing is substantial :-) But now when I look at them, they are different on the fast vs. the slow machines!!! Here is what I get for the query: fast execution: PLAN JOIN (JOIN (I INDEX (PI_T_INVOICES), R2 INDEX (I_T_RESULTS)), K INDEX (I_T_KEYS) PLAN (R1 NATURAL) slow execution: PLAN MERGE (SORT (JOIN (I NATURAL, K INDEX (I_T_KEYS))), SORT (R2 INDEX (I_T_RESULTS))) PLAN (R1 NATURAL) Query and database are identical, both created from the same backup. But I have an idea which I will check later this afternoon. It's all probably just something on 2 or 3 of my systems. I'll report back! thanks, André > > Probably difference in query plans. > Show query plan for both server versions > Regards,Karol Bieniaszewski > -------- Oryginalna wiadomość -------- > Od: "André Knappstein knappst...@beta-eigenheim.de > [firebird-support]" <firebird-support@yahoogroups.com> > Data: 12.11.2018 12:01 (GMT+01:00) > Do: firebird-support@yahoogroups.com > 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é > > >
