iBatis for Java [READ ONLY]
  1. iBatis for Java [READ ONLY]
  2. IBATIS-274

queryForPaginatedList doesn't play well with groupBy attribute of resultMap (N+1 selects solution)

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Won't Fix
    • Affects Version/s: 2.1.7
    • Fix Version/s: None
    • Component/s: Core
    • Labels:
      None
    • Environment:
      WinXP Pro, WebLogic 8.1, JDK 1.4.2_05, Spring 1.2.7

      Description

      I have implemented the N+1 solution using the groupBy attribute of the resultMap.

      When this is called using queryForList() it works as expected; I get two BatchResults objects, the first contains a list of 20 ProcessingResult objects and the second contains a list of 19 ProcessingResult objects.

      When called with queryForPaginatedList() and a page size of 10, I only get the first BatchResults object, which does contain the list of 20 ProcessingResult objects.

      When called with queryForPaginatedList() and a page size of 19, I only get both BatchResults objects, the first contains a list of 20 ProcessingResult objects and the second contains a list of 18 ProcessingResult objects. (total of 38, 2x the page size)

      When called with queryForPaginatedList() and a page size of 18, I only get both BatchResults objects, the first contains a list of 20 ProcessingResult objects and the second contains a list of 16 ProcessingResult objects. (total of 36, 2x the page size)

      When called with queryForPaginatedList() and a page size of 17, I only get both BatchResults objects, the first contains a list of 20 ProcessingResult objects and the second contains a list of 14 ProcessingResult objects. (total of 34, 2x the page size)

      See details of mapping and method calls below:

      <resultMap id="BatchResultMap" class="BatchResult" groupBy="processingSchedule.processingKey">
      <result property="processingSchedule.processingKey" column="PROCESSING_KEY" javaType="int"/>
      <result property="processingSchedule.processingDate" column="PROCESS_DATE" javaType="date"/>
      <result property="processingSchedule.printDate" column="PRINT_DATE" javaType="date"/>
      <result property="processingSchedule.processType.processTypeKey" column="PROCESS_TYPE_KEY" javaType="int"/>
      <result property="processingSchedule.processType.description" column="PROCESS_TYPE_DESCRIPTION" javaType="string"/>
      <result property="processingResults" resultMap="Processing.ProcessResultMap"/>
      </resultMap>

      <resultMap id="ProcessResultMap" class="ProcessingResult">
      <!-<result property="processingKey" column="PR_PROCESSING_KEY"/>->
      <result property="applicationKey" column="APPLICATION_KEY" javaType="int"/>
      <result property="applicationStatusKey" column="APPLICATION_STATUS_KEY" javaType="int"/>
      <result property="applicationStatusString" column="APPLICATION_STATUS_DESCRIPTION" javaType="string"/>
      <result property="testResults" column="

      {applicationKey=APPLICATION_KEY, processingKey=PR_PROCESSING_KEY}

      "
      select="getTestResultsForProcessingResult"/>
      <result property="application" column="APPLICATION_KEY" javaType="int"
      select="loadApplicationByPrimaryKey"/>
      <result property="processing" column="PR_PROCESSING_KEY" javaType="int"
      select="loadProcessingSchedule"/>
      </resultMap>
      <select id="loadBatchResultsForDateRange" parameterClass="DateRange" resultMap="BatchResultMap">
      select
      PRO.PROCESSING_KEY,
      PRO.PROCESS_DATE,
      PRO.PRINT_DATE,
      PRO.PROCESS_TYPE_KEY,
      PT.DESCRIPTION as PROCESS_TYPE_DESCRIPTION,
      PR.APPLICATION_KEY,
      ASCD.APPLICATION_STATUS_KEY,
      ASCD.DESCRIPTION as APPLICATION_STATUS_DESCRIPTION,
      PR.PROCESSING_KEY as PR_PROCESSING_KEY
      from
      WM.USTP_PROCESSING PRO
      join WM.USTP_PROCESS_TYPE_CD PT on PRO.PROCESS_TYPE_KEY = PT.PROCESS_TYPE_KEY
      left outer join WM.USTP_PROCESSING_RESULT PR on PR.PROCESSING_KEY = PRO.PROCESSING_KEY
      left outer join WM.USTP_APPLICATION_STATUS_CD ASCD on ASCD.APPLICATION_STATUS_KEY = PR.APPLICATION_STATUS_KEY
      where
      PRO.PROCESS_DATE between #start# and (#end# + 1)
      order by
      PRO.PROCESSING_KEY desc

      </select>

      public List loadByProcessingDateRange(DateRange aDateRange)
      throws DataAccessException

      { return getSqlMapClientTemplate().queryForList("loadBatchResultsForDateRange", aDateRange); }

      public PaginatedList loadByProcessingDateRange(DateRange aDateRange,
      int aPageSize)
      throws DataAccessException

      { return getSqlMapClientTemplate().queryForPaginatedList("loadBatchResultsForDateRange", aDateRange, aPageSize); }

        Activity

        Hide
        Tim Haley added a comment -

        I left out of the Environment section: Oracle 9i using the 10g driver.

        Show
        Tim Haley added a comment - I left out of the Environment section: Oracle 9i using the 10g driver.
        Hide
        Sven Boden added a comment -

        I think that at the moment using queryForPaginatedList together with groupBy is an undefined behaviour. It runs but it's undefined. Using a normal groupBy causes all rows to be read and puts all objects in the right "object bucket" (according to the groupBy), using paginatedList doesn't allow this because not all rows are inspected.

        Show
        Sven Boden added a comment - I think that at the moment using queryForPaginatedList together with groupBy is an undefined behaviour. It runs but it's undefined. Using a normal groupBy causes all rows to be read and puts all objects in the right "object bucket" (according to the groupBy), using paginatedList doesn't allow this because not all rows are inspected.
        Hide
        Dan Syrstad added a comment -

        I'm having the same problem with N+1 selects (groupBy). I believe that the desired behavior would be that N (N=pageSize) top-level objects are returned and each object contains the proper number of nested objects regardless of N. I can't see how any other behavior would be acceptable. The object graph is currently inconsistent depending on the page size, which is a UI setting. How the user wishes to view the objects should not affect the proper construction of the objects. I think Sven's comment about the behavoir being "undefined" may be true simply because there's no javadoc on PaginatedDataList . Also, Sven describes the technical problem, which should be fixed. This feature is exactly what I need, but since it does not work properly with "groupBy", I'll have to revert to queryForList().

        Here's my mapping:

        <?xml version="1.0" encoding="UTF-8" ?>
        <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">
        <sqlMap namespace="Load">

        <resultMap id="LoadMap" class="com.kingcompanies.usps.fast.dao.Load" groupBy="loadNum">
        <result property="loadNum" column="loadNum" />
        <result property="carrierName" column="carrierName" />
        <result property="carrierCode" column="carrierCode" />
        <result property="departureDate" column="departureDate" />
        <result property="trailerNum" column="trailerNum" />
        <result property="grossWeight" column="grossWeight" />
        <result property="stops" resultMap="Load.StopMap" />
        </resultMap>

        <resultMap id="StopMap" class="com.kingcompanies.usps.fast.dao.Stop" groupBy="stopId" >
        <result property="stopId" column="stopId" />
        <result property="stopNum" column="stopNum" />
        <result property="stopDate" column="stopDate" />
        <result property="stopTime" column="stopTime" />
        <result property="consigneeName" column="consigneeName" />
        <result property="consigneeEntryType" column="consigneeEntryType" />
        <result property="shipments" resultMap="Load.ShipmentMap" />
        <result property="appointment.apptNum" column="apptNum" />
        <result property="appointment.apptDateTime" column="apptDateTime" />
        <result property="appointment.apptStatus" column="apptStatus" />
        <result property="appointment.truckArrivedDateTime" column="truckArrivedDateTime" />
        <result property="appointment.unloadingStartedDateTime" column="unloadingStartedDateTime" />
        <result property="appointment.unloadingCompletedDateTime" column="unloadingCompletedDateTime" />
        </resultMap>

        <resultMap id="ShipmentMap" class="com.kingcompanies.usps.fast.dao.Shipment">
        <result property="proNum" column="proNum" />
        <result property="description" column="description" />
        <result property="loadClass" column="loadClass" />
        <result property="mailType" column="mailType" />
        <result property="jobNum" column="jobNum" />
        <result property="jobName" column="jobName" />
        <result property="quantity" column="quantity" />
        <result property="uom" column="uom" />
        <result property="shipmentWeight" column="shipmentWeight" />
        <result property="pieces" column="pieces" />
        <result property="deliveryDate" column="deliveryDate" />
        </resultMap>

        <select id="getLoads"
        parameterClass="com.kingcompanies.usps.fast.dao.LoadQueryParams"
        resultMap="LoadMap">

        SELECT
        Loads.LOAD_NUM loadNum,
        Loads.Carrier_Name carrierName,
        Loads.CARR_CODE carrierCode,
        Loads.Departure_Date departureDate,
        tblAvailablePros.fldTrailerNum trailerNum,
        Loads.Gross_Weight grossWeight,

        tblAvailStopCons.fldLoadStopId stopId,
        tblAvailStopCons.fldStopNum stopNum,
        tblAvailStopCons.fldApptDate stopDate,
        tblAvailStopCons.fldApptTime stopTime,
        Consignees.CONSIGNEE consigneeName,
        Consignees.fldEntryType consigneeEntryType,

        tblAvailablePros.fldProNum proNum,
        tblAvailablePros.fldComments description,
        tblAvailablePros.fldClass loadClass,
        tblAvailablePros.fldMailType mailType,
        tblAvailablePros.fldJobNum jobNum,
        tblAvailablePros.fldJobName jobName,
        tblAvailablePros.fldQuan quantity,
        tblAvailablePros.fldUOM uom,
        tblAvailablePros.fldWeight shipmentWeight,
        tblAvailablePros.fldPieces pieces,
        tblAvailablePros.fldDelDate deliveryDate,

        tblUSPSAppointments.fldApptNum apptNum,
        tblUSPSAppointments.fldApptDateTime apptDateTime,
        tblUSPSAppointments.fldStatus apptStatus,
        tblUSPSAppointments.fldTruckArrived truckArrivedDateTime,
        tblUSPSAppointments.fldUnloadingStarted unloadingStartedDateTime,
        tblUSPSAppointments.fldUnloadingCompleted unloadingCompletedDateTime
        FROM
        Loads
        INNER JOIN tblAvailStopCons
        ON Loads.LOAD_NUM = tblAvailStopCons.fldLoadNum
        LEFT OUTER JOIN tblUSPSAppointments
        ON Loads.LOAD_NUM = tblUSPSAppointments.fldLoadNum
        AND tblAvailStopCons.fldConsId = tblUSPSAppointments.fldConsId
        INNER JOIN tblAvailablePros
        ON Loads.LOAD_NUM = tblAvailablePros.fldLoadNum
        AND tblAvailStopCons.fldStopNum = tblAvailablePros.fldStopNum
        INNER JOIN Consignees
        ON tblAvailStopCons.fldConsId = Consignees.Cons_ID
        WHERE
        (Consignees.fldEntryType = 'SCF'
        OR Consignees.fldEntryType = 'BMC'
        OR Consignees.fldEntryType = 'ASF')
        AND Loads.Gross_Weight BETWEEN #startWeight# AND #endWeight#
        AND Loads.Departure_Date BETWEEN #startDate# AND #endDate#
        AND Loads.CARR_CODE IS NOT NULL
        AND tblAvailStopCons.fldApptDate IS NOT NULL
        AND tblAvailStopCons.fldApptTime IS NOT NULL
        AND (
        (#wantScheduled#=1 AND tblUSPSAppointments.fldApptDateTime IS NOT NULL)
        OR (#wantUnscheduled#=1 AND tblUSPSAppointments.fldApptDateTime IS NULL)
        OR (#wantClosedOut#=1 AND tblUSPSAppointments.fldUnloadingCompleted IS NOT NULL)
        )
        ORDER BY
        Loads.Departure_Date, Loads.LOAD_NUM, tblAvailStopCons.fldStopNum
        </select>
        </sqlMap>

        Show
        Dan Syrstad added a comment - I'm having the same problem with N+1 selects (groupBy). I believe that the desired behavior would be that N (N=pageSize) top-level objects are returned and each object contains the proper number of nested objects regardless of N. I can't see how any other behavior would be acceptable. The object graph is currently inconsistent depending on the page size, which is a UI setting. How the user wishes to view the objects should not affect the proper construction of the objects. I think Sven's comment about the behavoir being "undefined" may be true simply because there's no javadoc on PaginatedDataList . Also, Sven describes the technical problem, which should be fixed. This feature is exactly what I need, but since it does not work properly with "groupBy", I'll have to revert to queryForList(). Here's my mapping: <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd"> <sqlMap namespace="Load"> <resultMap id="LoadMap" class="com.kingcompanies.usps.fast.dao.Load" groupBy="loadNum"> <result property="loadNum" column="loadNum" /> <result property="carrierName" column="carrierName" /> <result property="carrierCode" column="carrierCode" /> <result property="departureDate" column="departureDate" /> <result property="trailerNum" column="trailerNum" /> <result property="grossWeight" column="grossWeight" /> <result property="stops" resultMap="Load.StopMap" /> </resultMap> <resultMap id="StopMap" class="com.kingcompanies.usps.fast.dao.Stop" groupBy="stopId" > <result property="stopId" column="stopId" /> <result property="stopNum" column="stopNum" /> <result property="stopDate" column="stopDate" /> <result property="stopTime" column="stopTime" /> <result property="consigneeName" column="consigneeName" /> <result property="consigneeEntryType" column="consigneeEntryType" /> <result property="shipments" resultMap="Load.ShipmentMap" /> <result property="appointment.apptNum" column="apptNum" /> <result property="appointment.apptDateTime" column="apptDateTime" /> <result property="appointment.apptStatus" column="apptStatus" /> <result property="appointment.truckArrivedDateTime" column="truckArrivedDateTime" /> <result property="appointment.unloadingStartedDateTime" column="unloadingStartedDateTime" /> <result property="appointment.unloadingCompletedDateTime" column="unloadingCompletedDateTime" /> </resultMap> <resultMap id="ShipmentMap" class="com.kingcompanies.usps.fast.dao.Shipment"> <result property="proNum" column="proNum" /> <result property="description" column="description" /> <result property="loadClass" column="loadClass" /> <result property="mailType" column="mailType" /> <result property="jobNum" column="jobNum" /> <result property="jobName" column="jobName" /> <result property="quantity" column="quantity" /> <result property="uom" column="uom" /> <result property="shipmentWeight" column="shipmentWeight" /> <result property="pieces" column="pieces" /> <result property="deliveryDate" column="deliveryDate" /> </resultMap> <select id="getLoads" parameterClass="com.kingcompanies.usps.fast.dao.LoadQueryParams" resultMap="LoadMap"> SELECT Loads.LOAD_NUM loadNum, Loads.Carrier_Name carrierName, Loads.CARR_CODE carrierCode, Loads.Departure_Date departureDate, tblAvailablePros.fldTrailerNum trailerNum, Loads.Gross_Weight grossWeight, tblAvailStopCons.fldLoadStopId stopId, tblAvailStopCons.fldStopNum stopNum, tblAvailStopCons.fldApptDate stopDate, tblAvailStopCons.fldApptTime stopTime, Consignees.CONSIGNEE consigneeName, Consignees.fldEntryType consigneeEntryType, tblAvailablePros.fldProNum proNum, tblAvailablePros.fldComments description, tblAvailablePros.fldClass loadClass, tblAvailablePros.fldMailType mailType, tblAvailablePros.fldJobNum jobNum, tblAvailablePros.fldJobName jobName, tblAvailablePros.fldQuan quantity, tblAvailablePros.fldUOM uom, tblAvailablePros.fldWeight shipmentWeight, tblAvailablePros.fldPieces pieces, tblAvailablePros.fldDelDate deliveryDate, tblUSPSAppointments.fldApptNum apptNum, tblUSPSAppointments.fldApptDateTime apptDateTime, tblUSPSAppointments.fldStatus apptStatus, tblUSPSAppointments.fldTruckArrived truckArrivedDateTime, tblUSPSAppointments.fldUnloadingStarted unloadingStartedDateTime, tblUSPSAppointments.fldUnloadingCompleted unloadingCompletedDateTime FROM Loads INNER JOIN tblAvailStopCons ON Loads.LOAD_NUM = tblAvailStopCons.fldLoadNum LEFT OUTER JOIN tblUSPSAppointments ON Loads.LOAD_NUM = tblUSPSAppointments.fldLoadNum AND tblAvailStopCons.fldConsId = tblUSPSAppointments.fldConsId INNER JOIN tblAvailablePros ON Loads.LOAD_NUM = tblAvailablePros.fldLoadNum AND tblAvailStopCons.fldStopNum = tblAvailablePros.fldStopNum INNER JOIN Consignees ON tblAvailStopCons.fldConsId = Consignees.Cons_ID WHERE (Consignees.fldEntryType = 'SCF' OR Consignees.fldEntryType = 'BMC' OR Consignees.fldEntryType = 'ASF') AND Loads.Gross_Weight BETWEEN #startWeight# AND #endWeight# AND Loads.Departure_Date BETWEEN #startDate# AND #endDate# AND Loads.CARR_CODE IS NOT NULL AND tblAvailStopCons.fldApptDate IS NOT NULL AND tblAvailStopCons.fldApptTime IS NOT NULL AND ( (#wantScheduled#=1 AND tblUSPSAppointments.fldApptDateTime IS NOT NULL) OR (#wantUnscheduled#=1 AND tblUSPSAppointments.fldApptDateTime IS NULL) OR (#wantClosedOut#=1 AND tblUSPSAppointments.fldUnloadingCompleted IS NOT NULL) ) ORDER BY Loads.Departure_Date, Loads.LOAD_NUM, tblAvailStopCons.fldStopNum </select> </sqlMap>
        Hide
        Clinton Begin added a comment -

        Unfortunately this will not be possible to fix. This is a case where an ORM has a significant advantage – it can detect the request and generate two (or more) SQL statements instead of one (which is required).

        There's NO WAY iBATIS can ever predict which rows to read (or not read). To solve this problem, we'd end up having to read all of the rows, which eliminates the value of the range limiting.

        This holds true of specifying min/max values as well.

        In a nutshell, both pagination and join mapping are performance enhancements....but they cannot be used together. So your option is to use either one, but not both. I suggest using lazy loading w/ pagination, or join mapping without pagination.

        Your only other option is to code it yourself to tweak it exactly to your needs.

        PS: I've deemed PaginatedList an anti-pattern, mostly because people are using it too much to solve complex problems. It was meant to be for very simple usages. iBATIS 3 likely will not support it in favor of encouraging using explicit ranges (i.e. offset, and count).

        I must close this issue, simply because it cannot be fixed – by design it's a tradeoff.

        Show
        Clinton Begin added a comment - Unfortunately this will not be possible to fix. This is a case where an ORM has a significant advantage – it can detect the request and generate two (or more) SQL statements instead of one (which is required). There's NO WAY iBATIS can ever predict which rows to read (or not read). To solve this problem, we'd end up having to read all of the rows, which eliminates the value of the range limiting. This holds true of specifying min/max values as well. In a nutshell, both pagination and join mapping are performance enhancements....but they cannot be used together. So your option is to use either one, but not both. I suggest using lazy loading w/ pagination, or join mapping without pagination. Your only other option is to code it yourself to tweak it exactly to your needs. PS: I've deemed PaginatedList an anti-pattern, mostly because people are using it too much to solve complex problems. It was meant to be for very simple usages. iBATIS 3 likely will not support it in favor of encouraging using explicit ranges (i.e. offset, and count). I must close this issue, simply because it cannot be fixed – by design it's a tradeoff.
        Hide
        Tim Haley added a comment -

        Clinton,

        Yes, I had a feeling that the result would be something like this. I was hoping that you would be able to do some magic, but I can see your point.

        My only remaining comment is that you need to document this limitation in both the javadocs for the PaginatedList and the documentation for join mapping.

        Thank you for looking into this.

        Tim Haley

        Show
        Tim Haley added a comment - Clinton, Yes, I had a feeling that the result would be something like this. I was hoping that you would be able to do some magic, but I can see your point. My only remaining comment is that you need to document this limitation in both the javadocs for the PaginatedList and the documentation for join mapping. Thank you for looking into this. Tim Haley

          People

          • Assignee:
            Clinton Begin
            Reporter:
            Tim Haley
          • Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development