You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Milosz Tylenda (JIRA)" <ji...@apache.org> on 2008/10/31 19:03:44 UTC
[jira] Created: (OPENJPA-759) Query pagination on DB2 - an
alternative way
Query pagination on DB2 - an alternative way
--------------------------------------------
Key: OPENJPA-759
URL: https://issues.apache.org/jira/browse/OPENJPA-759
Project: OpenJPA
Issue Type: Improvement
Components: sql
Affects Versions: 2.0.0
Environment: DB2 UDB
Reporter: Milosz Tylenda
Priority: Minor
Attachments: OPENJPA-759.patch
The attached patch provides an alternative way of doing paging in DB2.
I made it hoping that it would improve the performance of paging queries
but it didn't, at least not when the database and OpenJPA are co-located.
There is probably no point in committing this patch unless someone
proves it gives any advantages.
I am however presenting it here, maybe some users will find it useful
(it would be nice if someone could check timings with remote database).
Attached are:
- a patch which modifies DB2Dictionary and also adds a few tests to TestQueryPagination.
- a standalone modified DB2Dictionary class.
- a timer program which was supposed to prove the superior performance but failed :)
Some characteristics:
- The idea is to limit the result set returned by SQL query instead of skipping rows
when traversing the result set in OpenJPA. A similar approach exists
in OracleDictionary.
- If setMaxResulsts and setFirstResult were called on Query, the SQL query
[QUERY] is modified as follows:
SELECT * FROM (
SELECT rr.*, ROW_NUMBER() OVER(ORDER BY ORDER OF rr) AS rn FROM (
[QUERY]
FETCH FIRST [m] ROWS ONLY
) AS rr
) AS r WHERE rn > [n] ORDER BY rn
- The modified SQL query adds one column to the end of column list in the
result set. Luckily, I couldn't find any side effects of doing this.
- If only setMaxResults was called on Query, only FETCH FIRST [m] ROWS ONLY
is appended to SQL query - this is how it works currently.
- The new way of paging will be used only if the database is a UDB 8.1 or later
because of ORDER OF construct and FETCH FIRST [m] ROWS ONLY in a subselect.
Maybe some other DB2 flavours could also handle it but I have no access.
- User can fall back to the old behaviour by setting supportsSelectStartIndex
Dictionary property to false.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-759) Query pagination on DB2 - an
alternative way
Posted by "Milosz Tylenda (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-759?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Milosz Tylenda updated OPENJPA-759:
-----------------------------------
Attachment: DB2Dictionary.java
> Query pagination on DB2 - an alternative way
> --------------------------------------------
>
> Key: OPENJPA-759
> URL: https://issues.apache.org/jira/browse/OPENJPA-759
> Project: OpenJPA
> Issue Type: Improvement
> Components: sql
> Affects Versions: 2.0.0
> Environment: DB2 UDB
> Reporter: Milosz Tylenda
> Priority: Minor
> Attachments: DB2Dictionary.java, OPENJPA-759.patch, timer.zip
>
>
> The attached patch provides an alternative way of doing paging in DB2.
> I made it hoping that it would improve the performance of paging queries
> but it didn't, at least not when the database and OpenJPA are co-located.
> There is probably no point in committing this patch unless someone
> proves it gives any advantages.
> I am however presenting it here, maybe some users will find it useful
> (it would be nice if someone could check timings with remote database).
> Attached are:
> - a patch which modifies DB2Dictionary and also adds a few tests to TestQueryPagination.
> - a standalone modified DB2Dictionary class.
> - a timer program which was supposed to prove the superior performance but failed :)
> Some characteristics:
> - The idea is to limit the result set returned by SQL query instead of skipping rows
> when traversing the result set in OpenJPA. A similar approach exists
> in OracleDictionary.
> - If setMaxResulsts and setFirstResult were called on Query, the SQL query
> [QUERY] is modified as follows:
> SELECT * FROM (
> SELECT rr.*, ROW_NUMBER() OVER(ORDER BY ORDER OF rr) AS rn FROM (
> [QUERY]
> FETCH FIRST [m] ROWS ONLY
> ) AS rr
> ) AS r WHERE rn > [n] ORDER BY rn
> - The modified SQL query adds one column to the end of column list in the
> result set. Luckily, I couldn't find any side effects of doing this.
> - If only setMaxResults was called on Query, only FETCH FIRST [m] ROWS ONLY
> is appended to SQL query - this is how it works currently.
> - The new way of paging will be used only if the database is a UDB 8.1 or later
> because of ORDER OF construct and FETCH FIRST [m] ROWS ONLY in a subselect.
> Maybe some other DB2 flavours could also handle it but I have no access.
> - User can fall back to the old behaviour by setting supportsSelectStartIndex
> Dictionary property to false.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-759) Query pagination on DB2 - an
alternative way
Posted by "Milosz Tylenda (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-759?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Milosz Tylenda updated OPENJPA-759:
-----------------------------------
Attachment: OPENJPA-759.patch
> Query pagination on DB2 - an alternative way
> --------------------------------------------
>
> Key: OPENJPA-759
> URL: https://issues.apache.org/jira/browse/OPENJPA-759
> Project: OpenJPA
> Issue Type: Improvement
> Components: sql
> Affects Versions: 2.0.0
> Environment: DB2 UDB
> Reporter: Milosz Tylenda
> Priority: Minor
> Attachments: OPENJPA-759.patch
>
>
> The attached patch provides an alternative way of doing paging in DB2.
> I made it hoping that it would improve the performance of paging queries
> but it didn't, at least not when the database and OpenJPA are co-located.
> There is probably no point in committing this patch unless someone
> proves it gives any advantages.
> I am however presenting it here, maybe some users will find it useful
> (it would be nice if someone could check timings with remote database).
> Attached are:
> - a patch which modifies DB2Dictionary and also adds a few tests to TestQueryPagination.
> - a standalone modified DB2Dictionary class.
> - a timer program which was supposed to prove the superior performance but failed :)
> Some characteristics:
> - The idea is to limit the result set returned by SQL query instead of skipping rows
> when traversing the result set in OpenJPA. A similar approach exists
> in OracleDictionary.
> - If setMaxResulsts and setFirstResult were called on Query, the SQL query
> [QUERY] is modified as follows:
> SELECT * FROM (
> SELECT rr.*, ROW_NUMBER() OVER(ORDER BY ORDER OF rr) AS rn FROM (
> [QUERY]
> FETCH FIRST [m] ROWS ONLY
> ) AS rr
> ) AS r WHERE rn > [n] ORDER BY rn
> - The modified SQL query adds one column to the end of column list in the
> result set. Luckily, I couldn't find any side effects of doing this.
> - If only setMaxResults was called on Query, only FETCH FIRST [m] ROWS ONLY
> is appended to SQL query - this is how it works currently.
> - The new way of paging will be used only if the database is a UDB 8.1 or later
> because of ORDER OF construct and FETCH FIRST [m] ROWS ONLY in a subselect.
> Maybe some other DB2 flavours could also handle it but I have no access.
> - User can fall back to the old behaviour by setting supportsSelectStartIndex
> Dictionary property to false.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-759) Query pagination on DB2 - an
alternative way
Posted by "Milosz Tylenda (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-759?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Milosz Tylenda updated OPENJPA-759:
-----------------------------------
Attachment: timer.zip
> Query pagination on DB2 - an alternative way
> --------------------------------------------
>
> Key: OPENJPA-759
> URL: https://issues.apache.org/jira/browse/OPENJPA-759
> Project: OpenJPA
> Issue Type: Improvement
> Components: sql
> Affects Versions: 2.0.0
> Environment: DB2 UDB
> Reporter: Milosz Tylenda
> Priority: Minor
> Attachments: DB2Dictionary.java, OPENJPA-759.patch, timer.zip
>
>
> The attached patch provides an alternative way of doing paging in DB2.
> I made it hoping that it would improve the performance of paging queries
> but it didn't, at least not when the database and OpenJPA are co-located.
> There is probably no point in committing this patch unless someone
> proves it gives any advantages.
> I am however presenting it here, maybe some users will find it useful
> (it would be nice if someone could check timings with remote database).
> Attached are:
> - a patch which modifies DB2Dictionary and also adds a few tests to TestQueryPagination.
> - a standalone modified DB2Dictionary class.
> - a timer program which was supposed to prove the superior performance but failed :)
> Some characteristics:
> - The idea is to limit the result set returned by SQL query instead of skipping rows
> when traversing the result set in OpenJPA. A similar approach exists
> in OracleDictionary.
> - If setMaxResulsts and setFirstResult were called on Query, the SQL query
> [QUERY] is modified as follows:
> SELECT * FROM (
> SELECT rr.*, ROW_NUMBER() OVER(ORDER BY ORDER OF rr) AS rn FROM (
> [QUERY]
> FETCH FIRST [m] ROWS ONLY
> ) AS rr
> ) AS r WHERE rn > [n] ORDER BY rn
> - The modified SQL query adds one column to the end of column list in the
> result set. Luckily, I couldn't find any side effects of doing this.
> - If only setMaxResults was called on Query, only FETCH FIRST [m] ROWS ONLY
> is appended to SQL query - this is how it works currently.
> - The new way of paging will be used only if the database is a UDB 8.1 or later
> because of ORDER OF construct and FETCH FIRST [m] ROWS ONLY in a subselect.
> Maybe some other DB2 flavours could also handle it but I have no access.
> - User can fall back to the old behaviour by setting supportsSelectStartIndex
> Dictionary property to false.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.