You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "JackLi0812 (Jira)" <ji...@apache.org> on 2021/01/07 10:18:00 UTC
[jira] [Updated] (DERBY-7096) performance in `fetch first n rows
only `
[ 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 *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)