Uploaded image for project: 'Cayenne'
  1. Cayenne
  2. CAY-1681

Third prefetch kind - DISJOINT_BY_ID

Attach filesAttach ScreenshotVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Task
    • Status: Closed
    • Major
    • Resolution: Fixed
    • None
    • 3.1B1
    • Core Library
    • None

    Description

      (here is a mailing list thread discussing the issue: http://markmail.org/message/zzyd26ucfwhnacfe )

      I keep encountering a common scenario where neither JOINT or DISJOINT prefetch strategies are adequate - queries with fetch limit. It is very common in the application to display X most recent entries from a table with millions of rows, and then drill down to the object details. E.g. assume 2 entities - "Order" and "LineItem", with orders having multiple line items. We want 10 most recent orders, with line items prefetched, so you'd so something like this:

      SelectQuery q = new SelectQuery(Order.class);
      q.addPrefetch("lineItems");
      q.setFetchLimit(10);

      "Disjoint" prefetch in this situation would fetch 10 orders and ALL LineItems in DB.

      "Joint" prefetch will fetch anywhere between 1 and 10 orders, depending on how many line items the first 10 orders have, i.e. fetch limit is applied to to-many join, not to the query root. And this is certainly not what we want.

      Now Cayenne already has something that can solve the problem:

      q.setPageSize(10); // same as fetch limit

      Paginated query is the most optimal way to prefetch here. Whenever a result list is accessed, Cayenne would execute 2 IN () queries - one for the Orders, another one - for the LineItems. Both queries are matching on a set of Order PKs and are pretty efficient, and only return the objects that we care about.

      The problem with this solution is that it is counterintuitive to the user (why should I set "pageSize" to make my prefetches work) and adds one extra query (the IN query resolving the root object list). Would be cool to turn it into a separate type of prefetch. Something like "disjoint by id"?

      Attachments

        1. CAY-1681-v2.patch
          52 kB
          Andrei Veprev
        2. CAY-1681-v3.patch
          13 kB
          Andrei Veprev
        3. CAY-1681-v4.patch
          10 kB
          Andrei Veprev
        4. CAY-1681-v5.patch
          12 kB
          Andrei Veprev
        5. CAY-1681-varchar-length.patch
          0.7 kB
          Andrei Veprev

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            andrus Andrus Adamchik
            andrus Andrus Adamchik
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment