Details
-
Improvement
-
Status: Open
-
Major
-
Resolution: Unresolved
-
10.11.1.1, 10.14.2.0
-
None
-
None
-
windows 10.
org.apache.derby:derby:10.11.1.1
java
-
Normal
-
Performance
-
Important
Description
> When I use 'fetch first n rows only' in A cascading query, performance drops dramatically
> Without FETCH FIRST, it takes only a dozen milliseconds, but using this syntax will result in a query that takes 7min+.
```sql
SELECT *
from (
select * from SA.CUSTOMERS fetch first 50000 rows only) "SA.CUSTOMERS",
(
select * from SA.CONTACTS fetch first 50000 rows only) "SA.CONTACTS",
(
select * from SA.REGIONS fetch first 50000 rows only) "Customer Region",
(
select * from SA.ORDERS fetch first 50000 rows only) "SA.ORDERS",
(
select * from SA.CATEGORIES fetch first 50000 rows only) "SA.CATEGORIES",
(
select * from SA.PRODUCTS fetch first 50000 rows only) "SA.PRODUCTS",
(
select * from SA.ORDER_DETAILS fetch first 50000 rows only) "SA.ORDER_DETAILS",
(
select * from SA.SALES_EMPLOYEES fetch first 50000 rows only) "SA.SALES_EMPLOYEES",
(
select * from SA.SUPPLIERS fetch first 50000 rows only) "SA.SUPPLIERS",
(
select * from SA.REGIONS fetch first 50000 rows only) "Salesperson Region"
where "SA.CONTACTS".CUSTOMER_ID = "SA.CUSTOMERS".CUSTOMER_ID
and "SA.CUSTOMERS".REGION_ID = "Customer Region".REGION_ID
and "SA.ORDERS".CUSTOMER_ID = "SA.CUSTOMERS".CUSTOMER_ID
and "SA.ORDERS".ORDER_ID = "SA.ORDER_DETAILS".ORDER_ID
and "SA.PRODUCTS".CATEGORY_ID = "SA.CATEGORIES".CATEGORY_ID
and "SA.ORDER_DETAILS".PRODUCT_ID = "SA.PRODUCTS".PRODUCT_ID
and "SA.ORDERS".EMPLOYEE_ID = "SA.SALES_EMPLOYEES".EMPLOYEE_ID
and "SA.PRODUCTS".SUPPLIER_ID = "SA.SUPPLIERS".SUPPLIER_ID
and "SA.SALES_EMPLOYEES".REGION_ID = "Salesperson Region".REGION_ID
```