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]
