Dear Alan, dear Alexander,
I've compared the metadata of the origin and the backuped database, and
they are eqaul. So I assume that the indices didn't get lost.

Then I've looked on the plans, and they do differ. So I tried to provide
the plan to the firebird server, and that worked perfekt:
time on origin db : ~ 4 seconds
time on backup db : ~ 4 minutes!! (no typo,
it's real, belive me)
time on backup*db with plan from origin db : ~ 3 seconds (here again, no
typo)

Here is the SQL-Statement and plans that the firebird server used, if I
don't provide the plan:

SELECT
POSGRP."POSID",
GRPREL."PARENTID",
GRPREL."CATEGORY"
FROM
"T_POSGROUPINGS" POSGRP,
"T_PRODUCT_GROUP_RELATION" GRPREL,
"T_INVLINE" LIN,
"T_COSTITEM" CI
WHERE
( POSGRP."RECHID" = 14 ) AND /*Index*/
( POSGRP."GROUPID" = GRPREL."CHILDID" ) AND /*Second part of primary
key & foreign key to another table T - foreign key to same table T*/
( POSGRP."POSID" = LIN."ID" ) AND /*First part of primary
key & foreign key to lin.id - Primary Key*/
( CI."ID" = LIN."COSTITEMID") /*primary key - foreign key
to ci.id*/
ORDER BY
CI."COSTITEM", /*index*/
LIN."PRODWGRPID",
LIN."DATEFROM",
LIN."ID" /*primary key*/

restored db:
PLAN SORT (JOIN (LIN NATURAL,CI INDEX (RDB$PRIMARY22),POSGRP INDEX
(RDB$PRIMARY49,RDB$FOREIGN122),GRPREL INDEX (RDB$FOREIGN109)))

original db:
PLAN SORT (JOIN (POSGRP INDEX (RDB$FOREIGN122),LIN INDEX
(RDB$PRIMARY31),CI
INDEX (RDB$PRIMARY22),GRPREL INDEX (RDB$FOREIGN109)))

where comes the natural join from?

table count(*)
T_INVLINE 30000
T_COSTITEM 200
T_POSGROUPINGS 300000
T_PRODUCT_GROUP_RELATION 1000


Best regards/liebe Grüße aus einem regnerischen Wien
André Dostal


-----Ursprüngliche Nachricht-----
Von: Alexander V.Nevsky [mailto:ded@...]
Gesendet: Mittwoch, 05. Mai 2004 19:44
An: firebird-support@yahoogroups.com
Betreff: [firebird-support] Re: Bad Performance after Backup-Restore

--- In firebird-support@yahoogroups.com, André Dostal <ad7@g...> wrote:
> Hi,
> I've backuped and restored a 70MB-Firebird-DB-File, that ended up in a

> 30MB-DB-File. That's fine, but after performing that tasks, the Server

> takes about 3..4 times longer for carring out SQL-Statements. Has
> anyone any hint?

Are plans for slowed queries the same as on old database instance?
Did'nt you changed FB version or some config settings? Are you sure
restore is successfully finished, not failed when creating one of
indices?

Best regards,
Alexander.





Yahoo! Groups Links