I'm not exactly sure what you mean, you may need to describe your usage scenario more. Are you dealing with the same resultset for every page or do you have to issue a new statement for each page load?
Issuing a new statement is typical and the usual approach is to use the limit clause with an offset. However each time you go after a different page of results this way that may involve reexecuting what ammounts to the whole query - unless you are using materialization.
Another approach would be to use a cached query result, see resultset caching in the caching guide. Then page over the results on the client side, by using for example the ResultSet.absolute method. Since the result is cached this should not reexecute the query for each statement, but I can see by the code it is not as efficient as it should be - since the client is still linearly reading the results rather than just skipping to what is needed. I'll log an issue on that.
Thank you, Steven.
I'd like to clarify my situation.
If there is a virtual table which contains 1,000,000 rows, it is not acceptable to retrieve all the rows to client. So the cached query result may not work here.
A valid way needs to be provided to client so that each time he can get next 50 rows instead of all.
So am I correct that you are using a new statement/connection each time to get a different page? If not and you are using the same scroll insensitive resultset, then it will scroll effectively (although it will still initially read the results linearly).
In any case I have logged https://issues.jboss.org/browse/TEIID-1421 to cover better scrolling over cached results.
It seems like there may also be a case to be made for a caching directive that applies the limit clause on top of the cached results rather than making each page a separate cache entry.