Sorry I can't help at all, Kjell, I'm all "old style query plan". Though
I'm baffled by the new style changing when the old style remains and would
love if someone could explain...
Set
fre. 1. mai 2020 kl. 11:56 skrev Karol Bieniaszewski
liviusliv...@poczta.onet.pl [firebird-support] <
You can try also pseudo inner join (left join with where clause)
Instead of
T1 INNER JOIN T2 ON T1.ID1=T2.ID2
change it to
T1 LEFT JOIN T2 ON T1.ID1=T2.ID2
WHERE
T2. ID2 IS NOT NULL
regards,
Karol Bieniaszewski
Well, the changed subselect didn't do the trick, but I think it speeds
up the query slightly, overall.
Now, it's this query that's causing problems:
insert into "TmpFKExportUhant" ("ECO_ID", "Bärartyp")
select FtgOmsar."Uppgiftshanterare", 'FöretagOmsättningsår' "Bärartyp"
from "Företag" F
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
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
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
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
Maybe it's worth noting that if I execute that exact query in
FlameRobin, while the batch is running and suffering 50+ second
execution time, FlameRobin gets it executed in 2-4 seconds every single
time.
Weird.
I'll try to rewrite the query using execute block, a temporary table or
something
Yes, but the transaction just wasn't there.
I did let it run during the night. Here are FBMonitor diagrams from
about 1 hour before slowdown up to now:
https://cdn.mise.se/FirebirdDiagrams.png
The vertical line in each diagram marks the time of slowdown (at about
22:05 last night).
The upper
Simply run query with join two tables MON$ATATACHEMENTS and MON$TRANSACTIONS –
you can also be interested with joining with MON$STATEMENTS
And then you can see which attachement consume this transaction (you can
retrive IP port and proces ID (PID)
Pozdrawiam,
Karol Bieniaszewski
Thanks,
I tried firing up Gregor Kobler's FBMonitor (similar to the old but dead
Sinatica Monitor).
I found that OAT is 222085103 while OIT and OST (what's that?) is
261140768 and growing. I assume the OAT must have got stuck somehow
quite a while back, since it's so far behind the others.
Hi
There are many possibilities without access i can only hint you:
Look at MON$Tranasctions maybe you have active one which stop garbage collecion.
Look also at sort buffer setting if firebird.conf
Look at settings about buffers in database itself (gfix -h show you value).
Look also at automatic
12 matches
Mail list logo