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.
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 primary key of the table VAREFRVSTR_DETAIL.
Primary key is:
PRIMARY KEY (VAREPLU_ID, FARVE_NAVN, LAENGDE_NAVN, STOERRELSE_NAVN,
AFDELING_ID)
All fields in the primary key is VARCHAR(30).
I would never do it this way, but this is before my time, and was setup in
1999.
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 primary 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 can I by any means changed this trigger to not use primary key.
Logically it only need VAREFRVSTR_DETAIL_VNR.
