Topic
2 replies Latest Post - ‏2013-01-05T09:08:42Z by SystemAdmin
p_vijay_19
p_vijay_19
1 Post
ACCEPTED ANSWER

Pinned topic Help needed in selecting a range of records from query (custom pagination)

‏2012-09-07T05:12:13Z |
Hi,

I want to implement a feature of custom pagination/returning query results in a paged manner (by using two input values - 'pageno' and 'pagesize'). For this I plan to use the ROW_NUMBER function avalable in DB2.

But to fetch a page of results (e.g. a set of 1000 records) ROW_NUMBER function will be applied to all the rows in the original query (assuming it returns 1 million rows), then I'll write a where clause to select 1000 records.

I am worried since the ROW_NUMBER function would be computed 1 million times which will affect the performance badly. Please let know if there any way to optimize this.

Also if you could share any suggestions/workarounds it will be of great help.
Thanks,
Vijay Prasad
Updated on 2013-01-05T09:08:42Z at 2013-01-05T09:08:42Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    3105 Posts
    ACCEPTED ANSWER

    Re: Help needed in selecting a range of records from query (custom pagination)

    ‏2012-09-08T01:31:15Z  in response to p_vijay_19
    Vijay, You might look into cursors. You don't have to fetch just one row. You can do a multi-row fetch into an array. If you only need to page forward, a normal cursor will work. If you need to page forward and backward, you can use a scrollable cursor (dynamic or static).
  • SystemAdmin
    SystemAdmin
    3105 Posts
    ACCEPTED ANSWER

    Re: Help needed in selecting a range of records from query (custom pagination)

    ‏2013-01-05T09:08:42Z  in response to p_vijay_19
    it depends on your implementation
    if you are running cics transactions, cursors might be the solution
    if you are running some sort of web application you might not want to have cursors, but rather have a more stateless program
    in that case, you can use the row_number function without too much concern regarding performance as long as you order by a sequence that has an index (so no actual sort is needed), so if you order by col1,col2 just make sure you have an index on col1,col2