Hi,

At May 6, 2004, 08:05, André Dostal wrote:

> 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?

You say that the indices are there. Can you confirm that they all are
active(since an index can be set to inactive)?

--
Best regards,
Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)