I don't know why things slow down, Kjell, if the batch job updates records,
I guess it could be related to there being lots of versions of some records
and that garbage are only collected after your application ends (it sounds
plausible if transactions are still open when you "Stopping the batch and
restarting it, with the app still open". Maybe you should check if there's
a large gap between oldest and next transaction when you stop the batch
(similar to Karols first recommendation)? Another thing that would not harm
is to change from
where Ar."Årtal" = ( select max( Ar2."Årtal" )
from "FöretagOmsättningsår" FtgOmsar2
inner join "År" Ar2 on Ar2.."ECO_ID" =
FtgOmsar2."Omsättningsår"
where FtgOmsar2."Företag" = F."ECO_ID" )
to
where not exists ( select 1
from "FöretagOmsättningsår" FtgOmsar2
inner join "År" Ar2 on Ar2.."ECO_ID" =
FtgOmsar2."Omsättningsår"
where FtgOmsar2."Företag" = F."ECO_ID"
and Ar."Årtal" < Ar2."Årtal" )
in the second part of your query. This will probably not help with the
slowdown, but it should not be slower than your max( and it may be slightly
quicker (although the table names "FöretagOmsättningsår" and "År" does
indicate these are tiny tables).
Set
ons. 29. apr. 2020 kl. 13:44 skrev Kjell Rilbe
[email protected] [firebird-support] <
[email protected]>:
> Hi,
>
> I have a batch job that among other things executes a certain SQL query
> to load some data.
>
> This query takes about 10-12 seconds the first ~320 times, but then
> suddenly takes ~45-50 seconds every time.
>
> Any idea what could be causing this?
>
> The query is rather long, see below. Firebird 3.0.4 on Windows Server
> 2016 64 bit. App is ASP Framework using FirebirdSql.Data.FirebirdClient
> 7.1.1.0.
>
> I can't see any other operations slowing down at the same point in time.
> I can't see any particular change in the app's memory usage (it varies
> up and down both before and after the time of slowdown). I did try a
> change of Firebird's cache settings (more page buffers), but it didn't
> seem to have any effect (yes I did restart FB service to ensure changes
> took effect). Windows file system cache doesn't seem to hit the roof at
> this particular time, so I see no connection there.
>
> Stopping the batch and restarting it, with the app still open, doesn't
> help, but restarting the entire app leads to the shorter execution time
> again.
>
> Each execution of this SQL query is done in a new connection (not
> pooled). Before this SQL, the same connection is also used to empty the
> table "TmpFKExportId" and then fill it with some new data, a simple list
> of 250 bigints).
>
> The app does do other DB work that spans multiple executions of this SQL
> query, using a separate connection. Those operations do not exhibit any
> noticable slowdown.
>
> I'm at a loss... Any suggestions of things to try are most welcome!
>
> SQL:
>
> select distinct Upg."ECO_ID", Upg."ECO_TYPE"
> from (
> select 'Företag' "Bärartyp", F."ECO_ID" "Bärare", Uhant."ECO_ID"
> from "Företag" F
> inner join "TmpFKExportId" L on L."ECO_ID" = F."ECO_ID"
> inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" =
> F."Uppgiftshanterare"
> union
> 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"
> inner join "År" Ar on Ar."ECO_ID" = FtgOmsar."Omsättningsår"
> inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" =
> FtgOmsar."Uppgiftshanterare"
> where Ar."Årtal" = (
> select max(Ar2."Årtal")
> from "FöretagOmsättningsår" FtgOmsar2
> inner join "År" Ar2 on Ar2.."ECO_ID" = FtgOmsar2."Omsättningsår"
> where FtgOmsar2."Företag" = F."ECO_ID"
> )
> union
> select 'ITmiljö' "Bärartyp", ITmilj."ECO_ID" "Bärare", Uhant."ECO_ID"
> from "Företag" F
> inner join "TmpFKExportId" L on L."ECO_ID" = F."ECO_ID"
> inner join "ITmiljö" ITmilj on ITmilj."ECO_ID" = F."ITmiljö"
> inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" =
> ITmilj."Uppgiftshanterare"
> union
> select 'Befattningsinnehav' "Bärartyp", BI."ECO_ID" "Bärare",
> Uhant."ECO_ID"
> from "Företag" F
> inner join "TmpFKExportId" L on L."ECO_ID" = F."ECO_ID"
> inner join "Befattningsinnehav" BI on BI."Företag" = F."ECO_ID"
> inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" =
> BI."Uppgiftshanterare"
> union
> select 'Person' "Bärartyp", P."ECO_ID" "Bärare", Uhant."ECO_ID"
> from "Företag" F
> inner join "TmpFKExportId" L on L."ECO_ID" = F."ECO_ID"
> inner join "Befattningsinnehav" BI on BI."Företag" = F."ECO_ID"
> inner join "Person" P on P."ECO_ID" = BI."Person"
> inner join "Uppgiftshanterare" Uhant on Uhant."ECO_ID" =
> P."Uppgiftshanterare"
> ) Uhant
> inner join "Uppgiftshållare" UH on UH."Hanterare" = Uhant."ECO_ID"
> inner join "Uppgift" Upg on Upg."Hållare" = UH."ECO_ID"
> left join "Uppgiftsspärr" SpUhant on SpUhant."Uppgiftshanterare" =
> Uhant."ECO_ID" and Uhant."Bärartyp" in ('Epostadress', 'Person')
> left join "Uppgiftsspärr" SpUH on SpUH."Uppgiftshållare" = UH.."ECO_ID"
> where Upg."TogsBort" is null
> and SpUhant."ECO_ID" is null
> and SpUH."ECO_ID" is null
> and not exists (
> select 1
> from "Uppgiftshållare" UH2
> where UH2."ECO_ID" = Upg."RelateradHållare"
> and (
> exists (
> select 1
> from "Uppgiftsspärr" Sp2
> where Sp2."Uppgiftshållare" = UH2."ECO_ID"
> )
> or exists (
> select 1
> from "Uppgiftsspärr" Sp2
> inner join "Uppgiftshanterare" Uhant2 on Uhant2."ECO_ID" =
> Sp2."Uppgiftshanterare"
> where Uhant2."ECO_ID" = UH2."Hanterare"
> )
> )
> )
>
> Mvh,
> Kjell
>
>
>
> [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
>
>
>
>
