Hello,

thank you for your very helpful answer!
I solved the problem by importing the indexes after importing data.

Best wishes
Marco


________________________________
Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Im Auftrag von Svein Erling Tysvær
Gesendet: Freitag, 21. August 2009 10:16
An: 'firebird-support@yahoogroups.com'
Betreff: RE: [firebird-support] Worse performance in database copy than original database (was: Problem)



First Marco, I changed your subject since your original subject was general enough to fit well over 90% of all threads in this list.

My guess is that your problem stems from the statistics for indexes being out of date after importing the data, making the optimizer choose suboptimal plans. The simplest way to fix this, would be to create the indexes after the data have been imported rather than before. This should also have the benefit of the import taking somewhat shorter time. Other alternatives (if you for some reason doesn't want to suspend creation of the indexes) is to run 'update statistics' or count or select all records of all tables.

By the way, my guess is probably incorrect if everything is ten times slower on the copy, but if some queries execute equally quick on the copy as the original whereas others are extremely slow, then I think I could be right.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com<mailto:firebird-support%40yahoogroups.com> [mailto:firebird-support@yahoogroups.com<mailto:firebird-support%40yahoogroups.com>] On Behalf Of Guettler, Marco (Allianz Deutschland)
Sent: 21. august 2009 09:29
To: firebird-support@yahoogroups.com<mailto:firebird-support%40yahoogroups.com>
Subject: [firebird-support] Problem

Hello,

I use 2 firebird databases with the same content in my tables and the same indexes.
The first database is the original one, doesn't have any performance problem.
My own, the second database has a bad performance (containing the same data and indexes),
about factor 10 slower.
I have to create a new, identical database because of different reasons (a clone by copy and paste is not possible).
The procedure of creating the second database:

1. open der Security-DB (using standard user sysdba and password):
...\Firebird\bin>gsec.exe -user sysdba -password masterkey -database localhost:C:\Programme\Firebird\security2.fdb
2. adding new user
GSEC> add myuser -pw mypw -fname C:\test\db\Firebird\xps.fdb
3. create new database using example database
...\Firebird\bin>isql localhost:C:\Programme\Firebird\examples\empbuild\employee.fdb -u myuser -p mypassword
SQL> create database 'localhost:C:\test\db\Firebird\xps.fdb';
4. export scheme and indexes from the original database
5. import scheme and indexes into my new database
6. copy all data from tables (not system-tables) of the original database into the new database

Maybe the problem is caused by the content of system tables I did not copy into the new database?
But I really don't know which system tables are relevant and which data is necessary?

Additional informations
version: WI-V2.1.1.17910 Firebird 2.1
model: classic as application (later embedded usage)
language: java
os: Windows XP Professional (32bit) version 2002, SP2

It would give me great pleasure if somebody can help me. Thank you very much!
Best wishes, Marco





[Non-text portions of this message have been removed]