JackLi0812 created DERBY-7096:
---------------------------------
Summary: performance in `fetch first n rows only `
Key: DERBY-7096
URL: https://issues.apache.org/jira/browse/DERBY-7096
Project: Derby
Issue Type: Improvement
Components: SQL
Affects Versions: 10.14.2.0, 10.11.1.1
Environment: windows 10.
org.apache.derby:derby:10.11.1.1
java
Reporter: JackLi0812
> When I use 'fetch first n rows only' in A cascading query, performance drops
>dramatically
``` sql
SELECT *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
```
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
