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