[ https://issues.apache.org/jira/browse/DERBY-7096?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
JackLi0812 updated DERBY-7096: ------------------------------ Issue & fix info: (was: High Value Fix) > 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 > > 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 > > ``` -- This message was sent by Atlassian Jira (v8.3.4#803005)