Prefetching and paginated queries

From: Derek Rendall (dere..sure.com)
Date: Tue Oct 26 2004 - 22:23:27 EDT

  • Next message: Tore Halset: "Re: Prefetching and paginated queries"

    Hi

    I noticed that prefetching doesnt work for paginated queries. I guess this
    makes sense - I cant think how to get it going nicely.

    There are only two possibilities that I can think of:

    (1) creating a temporry table containing the results for one page, and
    joining on that for the prefetched relationship - sounds really dodgy to do
    in a cross DB manner

    (2) grabbing all the ids for the page and then doing a select on the
    relationship using an IN qualifier or similar. The problem would be that one
    would need to be careful to make sure that the number of parameters didnt
    get too big for databases to handle (e.g. our db2 is set up to have a limit
    of 250 parameters). This could be achieved by putting a "max prefetching
    capable limit" on the page size of 200 (may be too restrictive?), or split
    the select into batches of queries based on a maximum number of parameters
    to the in statement (say 200, or configurable as a parameter in modeler?) -
    we use the second option in our (to be replaced) custom data layer and it
    seems to work reasonably well.

    Neither sounds nice nor easy. I can live with use of page size meaning that
    prefecthing doesnt work, but I would like at least a Warning logged if one
    sets a page size on a query with a prefetch or sets a prefetch on a query
    with a page size. That way I get some feedback that things are not going to
    work quite how I (may have) expected.

    Unless someone has another alternative and/or an easier way to get this
    going?

    Thanks

    Derek

    This archive was generated by hypermail 2.0.0 : Tue Oct 26 2004 - 22:23:32 EDT