Hi

I need some input to a problem I struggle with.

I have a Firebird 2.5 database.

The size is somewhat 220 Gb. 

This database has just been moved to a new databaseserver.

This new server is a virtual server with the fastest storage available to us (Where we host our servers, they call it storage T400).

 - Windows Server 2012R2 (both new and old server)

 - 32 Gb RAM (both new and old server)

 - C Drive with Windows on  (both new and old server)

 - E Drive with Firebird database on. Size of drive is 500GB. NEW Server is some SSD storage of some - Old was a Fusion I/O something external storage

 - F Drive is where the dump is made every night. Size 300GB. NEW Server is some SSD storage of some - Old was a Fusion I/O something external storage

 - Firebird 2.5.7 Classic server. Old server was running 2.5.2. Firebird.conf file from old server was copied to new server to ensure same settings

 - New restored database with 16K pagesize. Old had 8K page size. (Os partition has been formatted with 8K pagesize).

 - Usual connections: 150-200

The programs are the same, so the only changes is the server and its hardware.

Some comparisions:

OLD SERVER: 

 - Dump database: 3½ hours .

 - Restore database: 4½ hours

NEW SERVER

 - Dump database: 1 hour 12 minutes

 - Restore database: 2 hours 34 monutes

I have then made several different usually routines on both old and new server, and routines in our program are approx. at least 2 times faster. 

This new server has now been running for 3 days. 

I have only heard about 1 complaint so far. 

We have a routine, which makes new items in our database. 

Lets say I create 10 new items. This customer has 58 departments.

So there must be created 58 * 10 new records.

To this I have a stored procedure. This creates a record to an item to all departments. Like this:

CREATE OR ALTER PROCEDURE OPRET_VARERDETALJEDETAIL4 (

  PENGROSPRIS Numeric(18,2), 

  PVAREPLU_ID VarChar(30), 

  PFARVE_NAVN VarChar(30), 

  PLAENGDE_NAVN VarChar(30), 

  PSTOERRELSE_NAVN VarChar(30), 

  PV509INDEX VarChar(30), 

  PMINBEHOLDNING Numeric(18,2), 

  PGENBESTILLING Numeric(18,2), 

  PNORMALSTKANTAL Numeric(18,2), 

  PVALUTA_NAVN VarChar(10), 

  PPROVISION Numeric(18,2), 

  PMOMSSATS VarChar(25), 

  PVEJETKOSTPRISSTK Numeric(18,2), 

  PSALGSPRISSTK Numeric(18,2), 

  PENHEDSNOGLE Integer, 

  PLEVERID VarChar(30), 

  PVAREGRPID VarChar(30), 

  POPRETTELSESDATO Date, 

  PAFDELING_ID VarChar(30)) AS 

DECLARE VARIABLE AFDNR VARCHAR(15);             

Declare Variable lMomsSatsNavn VarChar(25); 

Declare variable lValuta Varchar(10); 

BEGIN

     For

      SELECT

        A.AFDELINGSNUMMER,

        STAMDATA_PRG_EXT.STDMOMS,

        STAMDATA_PRG_EXT.STDVALUTA

      FROM

        AFDELING A

        INNER JOIN STAMDATA_PRG_EXT ON STAMDATA_PRG_EXT.AFDELING_ID = A.AFDELINGSNUMMER

      WHERE

        A.AFDELINGSNUMMER<>:PAFDELING_ID

     INTO

       :AfdNr, :lMomsSatsNavn, :lValuta

     do  

     Begin 

       if (not exists (select v509Index from VAREFRVSTR_DETAIL where 

                                                       VarePlu_ID=:PVarePlu_ID and 

                                                       Afdeling_ID=:AfdNr and          

                                                       Farve_Navn=:PFarve_Navn and 

                                                       Stoerrelse_Navn=:PStoerrelse_Navn and 

                                                       Laengde_Navn=:PLaengde_Navn)) then 

       begin 

          Insert into VareFrvStr_Detail ( 

            VarePlu_ID, 

            Farve_Navn, 

            Laengde_Navn, 

            Stoerrelse_Navn, 

            V509Index, 

            MinBeholdning, 

            Genbestilling, 

            NormalStkAntal, 

            Valuta_Navn, 

            Provision, 

            MomsSats, 

            VejetKostPrisStk, 

            SalgsPrisStk, 

            EngrosPris, 

            EnhedsNogle, 

            OprettelsesDato, 

            LeverID, 

            VareGrpID, 

            Afdeling_ID) 

          Values ( 

            :PVarePlu_ID, 

            :PFarve_Navn, 

            :PLaengde_Navn, 

            :PStoerrelse_Navn, 

            :PV509Index, 

            :PMinBeholdning, 

            :PGenbestilling, 

            :PNormalStkAntal, 

            :lValuta, 

            :PProvision, 

            :lMomsSatsNavn, 

            :PVejetKostPrisStk, 

            :PSalgsPrisStk, 

            :PEngrosPris, 

            :PEnhedsNogle, 

            :POprettelsesDato, 

            :PLeverID, 

            :PVareGrpID, 

            :AfdNr); 

       end 

     end 

END ^^

Short:

It will itereate through the departments and insert a record per item.

On the old server this called took less than ½ seconds. 

On the new server, this is 3+ seconds. 

This means that creating 10 new items on the old server took 2-4 seconds. Now it takes 30+ seconds. 

If I do the same on a copy I have on a very slow server, it takes some 10 seconds. This is just my testserver, and this is expected.

When doing this item creation, there are a lot of the inserts and updates made, but this one is the only of them, which are significanly slower. 

Table AFDELING contains 58 records. Table STAMDATA_PRG_EXT contains 58 records.

Table VareFrvStr_Detail contains at least 57.500.000 records.

I have checked thegab betweens oldest active transaction and newest one. This is always between 0 and 50.000. This is a the server always is and has been for many years. 

I have a hard time finding a reason why this call suddenly takes a lot longer than before. 

I would have suspected this to be faster. 

What so I look into?