ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeff Butler" <jeffgbut...@gmail.com>
Subject Re: Need help on nested N + 1 query
Date Mon, 27 Nov 2006 17:57:50 GMT
You should also consider that this is really an (N*N*N)+1 query - the number
of selects sent to the DB grows exponentially as more rows are returned.  I
think it would be worth trying this all in one query and using iBATIS
groupBy solution for dealing with N+1 queries.  You'll have a lot of
redundant data in the result set, but I think it will still perform better
than sending lots of seperate select statements to the DB.  Here's a link to
a discussion about doing multiple nested groupBys:

http://www.mail-archive.com/user-java@ibatis.apache.org/msg06596.html

Jeff Butler



On 11/27/06, Jeynes, Paul [CIB-IT] <paul.jeynes@citigroup.com> wrote:
>
>  Check page 38 of the iBatis SQL Map Developers Guide (2.0).  I think that
> has what you need (complex parameter properties).
>
> -----Original Message-----
> *From:* Tamilselvan Radha Krishnan [mailto:
> Tamilselvan.RadhaKrishnan@igate.com]
> *Sent:* 27 November 2006 17:39
> *To:* user-java@ibatis.apache.org
> *Subject:* Need help on nested N + 1 query
>
> Hi,
>
> I've written a N + 1 query in Ibatis such a way that it will return the
> nested results as below :
>
>    Customer – First object
>
>              DealFacility – An array list of objects within Customer
>
>                      Facility – An array list of objects within
> DealFacility
>
>                         LoanInfo – An array list of objects within
> Facility
>
>
>
> The Customer object is having a 'strCustomerId' as an attribute.  I need
> to use this 'strCustomerId' in the WHERE clause of the query used for
> populating 'LoanInfo'  (<select id="getFacilityLoans"  )  i.e, 3 level
> below the Customer, in addition to 'Facility' class above.
>
> I've placed my Login.xml query below.  Any suggestion would be much
> helpful.
>
>
> <resultMap id="customersResults" class="
> com.ge.cf.myaccounts.domain.Customer">
>
>         <result property="strCustomerId"
> column="CUSTOMER_CODE"/>
>
>         <result property="strCustomerName"
> column="CUSTOMER_NAME"/>
>
>         <result property="alDealFacilities"
> column="CUSTOMER_CODE"          select="Login.getDealFacilities"/>
>
>
>    </resultMap>
>
>
>
>    <select id="getCustomerDetails"  resultMap="customersResults"
> parameterClass="com.ge.cf.myaccounts.domain.Customer">
>
>         SELECT   CUSTOMER_CODE,CUSTOMER_NAME
>
>                 FROM     STG_INTM_CUSTOMER_INFO
>
>                 WHERE    CUSTOMER_CODE=#strCustomerId#
>
>    </select>
>
>
>
>
>
>
>
>         <resultMap id="dealFacilityResults" class="
> com.ge.cf.myaccounts.domain.DealFacility">
>
>
>
>             <result property="strCustomerId"
>             column="CUSTOMER_CODE"/>
>
>             <result property="strDealFacilityId"
>                 column="DEAL_FACILITY_CODE"/>
>
>         <result property="strlType"
>                 column="DEAL_FACILITY_TYPE"/>
>
>         <result property="dobCommitAmount"         nullValue="0"
> column="DEAL_FACILITY_COM_AMOUNT"/>
>
>         <result property="dobBalance"              nullValue="0"
> column="DEAL_FACILITY_BALANCE"/>
>
>         <result property="alFacilities"
> column="DEAL_FACILITY_CODE" select="Login.getFacilities"/>
>
>         </resultMap>
>
>
>
>         <select id="getDealFacilities"  resultMap="dealFacilityResults"
> parameterClass="java.lang.String">
>
>         SELECT
>
>                                         ci.CUSTOMER_CODE,
> cf.DEAL_FACILITY_CODE,cf.DEAL_FACILITY_TYPE,cf.DEAL_FACILITY_COM_AMOUNT,
> cf.DEAL_FACILITY_BALANCE
>
>                 FROM            STG_INTM_CUSTOMER_INFO ci
> ,STG_INTM_DEAL_FACILITY cf
>
>                 WHERE           ci.CUSTOMER_CODE = #strCustomerId#
>
>                 AND         ci.CUSTOMER_CODE = cf.CUSTOMER_CODE
>
>                 AND         cf.IS_DELETED='N'
>
>         </select>
>
>
>
>
>
>      <resultMap id="facilityResults" class="
> com.ge.cf.myaccounts.domain.Facility">
>
>             <result property="strFacilityId"
>                  column="FACILITY_CODE"/>
>
>         <result property="strTypeDescription"
>          column="FACILITY_TYPE_DESC"/>
>
>         <result property="strDescription"
>          column="FACILITY_DESC"/>
>
>         <result property="dobCommitAmount"           nullValue="0"
> column="FACILITY_COM_AMOUNT"/>
>
>         <result property="dobBalance"            nullValue="0"
> column="FACILITY_BALANCE"/>
>
>         <result property="strBbcId"
>          column="BBC_ID"/>
>
>         <result property="strStatus"
>              column="STATUS"/>
>
>         <result property="alLoanInfos"
> column="FACILITY_CODE" select="Login.getFacilityLoans"/>
>
>
>
>
>         </resultMap>
>
>
>
>         <select id="getFacilities"  resultMap="facilityResults"
> parameterClass="java.lang.String">
>
>                 SELECT DISTINCT f.facility_code, f.facility_desc,
> f.facility_type_desc,
>
>                 f.facility_com_amount, f.facility_balance, f.bbc_id,
> d.status
>
>         FROM tmplte_master_status d,
>
>                 (SELECT DISTINCT a.facility_code facility_code,
>
>                                  facility_desc facility_desc,
>
>                                  facility_type_desc facility_type_desc,
>
>                                  facility_com_amount facility_com_amount,
>
>                                  facility_balance facility_balance,
>
>                                  bbc_id bbc_id, cur_status_id
> cur_status_id
>
>                  FROM                    stg_intm_mem_facility a,
>
>                                  stg_intm_deal_facility b,
>
>                                  tmplte_master c
>
>                  WHERE                   a.deal_facility_code =
> #strDealFacilityId#
>
>                  AND                     a.facility_code = c.facility_code
> (+)
>
>                  AND                     a.is_deleted = 'N') f
>
>         WHERE d.status_id(+) = f.cur_status_id
>
>         </select>
>
>
>
>
>
>         <resultMap id="facilityLoanResults" class="
> com.ge.cf.myaccounts.domain.LoanInfo">
>
>         <result property="strLoanId"
>         column="LOAN_CODE"/>
>
>         <result property="strLoanIntRateType"
>         column="LOAN_INT_RATE_TYPE"/>
>
>         <result property="dobBalance"        nullValue="0"
> column="LOAN_BALANCE"/>
>
>         <result property="strSectionId"
>                 column="SECTION_CD"/>
>
>         <result property="strLoanProductGroup"
>         column="LOAN_PRODUCT_GROUP"/>
>
>         </resultMap>
>
>
>
>         <select id="getFacilityLoans"  resultMap="facilityLoanResults"
> parameterClass="java.lang.String">
>
>                 SELECT  LOAN_CODE, LOAN_INT_RATE_TYPE, LOAN_BALANCE ,
> SECTION_CD, LOAN_PRODUCT_GROUP
>
>                 FROM    STG_INTM_LOAN_INFO
>
>                 WHERE   FACILITY_CODE=#strFacilityId#
>
>                 AND     trim(SECTION_CD) IS NULL
>
>                 AND     IS_DELETED='N'
>
>                 ORDER BY LOAN_CODE
>
>     </select>
>
>
>
>
>
>
>
>
>
> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
>
>
> Information transmitted by this EMAIL is proprietary to iGATE Group of Companies and
is intended for use only by the individual
>
> or entity to whom it is addressed and may contain information that is privileged, confidential,
or exempt from disclosure under
>
> applicable law. If you are not the intended recipient of this EMAIL immediately notify
the sender at iGATE or
> mailadmin@igate.com
> and delete this EMAIL including any attachments
>
> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
>
>

Mime
View raw message