Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2011
    Posts
    3

    Unhappy Unanswered: Optimizing Db2 SQL query

    Hi Guys,

    Wondering if you can help me.
    I am trying to optimize the following sql query because is is taking too long to execute. Originally it took around 30minutes to execute (which is normal) and after I made a few changes it now it takes around 8 hours or more.

    Below is the SQL:

    select a.accountid, b.bmbranch as branchsortcode, a.accountdescription, a.accountstyle, a.opendate, a.dormantstatus,
    a.accrightsindicator, (a.debitlimit * -1) as debitlimit, a.creditlimit, a.lastcredittransdttm, tr.amount,
    pi.product_numericcode,
    ff.fixturematuritydate, ff.fixturestartdate,
    a.limitreviewdate,
    aud.returnsclass, aud.cntbnkcd, aud.stdbnkcd, aud.siccd, aud.riskgrade, aud.wtaxcd,
    aud.portfolioid,
    c.sectortype,
    cud.organisationcode, cud.subsectorcode, COALESCE(L.PARENT1,'') AS GROUP_NUMBER,
    pud.penaltymargin, pud.penaltylimit,
    note1, note2, note3, status1, status2, status3, status4, status5

    from wasadmin.account a
    left join wasadmin.productinheritance pi on a.productcontextcode = pi.productcontextcode
    left join wasadmin.fixturefeature ff on a.accountid = ff.accountid
    left join wasadmin.accountlimit al on a.accountid = al.accountid
    left join wasadmin.accountuserdef aud on a.accountid=aud.accountid
    left join wasadmin.accportmap apm on a.accountid=apm.accountid
    left join wasadmin.portfolio port on apm.portfolioid=port.portfolioid
    left join wasadmin.customer c on port.customercode=c.customercode
    left join wasadmin.customeruserdef cud on c.customercode=cud.customercode
    left join wasadmin.penaltyuserdef pud on a.accountid=pud.accountid
    left join wasadmin.branch b on a.branchsortcode = b.branchsortcode
    LEFT JOIN wasadmin.LIMIT L ON A.LIMITREF5 = L.LIMITREF


    left join table (
    select accountid,
    max(case when accnoteid = accountid || 'AN01' then note end) as note1,
    max(case when accnoteid = accountid || 'AN11' then note end) as note2,
    max(case when accnoteid = accountid || 'AN21' then note end) as note3,
    max(case when accnoteid = accountid || 'AN01' then status end) as status1,
    max(case when accnoteid = accountid || 'AN11' then status end) as status2,
    max(case when accnoteid = accountid || 'AN21' then status end) as status3,
    max(case when accnoteid = accountid || 'AN31' then status end) as status4,
    max(case when accnoteid = accountid || 'AN41' then status end) as status5
    from wasadmin.accountnote
    group by accountid
    ) as an on a.accountid = an.accountid

    -- below is the changes i made (which is cause the problem) --
    left join table ( select accountproduct_accprodid, max(ACCTRANSCOUNTER) as ACCTRANSCOUNTER from wasadmin.TRANSACTION
    where debitcreditflag='C' group by accountproduct_accprodid) as trn on a.accountid = trn.accountproduct_accprodid
    left join wasadmin.transaction tr on tr.accountproduct_accprodid = trn.accountproduct_accprodid and tr.ACCTRANSCOUNTER = trn.ACCTRANSCOUNTER



    Thanking you in advance..

    Regards,
    Panik

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Do you mean that you added two left joins after "-- below is the changes i made (which is cause the problem) --" ?

    So, how about to replace them with a left join:
    Code:
      left join
           table
           (select amount
              from wasadmin.TRANSACTION tr
             where debitcreditflag = 'C'
               AND a.accountid     = tr.accountproduct_accprodid
             ORDER BY
                   ACCTRANSCOUNTER DESC
             FETCH FIRST 1 ROW ONLY
           ) as tr
       ON  0=0
    Note:
    I assumed that number of rows with MAX(ACCTRANSCOUNTER) for a accountproduct_accprodid is one.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I have a question:
    How al related to the final result?
    Is the left join necessary?
    Because, I couldn't see any column of al other than
    Code:
      left join
           wasadmin.accountlimit       al
       on  a.accountid          = al.accountid
    Last edited by tonkuma; 04-21-11 at 08:38. Reason: Removed L, because I found L.PARENT1 in select list.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another idea.

    Replace

    left join table (
    select accountid,
    max(case when accnoteid = accountid || 'AN01' then note end) as note1,
    max(case when accnoteid = accountid || 'AN11' then note end) as note2,
    max(case when accnoteid = accountid || 'AN21' then note end) as note3,
    max(case when accnoteid = accountid || 'AN01' then status end) as status1,
    max(case when accnoteid = accountid || 'AN11' then status end) as status2,
    max(case when accnoteid = accountid || 'AN21' then status end) as status3,
    max(case when accnoteid = accountid || 'AN31' then status end) as status4,
    max(case when accnoteid = accountid || 'AN41' then status end) as status5
    from wasadmin.accountnote
    group by accountid
    ) as an on a.accountid = an.accountid


    with

    Code:
      left join
           table
           (select
                   max(case when accnoteid = accountid || 'AN01' then note end) as note1
                 , max(case when accnoteid = accountid || 'AN11' then note end) as note2
                 , max(case when accnoteid = accountid || 'AN21' then note end) as note3
                 , max(case when accnoteid = accountid || 'AN01' then status end) as status1
                 , max(case when accnoteid = accountid || 'AN11' then status end) as status2
                 , max(case when accnoteid = accountid || 'AN21' then status end) as status3
                 , max(case when accnoteid = accountid || 'AN31' then status end) as status4
                 , max(case when accnoteid = accountid || 'AN41' then status end) as status5
              from wasadmin.accountnote an
             WHERE a.accountid = an.accountid
               AND RIGHT(accnoteid , 4) IN ('AN01' , 'AN11' , 'AN21' , 'AN31' , 'AN41')
               AND POSSTR(accnoteid , accountid) = 1
           ) as an
       ON  0=0

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Because there is no WHERE condition for table wasadmin.account a,
    this rewriting for "an" may be not so effective than original subquery.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another idea of rewriting trn and tr.

    Code:
      left join
           table
           (select accountproduct_accprodid
                 , amount
                 , ROW_NUMBER()
                     OVER(PARTITION BY accountproduct_accprodid
                              ORDER BY ACCTRANSCOUNTER DESC) AS rn
              from wasadmin.TRANSACTION
             where debitcreditflag = 'C'
           ) as tr
       ON  tr.accountproduct_accprodid = a.accountid
       AND tr.rn                       = 1

  7. #7
    Join Date
    Apr 2011
    Posts
    3
    Hi tonkuma,

    thanks for your replies.


    with regards to the code :
    left join
    wasadmin.accountlimit al
    on a.accountid

    Please bare in mind that the query was originally written by someone else. I don't quite know what the relevance is but i'll find out and get back to you.

    I will try your suggestions and get back to you.

    thank you very much.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Panik,
    Did you tried my suggestions?

    Was there any issue?
    Did they worked better or worse than original?

  9. #9
    Join Date
    Apr 2011
    Posts
    3
    Hi tonkuma,

    Apologies for the delayed response. I've been under emense pressure here at work.

    Yes I did try your suggestions and it executes much faster .. I sent the program through for testing and i am waiting for their feedback.

    Thanx once again.
    Hopefully we will be celebrating soon lol

    Regards,
    Panik

Posting Permissions

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