--- In firebird-support@yahoogroups.com, "rjschappe" <rschappe@i...>
wrote:

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

Raymond, let me answer in descending order.
Why. Because FB serves IN (Select ...) scanning table in main select
and performing subquery. Seemingly, MS SQL perform subselect once and
perhaps use indices on main table if it is possible.
How.

select LO.Color, LO.Width, LO.LamLength
from LamOrder LO join OrderItem OI on LO.CustOrder=OI.CustOrder
join LamColor LC on OI.Color = LC.ID
where (OI.ItemFlags=1 or OI.ItemFlags=3)
and LC.LamMaker=4

Best regards,
Alexander