Please show us the definition of RDB$FOREIGN105 and VAREFRVSTR_DETAIL_VNR. Set
2017-11-21 12:38 GMT+01:00 [email protected] [firebird-support] <[email protected]>: > > > 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. > > > > > > > >
