Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2007
    Posts
    197

    Smile Unanswered: long running query

    Hi Have below query

    Code:
    select date(pd.date) as ISSUE_TM, pd.agent_cd,ba.AGENT_NM,0,0,0,0,0,0,0,0,0,pd.deposit_amount,GROUP_CONCAT(pd.RECEIPT_NO) RECEIPT_NO,'' from pagent_deposits pd
                    inner join booking_agents ba on ba.agent_cd = pd.agent_cd
                    inner join pagent_account_trans pat on pat.agent_cd = pd.agent_cd
                    and ba.agent_type = 'P' AND  ba.depot_cd='UPSRTC'
                    and pd.date BETWEEN '2014-01-31 00:00:00' AND '2014-01-31 23:59:59'
                    GROUP BY ba.AGENT_CD,date(pd.date)
    This is taking 1min 55sec

    If the above query executes in parallel many queries then server load goes to very Huge

    explain plan

    Code:
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: ba
             type: ref
    possible_keys: PRIMARY,FK_DEPOT_CD,idx_agent_type,FK_DEPOT_CD_AGENT_TYPE
              key: FK_DEPOT_CD_AGENT_TYPE
          key_len: 16
              ref: const,const
             rows: 1
            Extra: Using where; Using temporary; Using filesort
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: pd
             type: range
    possible_keys: idx_agent_cd,idx_date
              key: idx_date
          key_len: 8
              ref: NULL
             rows: 215
            Extra: Using where; Using join buffer
    *************************** 3. row ***************************
               id: 1
      select_type: SIMPLE
            table: pat
             type: ref
    possible_keys: idx_agent_cd
              key: idx_agent_cd
          key_len: 22
              ref: UpsrtcORS.pd.AGENT_CD
             rows: 40104
            Extra: Using where; Using index
    Is there any scope of optimization or rewrite the query

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why did you joined "pagent_account_trans pat"?
    No column of the table appeared except "on pat.agent_cd = pd.agent_cd".

  3. #3
    Join Date
    Jun 2007
    Posts
    197
    Quote Originally Posted by tonkuma View Post
    Why did you joined "pagent_account_trans pat"?
    No column of the table appeared except "on pat.agent_cd = pd.agent_cd".
    Thanks for the suggestion

    I have made some changes in the above query which is now executing much faster

    Removed the inner join for pagent_account_trans
    Use ba.agent_cd in (select ba.agent_cd from pagent_account_trans pat)

    remove the date(pd.date) from group by, as for one day this is not need in group by

    Code:
    select date(pd.date) as ISSUE_TM, pd.agent_cd,ba.AGENT_NM,pd.deposit_amount,group_concat(distinct pd.RECEIPT_NO) RECEIPT_NO 
    from pagent_deposits pd 
    inner join booking_agents ba on ba.agent_cd = pd.agent_cd 
    where ba.agent_cd in (select   ba.agent_cd from pagent_account_trans pat) 
    and pd.date BETWEEN '2014-01-31 00:00:00' AND '2014-01-31 23:59:59' 
    and ba.agent_type = 'P'
    AND  ba.depot_cd='UPSRTC' group by pd.agent_cd;

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    ...
    where ba.agent_cd in (select   ba.agent_cd from pagent_account_trans pat)
    ...
    (1) I thought that
    ba.agent_cd in (select ba.agent_cd from pagent_account_trans pat)
    should be
    ba.agent_cd in (select pat.agent_cd from pagent_account_trans pat)


    (2) EXISTS might be another option, like...
    Code:
    ...
     where EXISTS
           (SELECT 0
             FROM  pagent_account_trans pat
             WHERE pat.agent_cd = ba.agent_cd
           )
    ...

Posting Permissions

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