Andrew wrote:

>Slow:
>PLAN SORT (JOIN (
> W M NATURAL,
> W W INDEX (RDB$26), /* unique index Items (Manufacturer int, Name
>char) */
> W C INDEX (RDB$PRIMARY3), /* (C)PCategories.ID */
> W U INDEX (RDB$PRIMARY1) /* (C)PCategories.Unit_ID -> (U)Units.ID */
>Fast:
>PLAN SORT (JOIN (
> W C NATURAL,
> W U INDEX (RDB$PRIMARY1), /* (C)PCategories.Unit_ID->(U)Units.ID */
> W W INDEX (RDB$FOREIGN22),/* (W)Items.PCategory -> (C)PCategories.ID
>*/
> W M INDEX (RDB$PRIMARY12) /*
>(W)Items.Manufacturer->(M)Manufacturers.ID, M
>is a huge table */

OK Andrew,
your plans explain a lot. This has nothing to do with your subselects -
it's all about your ORDER BY (I think). The slow plan choose to go through
the Manufacturers table and then look up the other tables, whereas the fast
plan choose to start with the PCategories table which is much smaller and
hence - faster. I'm not deep into Firebird and cannot tell why it choose
the bad plan, I'm the kind of guy who avoids pitfalls rather than examine
them.

I think you could gain some speed by changing your select to
select ITEM_DESCRIPTION || ' ' as OrderByItem, * from Category_Related
where (
PCategory in ( /* Current node and immediate children, levles 0 and
1 */
select PC.ID from PCategories PC
where PC.ID = :CatgryID or PC.ParentID = :CatgryID)
or PCategory in ( /* Grandchildren, level 2 */
select PC.ID from PCategories PC, PCategories PC2
where PC.ParentID = PC2.ID and PC2.ParentID = :CatgryID)
or PCategory in ( /*Grand-grandchildren, level 3 */
select PC.ID from PCategories PC, PCategories PC2, PCategories PC3
where PC.ParentID = PC2.ID and PC2.ParentID = PC3.ID
and PC3.parentid = :CatgryID)
)
order by 1;

I am not certain that * is allowed when specifying a field, so you may have
to list all the other fields as well. My hope is that adding a space to
ITEM_DESCRIPTION will prevent using any indexes for the ORDER BY hopefully
making things speed up.

If Category_related contains an order by that may be fooling me, but from
your description it doesn't look like it does.

HTH,
Set