Thanks for the reply, Allen. After shooting off the email to
the FB Support
Group, I just had the same thought and I tried the same exercise
with the
ORDER BY field indexed. However it yields the same result, i.e.
there is no
significant change in the time taken.

Anand Kashelkar
Mumbai

----- Original Message -----

Message: 5
Date: Tue, 3 Feb 2004 20:59:41 +1100
From: "Alan McDonald" <alan@...>
Subject: RE: Optimize this query ...

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

Withou an ORDER BY clause you are retrieving the records in
natural order.
That's as fast as it can be. With and ORDER by clause, unless
you have an
index placed on the field in question, you will be asking the
server to
retrieve in natural order then sort the records.
Do you have an index on the field in question?
Alan




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