One of my queries runs normally. But as soon as I add an ORDER
BY clause to
it, it slows down considerably. I am only selecting first n
records from a
view.

SELECT FIRST n SKIP m * FROM VU1
WHERE
Field1 = Value1
AND Field2 IN
(SELECT Field2 FROM AnotherTable) -->
AND (Field3 IS NULL OR Field3 = ''
AND Field4 IN
(SELECT Field4 FROM YetAnotherTable
WHERE SomeField = SomeValue AND SomeOtherField IS NULL)

ORDER BY Field6


As it is the query runs alright. The view VU1 uses a JOIN of
two tables. I
can't add ORDER BY to the view's definition because I think
ORDER BY is not
allowed in the view.

I entered the same query into the IBExpert SQL Window and
without an ORDER
BY clause, the query was executed in a little over than 3
seconds. However,
after adding the ORDER BY clause the query took a little over
than a MINUTE
to process.

On another note, when I tried to convert the condition marked
with "-->"
into a JOIN, even then it slows down quite a bit.


Any suggestions on why it's taking too much time? Any tips to
optimize it?


__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/