HI,

I'm using Elevate 2.05B3 (also tested on 2.05B10)
Note: Assume that all queries below are insensitive and all indexes are in place.

I have around 180K records in Product table. Primary key in Product table is ProductCode.
There are indexes on both ProductCode and ProductName.
The below query takes around 12 seconds to execute

--Query1
SELECT *
FROM 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)
ORDER BY Product.ProductName
RANGE 1 to 3000
JOINOPTIMIZECOSTS

Without the ORDER BY, the same query returns in 0.25 seconds.

--Query2
SELECT *
FROM 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

The result set returned by Query2 is obviously different to Query1.

However, Query3 below appears to return the original result set (from Query 1) and only takes 0.25 seconds

--Query3
SELECT *
FROM
(SELECT *
FROM (SELECT * FROM Product ORDER BY ProductName RANGE 1 TO 2147483647) 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

Why is Elevate unable to optimize Query1 in the same way as Query3? Is this something that can be fixed in future versions of Elevate?

Chris

Don't these produce different result sets?  In the first you are sorting all the records then returning the first 3000.
In the second you are returning the first 3000 records (randomish order) and then sorting them.

John

> --Query1
> SELECT *
> FROM 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)
> ORDER BY Product.ProductName
> RANGE 1 to 3000
> JOINOPTIMIZECOSTS
snip
>
> --Query3
> SELECT *
> FROM
> (SELECT *
> FROM (SELECT * FROM Product ORDER BY ProductName RANGE 1 TO 2147483647) 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
>

John


>Don't these produce different result sets? In the first you are sorting all the records then returning the first 3000.
>In the second you are returning the first 3000 records (randomish order) and then sorting them.

I'd taken Chris at his word but you're almost certainly right and its just data entry order than makes them look the same.

Roy Lambert

I'm certainly right. I should've mentioned this in the first post.
Doing an inner join between the two queries (query 1 and query 3) returns the same number of records (i.e. 3000)

The trick/hack is using the ORDER BY + RANGE within the sub-select

(SELECT * FROM Product ORDER BY ProductName RANGE 1 TO 2147483647) Product

I'm certainly right. I should've mentioned this in the first post.
Doing an inner join between the two queries (query 1 and query 3) returns the same number of records (i.e. 3000)

The trick/hack is using the ORDER BY + RANGE within the sub-select

(SELECT * FROM Product ORDER BY ProductName RANGE 1 TO 2147483647) Product

I'm certainly right. I should've mentioned this in the first post.
Doing an inner join between the two queries (query 1 and query 3) returns the same number of records (i.e. 3000)

The trick/hack is using the ORDER BY + RANGE within the sub-select

(SELECT * FROM Product ORDER BY ProductName RANGE 1 TO 2147483647) Product

Ooops .. sorry about the multiple posts

SELECT *
FROM (SELECT * FROM Product ORDER BY ProductName RANGE 1 TO 2147483647) 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

Above query returns the same 3000 records as query 1 (and query 3), in data entry/insert order.
As above, this is confirmed by inner joining the result of Query 1 to this query on primiary key (i.e. Product.ProductCode). If there are any differences between the results, the join will return less than 3000 records

Chris

> Above query returns the same 3000 records as query 1 (and query 3), in data entry/insert order.
> As above, this is confirmed by inner joining the result of Query 1 to this query on primiary key (i.e.
Product.ProductCode). If there are any differences between the results, the join will return less than 3000 records

I see what you mean.  I had assumed the order by in the sub query was being ignored.  I have a new theory

Simplifying the queries to the following the execution plan shows number of rows visited as table1.recordcount in query
1 and just n in query 2.  It is as if the subquery can take advantage of the index while the "straight" query cannot.
If field1 is not indexed there is no advantage in the subquery syntax.

SELECT * FROM Table1 ORDER BY Field1 RANGE 1 to n

SELECT * FROM
(SELECT * FROM
(SELECT * FROM Table1 ORDER BY Field1 RANGE 1 to 10000000) T1
RANGE 1 to n) T2
ORDER BY Field1

Looks like a candidate for optimization, but in the meantime it looks like you have found a workaround to improve RANGE
queries.

John

ps just for interest sake I tried changing the RANGE to 6 to 8.  It seems to produce the correct result.  The I executed
the subselect ie

SELECT * FROM
(SELECT * FROM Table1 ORDER BY Field1 RANGE 1 to 10000000) T1
RANGE 6 to 8

and it gave a different dataset.  Curious or what!!

This may be related.

http://www.elevatesoft.com/forums?action=view&category=edb&id=edb_sql&page=1&msg=4380#4380

Chris,

<< Without the ORDER BY, the same query returns in 0.25 seconds. >>

How long does it take to execute the query (Query1) without the RANGE ?

--
Tim Young
Elevate Software
www.elevatesoft.com