Subject Re: [firebird-support] Speed difference 2.5.3 - 2.5.7
Author Svein Erling Tysvær
Almost seems like the PI_T_INVOICES is lacking or not active. Either that or that the part of it being used for the query in question has lousy selectivity. It is not unusual for the optimizer to think that two PLANs are almost equally good and then sometimes make a terrible choice, but I'm baffled by your good and bad plan being very similar excepting that one of them uses a query and the other doesn't. Is the query itself a secret or can you share it here?


Den man. 12. nov. 2018 kl. 14:45 skrev André Knappstein Knappstein@... [firebird-support] <>:
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"

"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:

slow execution:

Query   and   database   are  identical,  both  created  from the same

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!


> Probably difference in query plans.
> Show query plan for both server versions

> Regards,Karol Bieniaszewski
> -------- Oryginalna wiadomość --------
> Od: "André Knappstein Knappstein@...
> [firebird-support]" <>
> Data: 12.11.2018  12:01  (GMT+01:00)
> Do:
> 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,
> André


Posted by: =?utf-8?Q?Andr=C3=A9_Knappstein?= <Knappstein@...>


Visit and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at


Yahoo Groups Links

<*> To visit your group on the web, go to:

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    (Yahoo! ID required)

<*> To change settings via email:

<*> To unsubscribe from this group, send an email to:

<*> Your use of Yahoo Groups is subject to: