Svein and Karol (not Livius, sorry),

no it's not a secret.
I have a complete turnkey, in separate FDB, since I need it to create
some C# examples for students on different machines anyway, and if you
want I can share it.

But - before you're wasting precious time: I already know, why the
plan is different; once Karol got me on the way, the rest was easy to
find, even for me :-)

It's got nothing to do with sub releases!

If you want to know more, I need to be a bit verbose about the
scenario in the following description.

I am now puzzling over why this happens, one step earlier in the
process, on rolling out the scenario to the different machines.

The databases should be completely identical, being restored from the
same master backup, to overwrite existing FDB. For some reason,
though, on very few machines, the resulting restored FDB is not
identical to the master.

The problematic difference is that in the "fast" FDB I changed 1
ID-Field from Char(6) to BigInt and recreated the primary key index on
it. The other JOINed column is of type BigInt anyway, so that
sufficiently explains the great gain in speed.

That change is in several restored FDBs, but not in 2 of them, which
just coincidentally also had 2.5.3 running instead of 2.5.7.

Some sort of corruption must be in there or going on, which can affect
parts/pages of a restore (or a backup).

I remember having different such cases before, never on production
machines, always in testing environments. Once I also posted one
problem here, but found that it must be something specific with my
environment, not with Firebird at all.

Both problematic machines are running an outdated version of Win-10
and I am ready to believe that there is the culprit somewhere.

The problem is: it is not reliably reproducible. I ran the same steps
over and over again, overwriting an older version of the FBD with the
same backup. Close to 9/10 cases it works as desired, but sometimes
after the restore the resulting FDB has only some of the last changes,
but that one column is still of the old type.

I am sure it's only my environment; nobody knows what has been done to
those 2 or 3 machines (if it's only those). I will probably just reset
them.

But if you have need of a standalone example of why IDs meant for
JOINing best should be of the same type *OR* - if the type can't be
normalized (e.g. for legacy reasons) - one can use a selectable
storedproc instead of a simple SQL-Statement to boost speed, you're
welcome! :-)

I'll then translate german identifiers to english and send a download
link, database is 17 MByte.

André


=====================================================================
Ihre Nachricht:

>
>

>
> 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?



> Set


> Den man. 12. nov. 2018 kl. 14:45 skrev André Knappstein
> Knappstein@... [firebird-support]
> <firebird-support@yahoogroups.com>:

> 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é
>> 
>> 
>
>
>>   
>
>
>
> ------------------------------------
> Posted by: =?utf-8?Q?Andr=C3=A9_Knappstein?= <Knappstein@...>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org 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
> http://www.ibphoenix.com/resources/documents/
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> ------------------------------------
>
> Yahoo Groups Links
>
>
>
>
>
>
>
>