Chris,

Also, what happens when you remove the Product derived table (RANGE 1 TO
2147483647) from Query3 ?  Does the performance stay the same ?

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" wrote:
>> How long does it take to execute the query (Query1) without the RANGE ?

Just as long as with the RANGE ... around 12 seconds.


>>Also, what happens when you remove the Product derived table (RANGE 1 TO
2147483647) from Query3 ?  Does the performance stay the same ?

Performance remains the same (0.25 seconds). However, the results are now different from query 1.
Removing the RANGE (while keeping ORDER BY) has the same result (ie. performance is the same, resultset is different).

SELECT *
FROM
(SELECT *
FROM (SELECT * FROM Product ORDER BY ProductName
--RANGE 1 TO 2147483647 --commented out
) Product
INNER JOIN PricingPolicy ON Product.PricePolicyCategoryID = PricingPolicy.CategoryID
INNER JOIN Category ON Product.PricePolicyCategoryID = Category.CategoryID
WHERE (Category.CategoryCode = 1 AND Product.DiscontinuedFlag = False)
RANGE 1 to 3000
JOINOPTIMIZECOSTS) TBL1
ORDER BY ProductName

Chris,

<< Just as long as with the RANGE ... around 12 seconds. >>

Okay, that's the reason for the execution time - in the first query EDB has
to execute the entire query to satisfy the ORDER BY, and *then* it can apply
the range.  When there isn't an ORDER BY, EDB only needs to execute the
query enough to generate enough rows to satisfy the RANGE, i.e. it can
generate an incremental result set.

And yes, EDB could, theoretically, rewrite the first query so that it looks
like the derived version.  It's not particularly difficult, and hinges on
ordering the involved table before the query execution.  The problem with
this approach, however, is that in certain circumstances this could be very
slow, such as if there were further filters on the table being ordered, and
this may not be something that EDB can determine ahead of time.  So, it will
have to pick one way or the other, and will probably require an additional
keyword so that you can tell the optimizer not to do it, if it is slow.

I'll see about adding this in an upcoming build.

--
Tim Young
Elevate Software
www.elevatesoft.com