[
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.
