I have the slowness issue with my Query; I have searched the forum and web and have not found any explanation or solutions.
The web tool (Java based) that we are developing is using the below query to get the 1000 records at a time and when the user clicks a button the next 1000 records would show up. So, the below query could be executed many times, and every time the query runs it takes about 49 seconds. 49 Seconds traditionally is quite fast, but from a user experience it is very slow. Can you please let me know:
1. If I could use any other function to do the same work, basically to get Top 1000 rows, followed by next 1000 rows etc?
2. Why is this query taking such a long time? If I remove “qualify Row_number() Over (Order by SLSTY) between 1000 and 2000” statement and replace it with “Order by SLSTY” statement, it runs pretty fast (less than 10 secs).
Any solution for my issue would be very helpful.
More Information: Column SLS_TY is basically sales information, so I want the sales to be sorted from highest to lowest, and then a query that would return first 1000 records and then next 1000 records (which will be the same query just the range would change from 0 to 1000 to 1001 to 2000)
qualify Row_number() Over (Order by SLSTY) between 0 and 1000
Why not keep the session open and just fetch the next 1000 when the user hits the button? Then the query doesn't have to be executed over and over.
The Rownumber function is where the cost is, we have to take the entire result set, sort it and then number it across all the AMPs to get a consistent rownumber. Then another pass to get the 1000 that you have asked for.
Please refer to my blog post on JDBC ResultSet pagination.
My blog post refers to the approach that you are using as the "Stateless Model", and discusses the pros and cons of that model.
You may want to consider switching to the "Stateful Model" instead, which is also described in my blog post.
Thank you for the replies, i have gone through them and i think i should be able to build a solution for it on the Java side. I will keep you posted on the progress. Thanks