Amazing!!! Look at the new plan (speed) below!

Due to this the amalgamated report also works in a second or two, thanks a million S.E.T.

So is it good advise to look for the NATURAL in plan and set indexes of the related table.field? Or there is more thoughts to it than just this? I still do see a NATURAL in the plan below but the fields IID (which is a PK) and IPID (which is a FK) have index on them, why would that be? Though many FKs in the database have index but there are still some that do not have a constraint on them thus do not have an index, is it a good practice to have all FK indexed constraint or not?

Please advise

BTW the GTT is a misnomer it is a regular table that has the iIDs that the user selected of items to report on. I had planned on a real GTT but due my program limitations about connections I could not use them and created regular tables instead.

Thanks once again

Kind regards
Bhavbhuti


PLAN (HEIR D INDEX (FK_MITEMS_IPID))
PLAN SORT (JOIN (JOIN (JOIN (HEIR P NATURAL, HEIR GTT INDEX (PK_MITEMS_IID))
PLAN (HEIR P2 INDEX (FK_MITEMS_IPID)), SGRNITEM INDEX (FK_SGOODSRECVNOTEITEM_IITEMID), TGRN INDEX (PK_TGOODSRECVNOTE_IID))))


Executing statement...
Statement executed (elapsed time: 0.000s).
67203 fetches, 2 marks, 135 reads, 2 writes.
0 inserts, 0 updates, 0 deletes, 4388 index, 10197 seq.
Delta memory: 80560 bytes.
Total execution time: 0.078s
Script execution finished.

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
>
> I notice two things, Bhavbhuti:
>
> 1) GTT isn't needed, so you can simplify a little bit (though I don't think this will reduce the execution time much):
>
> WITH RECURSIVE Heir(iID, iPK) AS
> (SELECT p.iID, p.iID AS iPK FROM mItems p
> WHERE p.lHierarchialStock = 1
> OR NOT EXISTS (SELECT 1
> FROM mItems d
> WHERE d.iPID = p.iID)
> UNION ALL
> SELECT H.iID, p2.iID
> FROM mItems p2
> JOIN Heir H on p2.iPID = H.iPK)
> ...
>
> 2) I don't like PLAN (HEIR D NATURAL) and PLAN (HEIR P2 NATURAL). Do you have an index for mItems.iPID (and is mItems.iPID defined as integer)? If not, then adding this index ought to make your query a lot faster.
>
> HTH,
> Set
>