Re: Paging and SQL queries

From: Aristedes Maniatis (ar..aniatis.org)
Date: Sun May 27 2007 - 07:09:42 EDT

  • Next message: Andrus Adamchik: "Re: Paging and SQL queries"

    On 25/05/2007, at 7:45 PM, Andrus Adamchik wrote:

    > The default strategy, as implemented by
    > org.apache.cayenne.access.IncrementalFaultList, is to run a full
    > query to fully resolve page #1, but only read id columns from the
    > result set for pages 2, 3, etc... This strategy bets that a single
    > fat SQL query with full ResultSet fetching, but without reading
    > unneeded columns is faster than a PK-only query followed by a
    > second query to resolve page #1.

    I don't quite understand what 'resolvesFirstPage()' refers to. Does
    it mean 'firstPageResolved()'? Should it be negated here:

                        // resolve first page if we can
                         if (resolvesFirstPage()) {
                             // read first page completely, the rest as
    ObjectIds
                             for (int i = 0; i < pageSize && it.hasNextRow
    (); i++) {
                                 elements.add(it.nextDataRow());
                                 lastResolved++;
                             }

                             // defer DataRows -> Objects conversion till
    we are completely done.
                         }

                         // continue reading ids
                         DbEntity entity = rootEntity.getDbEntity();
                         while (it.hasNextRow()) {
                             elements.add(it.nextObjectId(entity));
                         }

    >
    > In some circumstances this is true, in some (like yours) it is
    > clearly not. I am leaning towards making a second strategy the
    > default, as paginated queries are really intended for the huge
    > result sets... Anybody has other thoughts on that?

    Without a LIMIT on the records which are fetched as a 'fat query'
    there is little point in paging at all I think. At any rate it is
    certainly a problem in our workload of only 60,000 records.

    Ari

    -------------------------->
    Aristedes Maniatis
    phone +61 2 9660 9700
    PGP fingerprint 08 57 20 4B 80 69 59 E2 A9 BF 2D 48 C2 20 0C C8





    This archive was generated by hypermail 2.0.0 : Sun May 27 2007 - 07:10:26 EDT