5 Replies Latest reply: Jan 5, 2011 11:18 AM by Steven Hawkins RSS

How to separate result into pages using Teiid

jalen zhong Newbie

We are trying to separate the search result of VDB into different pages since if there are many rows, performance is not accepted.

 

Is there anyway to use Teiid to separate result into different pages? Or the client side of Teiid (the SQL executor) must implement this logic itself?

  • 1. Re: How to separate result into pages using Teiid
    Steven Hawkins Master

    Jalen,

     

    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.

     

    Steve

  • 2. Re: How to separate result into pages using Teiid
    jalen zhong Newbie

    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.

  • 3. Re: How to separate result into pages using Teiid
    Steven Hawkins Master

    Hi Jalen,

     

    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.

     

    Steve

  • 4. Re: How to separate result into pages using Teiid
    jalen zhong Newbie

    Hi Steven,

     

    Yes, we are using a new statement to get different pages. We don't use the cached results because it will retrieve all the data to client at the first time which will be a performance issue. Please correct me if I am wrong.

     

    Thanks again for your help.

     

    Jalen

  • 5. Re: How to separate result into pages using Teiid
    Steven Hawkins Master

    Jalen,

     

    There is a minor amount of batch caching (max of 3 batches using lru) on the client side when using scroll insensitive results, but full resultset caching is the job of the server.  The cached results will be used by clients as if they were the real results on demand.

     

    Steve