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 > knappst...@beta-eigenheim.de [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 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é >> >> > > >> > > > > ------------------------------------ > Posted by: =?utf-8?Q?Andr=C3=A9_Knappstein?= <knappst...@beta-eigenheim.de> > ------------------------------------ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > 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 > > > > > > > >
