[firebird-support] error with gbak

2017-11-17 Thread 'Raether, Olaf' olaf.raet...@wink.de [firebird-support] 
I´trying to restore a firebird 2.5.2 database with gbak.
But I´m getting the following error:

gbak:restoring privilege for user ALLGEMEIN
gbak: ERROR:action cancelled by trigger (1) to preserve data integrity
gbak: ERROR:could not find column for GRANT
gbak:Exiting before completion due to errors


How can I find out what´s wrong with this database ?

Olaf Raether


___

Wink Stanzwerkzeuge GmbH & Co. KG | Gesellschaftssitz: Neuenhaus | 
Handelsregister: Amtsgericht Osnabrück, HRA 130647 | Persönlich haftender 
Gesellschafter: Wink Verwaltungsgesellschaft mbH | Gesellschaftssitz: Bielefeld 
| Handelsregister: Amtsgericht Bielefeld, HRB 38456 | Geschäftsführer: 
Ferdinand Oetker

Please consider the environment before printing this document.
___

Re: [firebird-support] Performance problem - input wanted

2017-11-17 Thread setysvar setys...@gmail.com [firebird-support] 
Just thought of a very simple test you could run, Michael. Is the plan 
identical for the following query on the old and new server?

select *
from rdb$database
where 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)

Set

Re: [firebird-support] Performance problem - input wanted

2017-11-17 Thread Brian Dunstan br...@dunstan.biz [firebird-support] 
Hi Michael,

I would be very suspicious of the config of the new VM.

Are there any non Firebird performance benchmark programs you can run on the 
new and old VMs for comparison?

Can you do a restore of the DB onto the old server and run you FB tests again 
for comparison? A restore will recalculate the selectivity of all the indexes, 
collects garbage etc. After a restore you should be comparing like for like.

I have seen inexplicable changes to query optimisation between database 
instances, but that was FB 1.5. Break up you SP into component queries and look 
at the PLAN generated for each one in an interactive SQL tool.

Such big performance differences are likely to be due to index selection or VM 
configuration. I’ve had many bad experiences with VM config so I’d look there 
first.

Another interesting comparison would be to build a test server on an ordinary 
PC and see how that compares to the VM.

Regards,
Brian
Sent from my mobile device. Please excuse brevity.

On 18 Nov 2017, at 1:09 am, 
michael.vilhelm...@microcom.dk 
[firebird-support] 
> 
wrote:



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,

Re: [firebird-support] Performance problem - input wanted

2017-11-17 Thread Svein Erling Tysvær setys...@gmail.com [firebird-support] 
Not answering your question, just curious whether

  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)
  SELECT :PVarePlu_ID, :PFarve_Navn, :PLaengde_Navn,
:PStoerrelse_Navn, :PV509Index, :PMinBeholdning, :PGenbestilling,
:PNormalStkAntal, spe.STDVALUTA,
 :PProvision, spe.STDMOMS, :PVejetKostPrisStk,
:PSalgsPrisStk, :PEngrosPris, :PEnhedsNogle, :POprettelsesDato, :PLeverID,
:PVareGrpID, A.AFDELINGSNUMMER
  FROM AFDELING A
  INNER JOIN STAMDATA_PRG_EXT spe ON spe.AFDELING_ID =
A.AFDELINGSNUMMER
  WHERE A.AFDELINGSNUMMER <> :PAFDELING_ID
and not exists (select v509Index from VAREFRVSTR_DETAIL vd
where VarePlu_ID = :PVarePlu_ID and
  Afdeling_ID = A.AFDELINGSNUMMER and
  Farve_Navn = :PFarve_Navn and
  Stoerrelse_Navn = :PStoerrelse_Navn and
  Laengde_Navn = :PLaengde_Navn)

is identical and takes the same time. You may have to cast the parameters
to appropriate type.

The select part of this insert may also help in discovering whether or not
the same plan is used on the old and new server.

Set

2017-11-17 13:27 GMT+01:00 Рустам Муса-Ахунов rusta...@ukr.net
[firebird-support] :

> Try to determine slowest query:
>
> - "SELECT ...  FROM  AFDELING A  INNER JOIN STAMDATA_PRG_EXT ..."
> or
> - "select v509Index from VAREFRVSTR_DETAIL ..."
> or
> - "Insert into VareFrvStr_Detail ..."
>
>
> Problem can be with indexes - check whether all indexes exists in new DB
> (after restore).
> Also check your INSERT triggers on VAREFRVSTR_DETAIL (if any).
>
>
> 
> Posted by: =?UTF-8?b?0KDRg9GB0YLQsNC8INCc0YPRgdCwLdCQ0YXRg9C90L7Qsg==?=
> 
> 
>
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu.  Try FAQ and other links from the left-side menu
> there.
>
> Also search the knowledgebases at http://www.ibphoenix.com/
> resources/documents/
>
> ++
> 
>
> Yahoo Groups Links
>
>
>
>

[firebird-support] Performance problem - input wanted

2017-11-17 Thread Рустам Муса-Ахунов rusta...@ukr.net [firebird-support] 
Try to determine slowest query:

- "SELECT ...  FROM  AFDELING A  INNER JOIN STAMDATA_PRG_EXT ..."
or
- "select v509Index from VAREFRVSTR_DETAIL ..."
or
- "Insert into VareFrvStr_Detail ..."


Problem can be with indexes - check whether all indexes exists in new DB (after 
restore).
Also check your INSERT triggers on VAREFRVSTR_DETAIL (if any).

[firebird-support] Performance problem - input wanted

2017-11-17 Thread michael.vilhelm...@microcom.dk [firebird-support] 
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