I have a select query that returns 500000 rows. I need to display 100 rows per page. When the user navigates to next page, I need to display the rows between 100 and 200 and so on. I am using DB2 v10 and java.
My query is taking long time to fetch the data even though I am fetching 100 rows at a time because of many order by clauses in the query. I am using SQL Pagination approach. Could you please let me know the best approach to do this for better performance?