I have what I thought was a fairly simple query, it runs in less than
5 seconds on a very old MS SQL Server 7 box with very little RAM but
it is _still_ running (30 mins) on my Firebird 1.5 box which is
_much_ better equipt hardware wise.

in MS SQL Server here is the query
==================================
select LamOrder.Color, LamOrder.Width, LamOrder.Length
from LamOrder
where LamOrder.CustOrder in (
select Convert(VARCHAR(10),OrderItem.CustOrder)
from OrderItem join LamColor on (OrderItem.Color = LamColor.ID)
where ((OrderItem.ItemFlags=1) or (OrderItem.ItemFlags=3))
and LamColor.LamMaker=4
)

Notes:
1. In firebird, I had to change the name of LamOrder.Length to
LamOrder.LamLength
2. Also, I did not have to convert the ID to string in the FB
database because all IDs are string
3. The LamOrder table has about 500,000 records
4. The subset for the "IN" will only return about 200 records -
pretty small!

in FB here is the query
==================================
select LamOrder.Color, LamOrder.Width, LamOrder.LamLength
from LamOrder
where LamOrder.CustOrder in (
select OrderItem.CustOrder
from OrderItem join LamColor on (OrderItem.Color = LamColor.ID)
where ((OrderItem.ItemFlags=1) or (OrderItem.ItemFlags=3))
and LamColor.LamMaker=4
)

I guess there are two questions here... one is how to optimize ths
query, but the other is why is there such a difference between the
same query running under MS SQL Server 7 and Firebird 1.5?

Thanks for any help,
--Raymond