Optimizing SQL Statement - Can anyone help?

DB_N00b · #1 (**permalink**) 07-09-10, 03:41

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)

DB_N00b · #2 (**permalink**) 07-09-10, 03:42

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.

dav1mo · #3 (**permalink**) 07-09-10, 08:15

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') )

DB_N00b · #4 (**permalink**) 07-09-10, 08:58

Dear Dav1mo,

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

Lenny77 · #5 (**permalink**) 07-09-10, 09:55

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

tonkuma · #6 (**permalink**) 07-09-10, 10:24

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 
               )
       )

dav1mo · #7 (**permalink**) 07-09-10, 11:17

Quote:

Originally Posted by Lenny77

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

dav1mo · #8 (**permalink**) 07-09-10, 11:21

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

tonkuma · #9 (**permalink**) 07-09-10, 11:36

Quote:

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.

Quote:

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)

tonkuma · #10 (**permalink**) 07-09-10, 11:42

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)

Internet Services

Web Development

Digital Marketing

Consumer Tech