Hi all
Sorry for my late answer and thank you all for your input. We experienced a
large performance drop friday and it lasted until monday around 17.
The place where we host our servers, had an update of some antivirus software
which gave huge problems.
At first we suspected Firebird and the new server, so late sunday night I
moved all back to the old server. Performance still very low.
And - Sorry to yhou guys how gace me an answer. I deleted them all by
accident.
Sorry!
That said - when we finally folund the reason, i got back to try to locate
perfomance bottleneck on this one routine.
I located a BEFORE INSERT trigger, which causes this problem.
This one:
CREATE TRIGGER WEB_UDSALG_SPR FOR VAREFRVSTR_DETAIL INACTIVE BEFORE INSERT
POSITION 11 AS
begin
Select
MAX(WEB_Udsalg_Spr),
MAX(WEB_Udsalg_Stk),
MAX(WEB_Udsalg_Type)
from VareFrvStr_Detail Where
VarePlu_ID=NEW.VarePlu_ID and
Afdeling_ID=NEW.Afdeling_ID
Into
NEW.WEB_Udsalg_Spr,
NEW.WEB_Udsalg_Stk,
NEW.WEB_Udsalg_Type;
end
Disabling this one and performance for this one routine increased back to
normal.
If I try runing this query in DBW and look at the plan, its like this:
PLAN (VAREFRVSTR_DETAIL INDEX (VAREFRVSTR_DETAIL_VNR, RDB$FOREIGN105))
VAREFRVSTR_DETAIL_VNR: 0,00001301287
(Foreign key to field PLU_NR in table VARER with 76.863 rows)
RDB$FOREIGN105: 0,01724137925
This is the foreign key to AFDELINGSNUMMER in table AFDELING (containing 58
records)
I then did a SET STAT for the above 2 indeices.
Afterwards its like:
RDB$FOREIGN105: 0,01724137925
VAREFRVSTR_DETAIL_VNR: 0,00001300965
This was the same problem.
Then I tested the PLAN on an older copy.
It then said:
PLAN (VAREFRVSTR_DETAIL INDEX (VAREFRVSTR_DETAIL_VNR))
So on this server, where it works fast, the plan does not use the other
ofreign key (RDB$FOREIGN105).
This stat of the two indices on this old server is:
RDB$FOREIGN105: 0,01724137925 (but not used).
VAREFRVSTR_DETAIL_VNR: 0,00001332285
So they are more or less alike.
On this server (my testserver) im running FB 2.5.3 SS.
This means - this must be the reason for the performance drop.
Right?
And the solution is to drop the foreign key to table AFDELING, right?
