Hi all.

How about my story?

One of my tables contains about 300 records. The table looks like:

create table PCategories
(id smallint not null primary key,
name varchar(30),
Name varchar (64) NOT NULL,
Unit_ID smallint NOT NULL references Units (ID), /* "Units" is a small table. It
contains 10-15 small records */
ParentID smallint references PCategories(ID), /* Tree structure */
Hidden char(1)
);

My query runs slowly. The query looks like:

select * 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 Item_Description;

Parameter "CatgryID" is passed as a starting node.

An odd thing happens when I've just added a sting field (varchar(255)) to "PCategories":
that query runs faster about 10 times.
What happened? I can't explain that. I even dont make any reference to that field, just
define it. Removing that field kills performance again. Any ideas?

BTW: FirebirdSS-1.0.0.796-0.64IO.i386.rpm under Red Hat Linux 7.2.

Thanks ahead

Andrew