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.