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
[email protected] [firebird-support] <
[email protected]>:
> 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
> [email protected] [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 <[email protected]>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 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
>
>
>
>
