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 Knappstein@...
> [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é
>
>


>