[
https://issues.apache.org/jira/browse/DERBY-7096?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
JackLi0812 updated DERBY-7096:
------------------------------
Description:
> When I use 'fetch first n rows only' in A cascading query, performance drops
>dramatically
```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
```
was:
> When I use 'fetch first n rows only' in A cascading query, performance drops
>dramatically
``` 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
```
> 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.11.1.1, 10.14.2.0
> Environment: windows 10.
> org.apache.derby:derby:10.11.1.1
> java
> Reporter: JackLi0812
> Priority: Major
>
> > When I use 'fetch first n rows only' in A cascading query, performance
> >drops dramatically
> ```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
>
> ```
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
