Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2013
    Posts
    1

    Unanswered: Count query taking too long to execute

    I have 2 SQL queries. First gets me the count of the pending dealer applications needing approval of a particular user and second gets me the details of the pending dealer applications needing approval of a particular user.

    Count Query:
    select count(mdealerapp0_.SEQ_DEALER) as col_0_0_
    from M_DEALER_APPLY mdealerapp0_
    where (exists (
    select mdealerapp1_.SEQ_DEALER
    from M_DEALER_APPLY mdealerapp1_, M_USER_BRANCH muserbranc2_, M_BRANCH msalesgrp3_, VIEW_SUB_BRANCH msubbranch4_
    where mdealerapp1_.CD_SALES_GRP=msalesgrp3_.CD_SALES_GRP
    and msalesgrp3_.CD_SUB_BRANCH=msubbranch4_.CD_SUB_BRAN CH
    and 1=1
    and msubbranch4_.CD_BRANCH=muserbranc2_.CD_BRANCH
    and muserbranc2_.USER_ID='200009'
    and muserbranc2_.CD_BIZ='2'
    and mdealerapp1_.SEQ_DEALER=mdealerapp0_.SEQ_DEALER)
    or exists (
    select mdealerapp5_.SEQ_DEALER
    from M_DEALER_APPLY mdealerapp5_, M_DEALER mdealer6_, M_USER_BRANCH muserbranc7_, M_BRANCH msalesgrp8_, VIEW_SUB_BRANCH msubbranch9_
    where mdealer6_.CD_SALES_GRP=msalesgrp8_.CD_SALES_GRP
    and msalesgrp8_.CD_SUB_BRANCH=msubbranch9_.CD_SUB_BRAN CH
    and 1=1
    and mdealerapp5_.CD_DEALER_FROM=mdealer6_.CD_DEALER
    and msubbranch9_.CD_BRANCH=muserbranc7_.CD_BRANCH
    and muserbranc7_.USER_ID='200009'
    and muserbranc7_.CD_BIZ='2'
    and mdealerapp5_.SEQ_DEALER=mdealerapp0_.SEQ_DEALER)
    )
    and (exists (
    select tsfeapprov10_.APPR_USERID
    from T_SFE_APPROVAL_IT tsfeapprov10_
    where tsfeapprov10_.APPR_USERID='200009'
    and tsfeapprov10_.CD_BIZ='2'
    and tsfeapprov10_.POS=mdealerapp0_.CUR_POS
    and tsfeapprov10_.SEQ_DELIV=mdealerapp0_.SEQ_DEALER
    and mdealerapp0_.ST_DEALER_APP=4) or mdealerapp0_.CREATE_USER='200009' and mdealerapp0_.CD_CREATE_BIZ='2' and mdealerapp0_.ST_DEALER_APP=1
    )
    and mdealerapp0_.CREATE_USER<>'SFE0201B02' order by mdealerapp0_.SEQ_DEALER;


    Data Query:
    select * from ( select mdealerapp0_.SEQ_DEALER as SEQ1_93_0_, msubsectio1_.CD_SUB_SECTION as CD1_17_1_, mchannel3_.CD_CHANNEL as CD1_11_2_, msalesgrp2_.CD_SALES_GRP as CD1_25_3_, viewalluse4_.USER_ID as USER1_19_4_, mdealerapp0_.ZZ_DT_UPDATED as ZZ2_93_0_, mdealerapp0_.CD_DEALER as CD3_93_0_, mdealerapp0_.NM_DESC as NM4_93_0_, mdealerapp0_.ST_DEALER as ST5_93_0_, mdealerapp0_.PROV_ID as PROV6_93_0_, mdealerapp0_.CITY_ID as CITY7_93_0_, mdealerapp0_.VAT as VAT93_0_, mdealerapp0_.ACCOUNT_BANK as ACCOUNT9_93_0_, mdealerapp0_.DETAIL_BANK as DETAIL10_93_0_, mdealerapp0_.NM_CONTACT as NM11_93_0_, mdealerapp0_.EMAIL as EMAIL93_0_, mdealerapp0_.CD_GRP as CD13_93_0_, mdealerapp0_.PL_TYP as PL14_93_0_, mdealerapp0_.ADDRESS_ACCOUNT as ADDRESS15_93_0_, mdealerapp0_.TEL_ACCOUNT as TEL16_93_0_, mdealerapp0_.POSTCODE_ACCOUNT as POSTCODE17_93_0_, mdealerapp0_.CD_NAS_GRP as CD18_93_0_, mdealerapp0_.VAIO_GRP_CODE as VAIO19_93_0_, mdealerapp0_.CREDIT_A_HIGH as CREDIT20_93_0_, mdealerapp0_.CREDIT_A_LOW as CREDIT21_93_0_, mdealerapp0_.PAY_LIMIT_DAY as PAY22_93_0_, mdealerapp0_.CREDIT_B_HIGH as CREDIT23_93_0_, mdealerapp0_.CREDIT_B_LOW as CREDIT24_93_0_, mdealerapp0_.INVOICE_LT as INVOICE25_93_0_, mdealerapp0_.TRANS_TYPE as TRANS26_93_0_, mdealerapp0_.TRANS_AREA as TRANS27_93_0_, mdealerapp0_.DELIVERY_FAC as DELIVERY28_93_0_, mdealerapp0_.DT_CREATE as DT29_93_0_, mdealerapp0_.CREATE_USER as CREATE30_93_0_, mdealerapp0_.ADDRESS_INV as ADDRESS31_93_0_, mdealerapp0_.TEL_INV as TEL32_93_0_, mdealerapp0_.POSTCODE_INV as POSTCODE33_93_0_, mdealerapp0_.TAX_NO as TAX34_93_0_, mdealerapp0_.SHORT_NAME_CN as SHORT35_93_0_, mdealerapp0_.CD_SUB_SECTION as CD36_93_0_, mdealerapp0_.FLG_CTO as FLG37_93_0_, mdealerapp0_.FLG_DUMMY as FLG38_93_0_, mdealerapp0_.CD_SALES_GRP as CD39_93_0_, mdealerapp0_.TYP_DEALER as TYP40_93_0_, mdealerapp0_.TYP_SELLOUT as TYP41_93_0_, mdealerapp0_.TYP_CREDIT as TYP42_93_0_, mdealerapp0_.FLG_SIX as FLG43_93_0_, mdealerapp0_.FLG_FIXREB as FLG44_93_0_, mdealerapp0_.FLG_IPN as FLG45_93_0_, mdealerapp0_.FLG_IOC as FLG46_93_0_, mdealerapp0_.FLG_IOC_GRP as FLG47_93_0_, mdealerapp0_.REMARK as REMARK93_0_, mdealerapp0_.FLG_VAIO as FLG49_93_0_, mdealerapp0_.FLG_LOCAL_DEALER as FLG50_93_0_, mdealerapp0_.SALESMAN as SALESMAN93_0_, mdealerapp0_.CD_COMPANY as CD52_93_0_, mdealerapp0_.COUNT_PROV_ID as COUNT53_93_0_, mdealerapp0_.COUNT_CITY_ID as COUNT54_93_0_, mdealerapp0_.FAX_INV as FAX55_93_0_, mdealerapp0_.FLG_CP as FLG56_93_0_, mdealerapp0_.FLG_EDI as FLG57_93_0_, mdealerapp0_.NM_EN_DEALER as NM58_93_0_, mdealerapp0_.NM_EN_SHORT as NM59_93_0_, mdealerapp0_.ID_SAP as ID60_93_0_, mdealerapp0_.CUSTOMER_COND_GRP as CUSTOMER61_93_0_, mdealerapp0_.CD_DEALER_FROM as CD62_93_0_, mdealerapp0_.ZZ_CREATED_USER as ZZ63_93_0_, mdealerapp0_.ZZ_DT_CREATED as ZZ64_93_0_, mdealerapp0_.ZZ_UPDATED_USER as ZZ65_93_0_, mdealerapp0_.ST_DEALER_APP as ST66_93_0_, mdealerapp0_.CUR_POS as CUR67_93_0_, mdealerapp0_.TYP_APPLY as TYP68_93_0_, mdealerapp0_.FLG_DEF_SHIPTO as FLG69_93_0_, mdealerapp0_.MEM_FILE_NAME as MEM70_93_0_, mdealerapp0_.IOC_FILE_NAME as IOC71_93_0_, mdealerapp0_.MEM_FILE_ID as MEM72_93_0_, mdealerapp0_.IOC_FILE_ID as IOC73_93_0_, mdealerapp0_.CD_CREATE_BIZ as CD74_93_0_, mdealerapp0_.FLG_UPDATE_SALESSH as FLG75_93_0_, mdealerapp0_.FLG_UPDATE_SALESSB as FLG76_93_0_, mdealerapp0_.FLG_UPDATE_SALESSBSH as FLG77_93_0_, mdealerapp0_.FLG_SEND as FLG78_93_0_, mdealerapp0_.FLG_EDI_AR_ORDER as FLG79_93_0_, mdealerapp0_.DT_CLOSE as DT80_93_0_, mdealerapp0_.RFC_FILE_ID as RFC81_93_0_, mdealerapp0_.RFC_FILE_NAME as RFC82_93_0_, mdealerapp0_.RFC_MAIL_FLAG as RFC83_93_0_, mdealerapp0_.RFC_MAIL_ID as RFC84_93_0_, mdealerapp0_.RFC_MAIL_NAME as RFC85_93_0_, msubsectio1_.CD_CHANNEL as CD2_17_1_, msubsectio1_.NM_SUB_SECTION as NM3_17_1_, msubsectio1_.NM_SUB_SECTION_EN as NM4_17_1_, msubsectio1_.ZZ_CREATED_USER as ZZ5_17_1_, msubsectio1_.ZZ_DT_CREATED as ZZ6_17_1_, msubsectio1_.ZZ_DT_UPDATED as ZZ7_17_1_, msubsectio1_.ZZ_UPDATED_USER as ZZ8_17_1_, mchannel3_.NM_CHANNEL as NM2_11_2_, mchannel3_.NM_CHANNEL_EN as NM3_11_2_, msalesgrp2_.NM_SALES_GRP as NM2_25_3_, msalesgrp2_.CD_SUB_BRANCH as CD3_25_3_, msalesgrp2_.NM_SUB_BRANCH as NM4_25_3_, msalesgrp2_.CD_BRANCH as CD5_25_3_, msalesgrp2_.NM_BRANCH as NM6_25_3_, viewalluse4_.NM_USER as NM2_19_4_, viewalluse4_.MAIL as MAIL19_4_, viewalluse4_.TEL as TEL19_4_, viewalluse4_.TYP_USER as TYP5_19_4_
    from M_DEALER_APPLY mdealerapp0_
    inner join M_SUB_SECTION msubsectio1_ on mdealerapp0_.CD_SUB_SECTION=msubsectio1_.CD_SUB_SE CTION
    inner join M_CHANNEL mchannel3_ on msubsectio1_.CD_CHANNEL=mchannel3_.CD_CHANNEL
    inner join M_BRANCH msalesgrp2_ on mdealerapp0_.CD_SALES_GRP=msalesgrp2_.CD_SALES_GRP
    left outer join VIEW_ALL_USER viewalluse4_ on mdealerapp0_.SALESMAN=viewalluse4_.USER_ID
    where (exists (
    select mdealerapp5_.SEQ_DEALER
    from M_DEALER_APPLY mdealerapp5_, M_USER_BRANCH muserbranc6_, M_BRANCH msalesgrp7_, VIEW_SUB_BRANCH msubbranch8_
    where mdealerapp5_.CD_SALES_GRP=msalesgrp7_.CD_SALES_GRP
    and msalesgrp7_.CD_SUB_BRANCH=msubbranch8_.CD_SUB_BRAN CH
    and 1=1
    and msubbranch8_.CD_BRANCH=muserbranc6_.CD_BRANCH
    and muserbranc6_.USER_ID='200009'
    and muserbranc6_.CD_BIZ='2'
    and mdealerapp5_.SEQ_DEALER=mdealerapp0_.SEQ_DEALER)
    or exists (
    select mdealerapp9_.SEQ_DEALER
    from M_DEALER_APPLY mdealerapp9_, M_DEALER mdealer10_, M_USER_BRANCH muserbranc11_, M_BRANCH msalesgrp12_, VIEW_SUB_BRANCH msubbranch13_
    where mdealer10_.CD_SALES_GRP=msalesgrp12_.CD_SALES_GRP
    and msalesgrp12_.CD_SUB_BRANCH=msubbranch13_.CD_SUB_BR ANCH
    and 1=1
    and mdealerapp9_.CD_DEALER_FROM=mdealer10_.CD_DEALER
    and msubbranch13_.CD_BRANCH=muserbranc11_.CD_BRANCH
    and muserbranc11_.USER_ID='200009'
    and muserbranc11_.CD_BIZ='2'
    and mdealerapp9_.SEQ_DEALER=mdealerapp0_.SEQ_DEALER)
    )
    and (exists (
    select tsfeapprov14_.APPR_USERID
    from T_SFE_APPROVAL_IT tsfeapprov14_
    where tsfeapprov14_.APPR_USERID='200009'
    and tsfeapprov14_.CD_BIZ='2'
    and tsfeapprov14_.POS=mdealerapp0_.CUR_POS
    and tsfeapprov14_.SEQ_DELIV=mdealerapp0_.SEQ_DEALER
    and mdealerapp0_.ST_DEALER_APP=4) or mdealerapp0_.CREATE_USER='200009' and mdealerapp0_.CD_CREATE_BIZ='2' and mdealerapp0_.ST_DEALER_APP=1
    )
    and mdealerapp0_.CREATE_USER<>'SFE0201B02' order by mdealerapp0_.SEQ_DEALER ) where rownum <= 10;

    Both the queries have identical conditions but there seems be a lot of difference in the performance. The data query seems fine and returns me results in about 0.2 - 1 second. But the count query seems to taking inadvertently long. It executes in 120-130 seconds.

    Could someone please have a look at the above queries and help me out with what could possibly be wrong with the count query?

    I also ran the explain plan for both the queries. Unfortunately, I've never worked with explain plans before, hence not in a position to understand them.

    Explain plan for slow count query:
    https://www.box.com/s/0vtkzesdro1qp8cl4jf5

    Explain plan for fast data query:
    https://www.box.com/s/i15lh3nodp8diurklsc5
    https://www.box.com/s/3sizesfjiur3qb7wl7oa

    Any help in understanding these would be appreciable

    Thanks in advance!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Last line of "Count Query" was
    and mdealerapp0_.CREATE_USER<>'SFE0201B02' order by mdealerapp0_.SEQ_DEALER;

    Last line of "Data Query" was
    and mdealerapp0_.CREATE_USER<>'SFE0201B02' order by mdealerapp0_.SEQ_DEALER ) where rownum <= 10;


    Your description
    Both the queries have identical conditions but there seems be a lot of difference in the performance. ...
    was not right.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •