db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Paul van der Maas (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3505) Current implementation of ROW_NUMBER() window function does not stop execution once criteria is met
Date Tue, 27 Jan 2009 00:54:59 GMT

    [ 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

> 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:
>            Reporter: Thomas Nielsen
> Using ROW_NUMBER() to limit the number of rows returned is typically done with the following
>     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.

View raw message