[jira] Commented: (DERBY-3505) Current implementation of ROW_NUMBER() window function does not stop execution once criteria is met
[ https://issues.apache.org/jira/browse/DERBY-3505?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12784368#action_12784368 ] Dag H. Wanvik commented on DERBY-3505: -- I should mention that DERBY now supports FETCH NEXT/OFFSET which would typically provide what Paul is looking for. See also DERBY-4397/DERBY-4398. > Current implementation of ROW_NUMBER() window function does not stop > execution once criteria is met > --- > > Key: DERBY-3505 > URL: https://issues.apache.org/jira/browse/DERBY-3505 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.4.1.3 >Reporter: Thomas Nielsen > > Using ROW_NUMBER() to limit the number of rows returned is typically done > with the following query: > SELECT * FROM ( > SELECT row_number() over () as r, t.* FROM T > ) AS tmp WHERE r <= 3; > The query plan shows that the restriction is perfomed in the outermost > ProjectRestrictResultSet, and that it actually sees all rows in the table. > *** Project-Restrict ResultSet (1): > Number of opens = 1 > Rows seen = 1280 > Rows filtered = 1277 > restriction = true > In this case all 1280 rows are read from disk, and passed up the ResultSet > chain. 1277 rows are filtered out so that, in the end, we only return 3 rows. > Ideally the execution should stop after pulling only 3 rows through the chain. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3505) Current implementation of ROW_NUMBER() window function does not stop execution once criteria is met
[ https://issues.apache.org/jira/browse/DERBY-3505?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12669532#action_12669532 ] Dag H. Wanvik commented on DERBY-3505: -- In PredicateList#orderUsefulPredicates, restriction predicates are analyzed and possibly converted into start/stop keys for index scans (cf. calls to markStartKey/markStopkey). I looked at an example where we have a table with two ints, i and j with a primary key on i giving rise to an index. The query: 'select * from t where i < 5' converted the literal 5 into a stop key when scanning the index. This technique allows predicates to effectively limit how many rows are read when applicable. It seems we need some similar analysis/logic for converting predicates on ROW_NUMBER to an efficient "LIMIT". This seems independent of whether the underlying window is ordered or not; the logic would be applied the language level, not at the store level. > Current implementation of ROW_NUMBER() window function does not stop > execution once criteria is met > --- > > Key: DERBY-3505 > URL: https://issues.apache.org/jira/browse/DERBY-3505 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.4.1.3 >Reporter: Thomas Nielsen > > Using ROW_NUMBER() to limit the number of rows returned is typically done > with the following query: > SELECT * FROM ( > SELECT row_number() over () as r, t.* FROM T > ) AS tmp WHERE r <= 3; > The query plan shows that the restriction is perfomed in the outermost > ProjectRestrictResultSet, and that it actually sees all rows in the table. > *** Project-Restrict ResultSet (1): > Number of opens = 1 > Rows seen = 1280 > Rows filtered = 1277 > restriction = true > In this case all 1280 rows are read from disk, and passed up the ResultSet > chain. 1277 rows are filtered out so that, in the end, we only return 3 rows. > Ideally the execution should stop after pulling only 3 rows through the chain. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3505) Current implementation of ROW_NUMBER() window function does not stop execution once criteria is met
[ https://issues.apache.org/jira/browse/DERBY-3505?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12667856#action_12667856 ] Dag H. Wanvik commented on DERBY-3505: -- Thanks for your feedback, Paul. I agree it would be nice to improve on this; it is on my list of things to investigate. > Current implementation of ROW_NUMBER() window function does not stop > execution once criteria is met > --- > > Key: DERBY-3505 > URL: https://issues.apache.org/jira/browse/DERBY-3505 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.4.1.3 >Reporter: Thomas Nielsen > > Using ROW_NUMBER() to limit the number of rows returned is typically done > with the following query: > SELECT * FROM ( > SELECT row_number() over () as r, t.* FROM T > ) AS tmp WHERE r <= 3; > The query plan shows that the restriction is perfomed in the outermost > ProjectRestrictResultSet, and that it actually sees all rows in the table. > *** Project-Restrict ResultSet (1): > Number of opens = 1 > Rows seen = 1280 > Rows filtered = 1277 > restriction = true > In this case all 1280 rows are read from disk, and passed up the ResultSet > chain. 1277 rows are filtered out so that, in the end, we only return 3 rows. > Ideally the execution should stop after pulling only 3 rows through the chain. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3505) Current implementation of ROW_NUMBER() window function does not stop execution once criteria is met
[ https://issues.apache.org/jira/browse/DERBY-3505?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12667525#action_12667525 ] Paul van der Maas commented on DERBY-3505: -- I would like to recommend that this optimization be made as soon as possible. Without improvements to these types of queries, any application that uses JavaDB and pages database result sets will be extremely slow for any serious data set. I am dealing with a situation right now where paging is required because the data set can grow very large (100,000's). I'm having to look at other DBMS's, specifically because of this problem. Without improvements in this area, web based projects especially, will be crippled when using JavaDB. > Current implementation of ROW_NUMBER() window function does not stop > execution once criteria is met > --- > > Key: DERBY-3505 > URL: https://issues.apache.org/jira/browse/DERBY-3505 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.4.1.3 >Reporter: Thomas Nielsen > > Using ROW_NUMBER() to limit the number of rows returned is typically done > with the following query: > SELECT * FROM ( > SELECT row_number() over () as r, t.* FROM T > ) AS tmp WHERE r <= 3; > The query plan shows that the restriction is perfomed in the outermost > ProjectRestrictResultSet, and that it actually sees all rows in the table. > *** Project-Restrict ResultSet (1): > Number of opens = 1 > Rows seen = 1280 > Rows filtered = 1277 > restriction = true > In this case all 1280 rows are read from disk, and passed up the ResultSet > chain. 1277 rows are filtered out so that, in the end, we only return 3 rows. > Ideally the execution should stop after pulling only 3 rows through the chain. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
