Hi Set! Understood and I thank you for pointing it out. My last attempt was to put all the Uhant."ECO_ID" in a temp table and then use that to run the last part of the query. That avoids the unions, but adds some extra work to store the records in the temp table. All in all I suppose it's slightly more work for the engine.
But as it turns out, it's that part of the query that suddenly takes a lot longer to execute. Trying now with the alternative subselect you suggested earlier, i.e. not exists (...). If that doesn't work out, I'll try the +0 you suggest below. Thumbs up! Regards, Kjell Den 2020-04-30 kl. 23:14, skrev Svein Erling Tysvær setys...@gmail.com [firebird-support]: > Hej Kjell, > > I'm not used to seeing plans this way, but if I'm reading the plan > correctly, then adding +0 or || '' (depending on the field type) > immediately after your first union like this: > > select 'FöretagOmsättningsår' "Bärartyp", FtgOmsar."ECO_ID" > "Bärare", Uhant."ECO_ID" > from "Företag" F > inner join "TmpFKExportId" L on L."ECO_ID" = F."ECO_ID" > inner join "FöretagOmsättningsår" FtgOmsar on FtgOmsar."Företag" > = F."ECO_ID" +0 > inner join "År" Ar on Ar."ECO_ID" = FtgOmsar."Omsättningsår" +0 > inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" = > FtgOmsar."Uppgiftshanterare" > > ought to speed up your query. The point being that your slow plan only > is an option if indices can be used for FtgOmsar."Omsättningsår" and > F."ECO_ID" and that this kind of addition prevents those indices from > being used. > > HTH, > Set > > tor. 30. apr. 2020 kl. 17:43 skrev Kjell Rilbe > kjell.ri...@marknadsinformation.se > <mailto:kjell.ri...@marknadsinformation.se> [firebird-support] > <firebird-support@yahoogroups.com > <mailto:firebird-support@yahoogroups.com>>: > > Thanks Karol! I will consider explicit plan if my current rewrite > of the > query doesn't pan out. > > The table "TmpFKExportId" will always have the same number of > records, > but a different set (each chunk of the batch will load the same > number > of id:s, but of course different id values). Statistics should be > constant. > > The other tables will vary slightly over time, but the database > contains > 2+ million companies and related data. During the batch, the only > updates to this data is from our TM staff who phone companies and > enter > data manually, one company at a time. So overall, the change rate is > minute. And in particular, there's no big change exactly 318 > chunks into > the batch job, every time. > > Yes, the query is hardcoded as a string literal into the app's source > code. It can't get more "same" than that. :-) > > Mvh, > Kjell > > Den 2020-04-30 kl. 17:06, skrev Karol Bieniaszewski > liviusliv...@poczta.onet.pl <mailto:liviusliv...@poczta.onet.pl> > [firebird-support]: > > > > I suppose you have two different queries – one with where clause > and > > one without on one of the tables involved in the query. > > Are you sure that the queries are the same? > > > > But also it is quite usual that after new data changes the plan is > > about to change. > > Is this true for your case that some table got more records > after fill > > then previously second table? > > Especially this one?: > > > > You can try also modify your query to have static plan (some +0) or > > update index statistics after some count of operations. > > > > Regards, > > Karol Bieniaszewski > > > > [Non-text portions of this message have been removed] > > > > > > > [Non-text portions of this message have been removed] > > > > ------------------------------------ > Posted by: Kjell Rilbe <kjell.ri...@marknadsinformation.se > <mailto:kjell.ri...@marknadsinformation.se>> > ------------------------------------ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > 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 > > > firebird-support-fullfeatu...@yahoogroups.com > <mailto:firebird-support-fullfeatu...@yahoogroups.com> > > > [Non-text portions of this message have been removed]
