Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2010
    Posts
    207

    Cool Unanswered: Optimizing SQL Statement - Can anyone help?

    Hello Folks,

    I do have a big SQL Statement and I would like to optimise it a bit. Can anyone help me with that? I really appreciate any clue Thanks in advance.


    update hsp_konto_repl h set (h.saldo, h.prozess_datum) =
    (select bs.btr_kapitalsaldo,bb.dat_von_hist from BSD_BASIS_FA_A bb
    join BSD_SALDO_FA_A bs on bs.nr_konto = bb.nr_konto
    join vwtges0a vg on vg.nr_konto = bb.nr_konto
    where h.hsp_sachgebiet = 'BASDA' and h.kbp_produktkennung is not null and vg.K000_KTO_KEY_1 = h.KTO_KEY_1 and vg.Pers_key_1 = h.Pers_key_1
    and bb.nr_konto = h.kontonummer and h.hsp_sachgebiet = 'BASDA' and sl_kontostatus in ('30','31','40','41')
    and sl_darlehensart in ('91') and sl_verwzweck in ('55'))
    where h.hsp_sachgebiet = 'BASDA' and h.kbp_produktkennung is not null
    and exists(select * from BSD_BASIS_FA_A bb1
    where bb1.nr_konto = h.kontonummer and bb1.sl_kontostatus in ('30','31','40','41')
    and bb1.sl_darlehensart in ('91') and bb1.sl_verwzweck in ('50')
    and '10#' || bb1.sl_produkt = h.kbp_produktkennung)

  2. #2
    Join Date
    Jan 2010
    Posts
    207
    Error Message of this Statement is:

    SQL0437W Performance of this complex query may be sub-optimal. Reason code: "3". SQLSTATE=01602 commit DB20000I The SQL command completed successfully.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Ok. first off you should wrap your code in code tags and format it,to make it easier for folks to look at. Next, since you are using alias'(in green below) you should use them throughout. I've done so with your query and moved some things around, just ensuring that the join keys between tables are together, so that you can easily ensure you are joining tables properly. The query is not that complex, does it run in respectable amount of time? Have you run an explain to see if you are using the indexes you expect to use for each table. Really the only items I see that may cause a bit of issue are:
    1. SELECT * within your exists clause, could be SELECT 1, but not sure if that really matters anymore. (Marked in Orange)
    2. the concatenation on the one column within your exists clause, if the H table is being accessed first, it really shouldn't matter, but if BSD_BASIS_FA_A bb1 is being accessed first, it would more than likely cause a tablescan. (marked in red)
    More than likely, you are seeing this message, because of the query optimization level you have set and everything is fine. You might want to just change the OPT level.
    Dave

    Code:
    update hsp_konto_repl h 
    set (h.saldo, h.prozess_datum) = (select bs.btr_kapitalsaldo,bb.dat_von_hist
                                                      from BSD_BASIS_FA_A bb 
                                                       join BSD_SALDO_FA_A bs
                                                         on bs.nr_konto = bb.nr_konto 
                                                       join vwtges0a vg
                                                         on vg.nr_konto       = bb.nr_konto 
                                                        and vg.K000_KTO_KEY_1 = h.KTO_KEY_1
                                                        and vg.Pers_key_1     = h.Pers_key_1 
                                                  where h.hsp_sachgebiet      = 'BASDA'
                                                     and h.kbp_produktkennung is not null
                                                     and bb.nr_konto           = h.kontonummer
                                                     and h.hsp_sachgebiet      = 'BASDA'
                                                     and bb.sl_kontostatus    in ('30','31','40','41') 
                                                     and bb.sl_darlehensart   in ('91')
                                                     and bb.sl_verwzweck      in ('55')) 
    where h.hsp_sachgebiet      = 'BASDA'
       and h.kbp_produktkennung is not null 
       and exists (select 1 from BSD_BASIS_FA_A bb1 
                      where bb1.nr_konto            = h.kontonummer
                         and '10#' || bb1.sl_produkt = h.kbp_produktkennung
                         and bb1.sl_kontostatus     in ('30','31','40','41') 
                         and bb1.sl_darlehensart    in ('91')
                         and bb1.sl_verwzweck       in ('50') )

  4. #4
    Join Date
    Jan 2010
    Posts
    207
    Dear Dav1mo,

    Thank you very much for your reply. I think i should set up the Optimisation Level.

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Wink

    There is not so good as could be:

    Code:
    and '10#' || bb1.sl_produkt = h.kbp_produktkennung
    Better (for performance):

    Code:
    and  h.kbp_produktkennung = '10#' || bb1.sl_produkt
    Lenny

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Add to dav1mo,
    I prefer to use upper case for keyword and lower case for object names(for example: tables and columns).
    It is not necessary to use different correlation names in WHERE clause and in SET clause.
    By puttion subqueris in same column positions, it would be easy to compare them to see the ways to rewrite the statement.

    2a. You can eliminate concatenation by modifying the predicate to:
    Code:
            -- AND '10#' || bb1.sl_produkt = h.kbp_produktkennung
               AND '10#'         = LEFT  (h.kbp_produktkennung , 3)
               AND bb.sl_produkt = SUBSTR(h.kbp_produktkennung , 4)
    3. You can remove the following predicate in SET clause,
    because it already specified in WHERE clause.
    h. hsp_sachgebiet = 'BASDA'

    4. You can remove the predicate "h.kbp_produktkennung IS NOT NULL",
    because h.kbp_produktkennung was already compared in EXISTS subquery.
    If h. kbp_produktkennung was NULL,
    then comparison with the column get unknown and the row would not selected.

    Code:
    UPDATE hsp_konto_repl h
       SET (saldo , prozess_datum)
         = (SELECT bs.btr_kapitalsaldo
                 , bb.dat_von_hist
              FROM bsd_basis_fa_a bb 
              JOIN bsd_saldo_fa_a bs
               ON  bs.nr_konto = bb.nr_konto 
              JOIN vwtges0a       vg
               ON  vg.nr_konto = bb.nr_konto 
             WHERE -- h. hsp_sachgebiet = 'BASDA'
            -- AND h. kbp_produktkennung IS NOT NULL
            -- AND
                   vg.k000_kto_key_1 = h.kto_key_1
               AND vg.Pers_key_1     = h.Pers_key_1 
               AND bb.nr_konto = h.kontonummer
            -- AND h. hsp_sachgebiet = 'BASDA'
               AND bb.sl_kontostatus  IN ('30','31','40','41') 
               AND bb.sl_darlehensart IN ('91')
               AND bb.sl_verwzweck    IN ('55')
           ) 
     WHERE h.hsp_sachgebiet = 'BASDA'
    -- AND h.kbp_produktkennung IS NOT NULL
       AND EXISTS
           (SELECT 0
              FROM bsd_basis_fa_a bb 
             WHERE bb.nr_konto = h.kontonummer
               AND bb.sl_kontostatus  IN ('30','31','40','41') 
               AND bb.sl_darlehensart IN ('91')
               AND bb.sl_verwzweck    IN ('50')
            -- AND '10#' || bb1.sl_produkt = h.kbp_produktkennung
               AND '10#'         = LEFT  (h.kbp_produktkennung , 3)
               AND bb.sl_produkt = SUBSTR(h.kbp_produktkennung , 4)
           )

    5. No column of the table "vwtges0a vg" is used in SELECT list.
    So, JOIN can be changed to EXISTS predicate.
    Although EXISTS might be rewritten to JOIN in optimization process,
    it would give more alternative access paths for optimizer.

    Code:
    UPDATE hsp_konto_repl h
       SET (saldo , prozess_datum)
         = (SELECT bs.btr_kapitalsaldo
                 , bb.dat_von_hist
              FROM bsd_basis_fa_a bb 
              JOIN bsd_saldo_fa_a bs
               ON  bs.nr_konto = bb.nr_konto
             WHERE EXISTS
                   (SELECT 0
                      FROM vwtges0a vg
                     WHERE vg.nr_konto       = bb.nr_konto
                       AND vg.k000_kto_key_1 = h. kto_key_1
                       AND vg.Pers_key_1     = h. Pers_key_1
                   )
               AND bb.nr_konto = h.kontonummer
               AND bb.sl_kontostatus  IN ('30','31','40','41') 
               AND bb.sl_darlehensart IN ('91')
               AND bb.sl_verwzweck    IN ('55')
           ) 
     WHERE h.hsp_sachgebiet = 'BASDA'
       AND EXISTS
           (SELECT 0
              FROM bsd_basis_fa_a bb 
             WHERE bb.nr_konto = h.kontonummer
               AND bb.sl_kontostatus  IN ('30','31','40','41') 
               AND bb.sl_darlehensart IN ('91')
               AND bb.sl_verwzweck    IN ('50')
               AND '10#'         = LEFT  (h.kbp_produktkennung , 3)
               AND bb.sl_produkt = SUBSTR(h.kbp_produktkennung , 4)
           )
    6. I felt following two(similar but different) predicates were strange.
    in WHERE clause:
    AND bb.sl_verwzweck IN ('50')
    in SET clause:
    AND bb.sl_verwzweck IN ('55')

    If both were same( both were IN ('50'), or both were IN ('55') ),
    then you may want to use MERGE statement.

    Code:
    MERGE hsp_konto_repl h
    USING bsd_basis_fa_a bb
      ON  bb.nr_konto = h.kontonummer
      AND bb.sl_kontostatus  IN ('30','31','40','41') 
      AND bb.sl_darlehensart IN ('91')
      AND bb.sl_verwzweck    IN ('50')
      AND '10#'         = LEFT  (h.kbp_produktkennung , 3)
      AND bb.sl_produkt = SUBSTR(h.kbp_produktkennung , 4)
    WHEN MATCHED THEN
    UPDATE
       SET (saldo , prozess_datum)
         = (SELECT bs.btr_kapitalsaldo
                 , bb.dat_von_hist
              FROM bsd_saldo_fa_a bs
             WHERE bs.nr_konto = bb.nr_konto
               AND EXISTS
                   (SELECT 0
                      FROM vwtges0a vg
                     WHERE vg.nr_konto       = bb.nr_konto
                       AND vg.k000_kto_key_1 = h. kto_key_1
                       AND vg.Pers_key_1     = h. Pers_key_1 
                   )
           )
    Last edited by tonkuma; 07-09-10 at 12:47. Reason: Sorry. Changed the name to dav1mo.

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Quote Originally Posted by Lenny77 View Post
    There is not so good as could be:

    Code:
    and '10#' || bb1.sl_produkt = h.kbp_produktkennung
    Better (for performance):

    Code:
    and  h.kbp_produktkennung = '10#' || bb1.sl_produkt
    Lenny
    Lenny,
    It really shouldn't matter which side of the equation you have the concatenated string. I agree quite a few years ago the optimizer might have given you a different access path, but I do not think it applies any longer. It would now be based off which table is accessed first, as I explained in prior post.
    Dave

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Tonkuma,
    I don't know that suggestion 2a would be of any benefit. I'll have to try something like that out. I do like suggestions 4 and 5 though.
    Dave

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I don't know that suggestion 2a would be of any benefit.
    I expected that an index including the column sl_produkt on table "bsd_basis_fa_a bb" might be used.
    2a. You can eliminate concatenation by modifying the predicate to:

    Code:
            -- AND '10#' || bb1.sl_produkt = h.kbp_produktkennung
               AND '10#'         = LEFT  (h.kbp_produktkennung , 3)
               AND bb.sl_produkt = SUBSTR(h.kbp_produktkennung , 4)

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    2b. This might be worth to consider, too.

    Code:
            -- AND '10#' || bb1.sl_produkt = h.kbp_produktkennung
               AND h.kbp_produktkennung LIKE '10#%'
               AND bb.sl_produkt = SUBSTR(h.kbp_produktkennung , 4)

Posting Permissions

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