topic Re: ABAP SQL tuning in Application Development and Automation Discussions

ABAP SQL tuning

Former Member — Mon, 04 Dec 2006 14:41:29 GMT

Have an abap query. Although only 40K records in SKAT program utilizes 100%cpu and takes a long time.

Any suggestions would be greatly appreciated.

SELECT

T_00 . "KTOPL" , T_00 . "MANDT" , T_00 . "SAKNR" , T_00 . "TXT20" , T_00 . "TXT50" ,

T_01 . "BILKT" , T_01 . "GVTYP" , T_01 . "KTOKS" , T_01 . "KTOPL" , T_01 . "SAKNR" ,

T_01 . "XBILK" , T_02 . "BEGRU" , T_02 . "BUKRS" , T_02 . "ERDAT" , T_02 . "FSTAG" ,

T_02 . "MITKZ" , T_02 . "MWSKZ" , T_02 . "SAKNR" , T_02 . "WAERS" , T_02 . "XKRES" ,

T_02 . "XMWNO" , T_02 . "XOPVW" , T_02 . "XSALH" , T_02 . "ZUAWA"

FROM

"SKAT" T_00 INNER JOIN "SKA1" T_01 ON T_01 . "MANDT" = :A0 AND T_01 . "KTOPL" = T_00 . "KTOPL" AND

T_01 . "SAKNR" = T_00 . "SAKNR" INNER JOIN "SKB1" T_02 ON T_02 . "MANDT" = :A1 AND T_02 . "SAKNR"

= T_00 . "SAKNR"

WHERE

T_00 . "MANDT" = :A2 AND T_00 . "KTOPL" = :A3 AND T_02 . "BUKRS" BETWEEN :A4 AND :A5 AND T_02 .

"ERDAT" BETWEEN :A6 AND :A7#

Re: ABAP SQL tuning

Former Member — Mon, 04 Dec 2006 14:46:43 GMT

This looks like code from the performance trace. Would you please post the original ABAP code?

Rob

Re: ABAP SQL tuning

Former Member — Mon, 04 Dec 2006 14:51:00 GMT

Sorry..And thanks for looking:

select SKAT_{KTOPL SKAT}MANDT SKAT_{SAKNR SKAT}TXT20 SKAT_{TXT50 SKA1}BILKT SKA1_{GVTYP SKA1}KTOKS SKA1_{KTOPL SKA1}SAKNR SKA1~XBILK

SKB1_{BEGRU SKB1}BUKRS SKB1_{ERDAT SKB1}FSTAG SKB1_{MITKZ SKB1}MWSKZ SKB1_{SAKNR SKB1}WAERS SKB1_{XKRES SKB1}XMWNO SKB1~XOPVW

SKB1_{XSALH SKB1}ZUAWA

into (SKAT-KTOPL , SKAT-MANDT , SKAT-SAKNR , SKAT-TXT20 , SKAT-TXT50 , SKA1-BILKT , SKA1-GVTYP , SKA1-KTOKS , SKA1-KTOPL

, SKA1-SAKNR , SKA1-XBILK , SKB1-BEGRU , SKB1-BUKRS , SKB1-ERDAT , SKB1-FSTAG , SKB1-MITKZ , SKB1-MWSKZ , SKB1-SAKNR

, SKB1-WAERS , SKB1-XKRES , SKB1-XMWNO , SKB1-XOPVW , SKB1-XSALH , SKB1-ZUAWA )

from ( SKAT

inner join SKA1

on SKA1_{KTOPL = SKAT}KTOPL

and SKA1_{SAKNR = SKAT}SAKNR

inner join SKB1

on SKB1_{SAKNR = SKAT}SAKNR )

where SKAT~KTOPL in SP$00001

and SKAT~SAKNR in SP$00002

and SKB1~BUKRS in SP$00003

and SKB1~ERDAT in SP$00005

and SKB1~FSTAG in SP$00004.

Re: ABAP SQL tuning

Former Member — Mon, 04 Dec 2006 15:02:22 GMT

For a start, try adding sy-langu to the SELECT:


TABLES: skat, ska1, skb1.

RANGES: sp
TABLES: skat, ska1, skb1.

RANGES: sp$00001 FOR skat-ktopl,
        sp$00002 FOR skat-saknr,
        sp$00003 FOR skb1-bukrs,
        sp$00004 FOR skb1-fstag,
        sp$00005 FOR skb1-erdat.

SELECT  skat~ktopl skat~mandt skat~saknr skat~txt20 skat~txt50
        ska1~bilkt ska1~gvtyp ska1~ktoks ska1~ktopl ska1~saknr
        ska1~xbilk skb1~begru skb1~bukrs skb1~erdat skb1~fstag
        skb1~mitkz skb1~mwskz skb1~saknr skb1~waers skb1~xkres
        skb1~xmwno skb1~xopvw skb1~xsalh skb1~zuawa
  INTO (skat-ktopl , skat-mandt , skat-saknr , skat-txt20 , skat-txt50 ,
        ska1-bilkt , ska1-gvtyp , ska1-ktoks , ska1-ktopl ,
        ska1-saknr , ska1-xbilk , skb1-begru , skb1-bukrs , skb1-erdat ,
        skb1-fstag , skb1-mitkz , skb1-mwskz , skb1-saknr ,
        skb1-waers , skb1-xkres , skb1-xmwno , skb1-xopvw , skb1-xsalh ,
        skb1-zuawa )
  FROM ( skat
    INNER JOIN ska1
      ON ska1~ktopl = skat~ktopl
     AND ska1~saknr = skat~saknr
    INNER JOIN skb1
      ON skb1~saknr = skat~saknr )
  WHERE skat~ktopl IN sp$00001
    AND skat~spras = sy-langu
    AND skat~saknr IN sp$00002
    AND skb1~bukrs IN sp$00003
    AND skb1~erdat IN sp$00005
    AND skb1~fstag IN sp$00004.

ENDSELECT.
 FOR skat-ktopl,
        sp[code]
TABLES: skat, ska1, skb1.

RANGES: sp$00001 FOR skat-ktopl,
        sp$00002 FOR skat-saknr,
        sp$00003 FOR skb1-bukrs,
        sp$00004 FOR skb1-fstag,
        sp$00005 FOR skb1-erdat.

SELECT  skat~ktopl skat~mandt skat~saknr skat~txt20 skat~txt50
        ska1~bilkt ska1~gvtyp ska1~ktoks ska1~ktopl ska1~saknr
        ska1~xbilk skb1~begru skb1~bukrs skb1~erdat skb1~fstag
        skb1~mitkz skb1~mwskz skb1~saknr skb1~waers skb1~xkres
        skb1~xmwno skb1~xopvw skb1~xsalh skb1~zuawa
  INTO (skat-ktopl , skat-mandt , skat-saknr , skat-txt20 , skat-txt50 ,
        ska1-bilkt , ska1-gvtyp , ska1-ktoks , ska1-ktopl ,
        ska1-saknr , ska1-xbilk , skb1-begru , skb1-bukrs , skb1-erdat ,
        skb1-fstag , skb1-mitkz , skb1-mwskz , skb1-saknr ,
        skb1-waers , skb1-xkres , skb1-xmwno , skb1-xopvw , skb1-xsalh ,
        skb1-zuawa )
  FROM ( skat
    INNER JOIN ska1
      ON ska1~ktopl = skat~ktopl
     AND ska1~saknr = skat~saknr
    INNER JOIN skb1
      ON skb1~saknr = skat~saknr )
  WHERE skat~ktopl IN sp$00001
    AND skat~spras = sy-langu
    AND skat~saknr IN sp$00002
    AND skb1~bukrs IN sp$00003
    AND skb1~erdat IN sp$00005
    AND skb1~fstag IN sp$00004.

ENDSELECT.
[/code]2 FOR skat-saknr,
        sp[code]
TABLES: skat, ska1, skb1.

RANGES: sp$00001 FOR skat-ktopl,
        sp$00002 FOR skat-saknr,
        sp$00003 FOR skb1-bukrs,
        sp$00004 FOR skb1-fstag,
        sp$00005 FOR skb1-erdat.

SELECT  skat~ktopl skat~mandt skat~saknr skat~txt20 skat~txt50
        ska1~bilkt ska1~gvtyp ska1~ktoks ska1~ktopl ska1~saknr
        ska1~xbilk skb1~begru skb1~bukrs skb1~erdat skb1~fstag
        skb1~mitkz skb1~mwskz skb1~saknr skb1~waers skb1~xkres
        skb1~xmwno skb1~xopvw skb1~xsalh skb1~zuawa
  INTO (skat-ktopl , skat-mandt , skat-saknr , skat-txt20 , skat-txt50 ,
        ska1-bilkt , ska1-gvtyp , ska1-ktoks , ska1-ktopl ,
        ska1-saknr , ska1-xbilk , skb1-begru , skb1-bukrs , skb1-erdat ,
        skb1-fstag , skb1-mitkz , skb1-mwskz , skb1-saknr ,
        skb1-waers , skb1-xkres , skb1-xmwno , skb1-xopvw , skb1-xsalh ,
        skb1-zuawa )
  FROM ( skat
    INNER JOIN ska1
      ON ska1~ktopl = skat~ktopl
     AND ska1~saknr = skat~saknr
    INNER JOIN skb1
      ON skb1~saknr = skat~saknr )
  WHERE skat~ktopl IN sp$00001
    AND skat~spras = sy-langu
    AND skat~saknr IN sp$00002
    AND skb1~bukrs IN sp$00003
    AND skb1~erdat IN sp$00005
    AND skb1~fstag IN sp$00004.

ENDSELECT.
[/code]3 FOR skb1-bukrs,
        sp[code]
TABLES: skat, ska1, skb1.

RANGES: sp$00001 FOR skat-ktopl,
        sp$00002 FOR skat-saknr,
        sp$00003 FOR skb1-bukrs,
        sp$00004 FOR skb1-fstag,
        sp$00005 FOR skb1-erdat.

SELECT  skat~ktopl skat~mandt skat~saknr skat~txt20 skat~txt50
        ska1~bilkt ska1~gvtyp ska1~ktoks ska1~ktopl ska1~saknr
        ska1~xbilk skb1~begru skb1~bukrs skb1~erdat skb1~fstag
        skb1~mitkz skb1~mwskz skb1~saknr skb1~waers skb1~xkres
        skb1~xmwno skb1~xopvw skb1~xsalh skb1~zuawa
  INTO (skat-ktopl , skat-mandt , skat-saknr , skat-txt20 , skat-txt50 ,
        ska1-bilkt , ska1-gvtyp , ska1-ktoks , ska1-ktopl ,
        ska1-saknr , ska1-xbilk , skb1-begru , skb1-bukrs , skb1-erdat ,
        skb1-fstag , skb1-mitkz , skb1-mwskz , skb1-saknr ,
        skb1-waers , skb1-xkres , skb1-xmwno , skb1-xopvw , skb1-xsalh ,
        skb1-zuawa )
  FROM ( skat
    INNER JOIN ska1
      ON ska1~ktopl = skat~ktopl
     AND ska1~saknr = skat~saknr
    INNER JOIN skb1
      ON skb1~saknr = skat~saknr )
  WHERE skat~ktopl IN sp$00001
    AND skat~spras = sy-langu
    AND skat~saknr IN sp$00002
    AND skb1~bukrs IN sp$00003
    AND skb1~erdat IN sp$00005
    AND skb1~fstag IN sp$00004.

ENDSELECT.
[/code]4 FOR skb1-fstag,
        sp[code]
TABLES: skat, ska1, skb1.

RANGES: sp$00001 FOR skat-ktopl,
        sp$00002 FOR skat-saknr,
        sp$00003 FOR skb1-bukrs,
        sp$00004 FOR skb1-fstag,
        sp$00005 FOR skb1-erdat.

SELECT  skat~ktopl skat~mandt skat~saknr skat~txt20 skat~txt50
        ska1~bilkt ska1~gvtyp ska1~ktoks ska1~ktopl ska1~saknr
        ska1~xbilk skb1~begru skb1~bukrs skb1~erdat skb1~fstag
        skb1~mitkz skb1~mwskz skb1~saknr skb1~waers skb1~xkres
        skb1~xmwno skb1~xopvw skb1~xsalh skb1~zuawa
  INTO (skat-ktopl , skat-mandt , skat-saknr , skat-txt20 , skat-txt50 ,
        ska1-bilkt , ska1-gvtyp , ska1-ktoks , ska1-ktopl ,
        ska1-saknr , ska1-xbilk , skb1-begru , skb1-bukrs , skb1-erdat ,
        skb1-fstag , skb1-mitkz , skb1-mwskz , skb1-saknr ,
        skb1-waers , skb1-xkres , skb1-xmwno , skb1-xopvw , skb1-xsalh ,
        skb1-zuawa )
  FROM ( skat
    INNER JOIN ska1
      ON ska1~ktopl = skat~ktopl
     AND ska1~saknr = skat~saknr
    INNER JOIN skb1
      ON skb1~saknr = skat~saknr )
  WHERE skat~ktopl IN sp$00001
    AND skat~spras = sy-langu
    AND skat~saknr IN sp$00002
    AND skb1~bukrs IN sp$00003
    AND skb1~erdat IN sp$00005
    AND skb1~fstag IN sp$00004.

ENDSELECT.
[/code]5 FOR skb1-erdat.

SELECT  skat~ktopl skat~mandt skat~saknr skat~txt20 skat~txt50
        ska1~bilkt ska1~gvtyp ska1~ktoks ska1~ktopl ska1~saknr
        ska1~xbilk skb1~begru skb1~bukrs skb1~erdat skb1~fstag
        skb1~mitkz skb1~mwskz skb1~saknr skb1~waers skb1~xkres
        skb1~xmwno skb1~xopvw skb1~xsalh skb1~zuawa
  INTO (skat-ktopl , skat-mandt , skat-saknr , skat-txt20 , skat-txt50 ,
        ska1-bilkt , ska1-gvtyp , ska1-ktoks , ska1-ktopl ,
        ska1-saknr , ska1-xbilk , skb1-begru , skb1-bukrs , skb1-erdat ,
        skb1-fstag , skb1-mitkz , skb1-mwskz , skb1-saknr ,
        skb1-waers , skb1-xkres , skb1-xmwno , skb1-xopvw , skb1-xsalh ,
        skb1-zuawa )
  FROM ( skat
    INNER JOIN ska1
      ON ska1~ktopl = skat~ktopl
     AND ska1~saknr = skat~saknr
    INNER JOIN skb1
      ON skb1~saknr = skat~saknr )
  WHERE skat~ktopl IN sp
TABLES: skat, ska1, skb1.

RANGES: sp$00001 FOR skat-ktopl,
        sp$00002 FOR skat-saknr,
        sp$00003 FOR skb1-bukrs,
        sp$00004 FOR skb1-fstag,
        sp$00005 FOR skb1-erdat.

SELECT  skat~ktopl skat~mandt skat~saknr skat~txt20 skat~txt50
        ska1~bilkt ska1~gvtyp ska1~ktoks ska1~ktopl ska1~saknr
        ska1~xbilk skb1~begru skb1~bukrs skb1~erdat skb1~fstag
        skb1~mitkz skb1~mwskz skb1~saknr skb1~waers skb1~xkres
        skb1~xmwno skb1~xopvw skb1~xsalh skb1~zuawa
  INTO (skat-ktopl , skat-mandt , skat-saknr , skat-txt20 , skat-txt50 ,
        ska1-bilkt , ska1-gvtyp , ska1-ktoks , ska1-ktopl ,
        ska1-saknr , ska1-xbilk , skb1-begru , skb1-bukrs , skb1-erdat ,
        skb1-fstag , skb1-mitkz , skb1-mwskz , skb1-saknr ,
        skb1-waers , skb1-xkres , skb1-xmwno , skb1-xopvw , skb1-xsalh ,
        skb1-zuawa )
  FROM ( skat
    INNER JOIN ska1
      ON ska1~ktopl = skat~ktopl
     AND ska1~saknr = skat~saknr
    INNER JOIN skb1
      ON skb1~saknr = skat~saknr )
  WHERE skat~ktopl IN sp$00001
    AND skat~spras = sy-langu
    AND skat~saknr IN sp$00002
    AND skb1~bukrs IN sp$00003
    AND skb1~erdat IN sp$00005
    AND skb1~fstag IN sp$00004.

ENDSELECT.

    AND skat~spras = sy-langu
    AND skat~saknr IN sp[code]
TABLES: skat, ska1, skb1.

RANGES: sp$00001 FOR skat-ktopl,
        sp$00002 FOR skat-saknr,
        sp$00003 FOR skb1-bukrs,
        sp$00004 FOR skb1-fstag,
        sp$00005 FOR skb1-erdat.

SELECT  skat~ktopl skat~mandt skat~saknr skat~txt20 skat~txt50
        ska1~bilkt ska1~gvtyp ska1~ktoks ska1~ktopl ska1~saknr
        ska1~xbilk skb1~begru skb1~bukrs skb1~erdat skb1~fstag
        skb1~mitkz skb1~mwskz skb1~saknr skb1~waers skb1~xkres
        skb1~xmwno skb1~xopvw skb1~xsalh skb1~zuawa
  INTO (skat-ktopl , skat-mandt , skat-saknr , skat-txt20 , skat-txt50 ,
        ska1-bilkt , ska1-gvtyp , ska1-ktoks , ska1-ktopl ,
        ska1-saknr , ska1-xbilk , skb1-begru , skb1-bukrs , skb1-erdat ,
        skb1-fstag , skb1-mitkz , skb1-mwskz , skb1-saknr ,
        skb1-waers , skb1-xkres , skb1-xmwno , skb1-xopvw , skb1-xsalh ,
        skb1-zuawa )
  FROM ( skat
    INNER JOIN ska1
      ON ska1~ktopl = skat~ktopl
     AND ska1~saknr = skat~saknr
    INNER JOIN skb1
      ON skb1~saknr = skat~saknr )
  WHERE skat~ktopl IN sp$00001
    AND skat~spras = sy-langu
    AND skat~saknr IN sp$00002
    AND skb1~bukrs IN sp$00003
    AND skb1~erdat IN sp$00005
    AND skb1~fstag IN sp$00004.

ENDSELECT.
[/code]2
    AND skb1~bukrs IN sp[code]
TABLES: skat, ska1, skb1.

RANGES: sp$00001 FOR skat-ktopl,
        sp$00002 FOR skat-saknr,
        sp$00003 FOR skb1-bukrs,
        sp$00004 FOR skb1-fstag,
        sp$00005 FOR skb1-erdat.

SELECT  skat~ktopl skat~mandt skat~saknr skat~txt20 skat~txt50
        ska1~bilkt ska1~gvtyp ska1~ktoks ska1~ktopl ska1~saknr
        ska1~xbilk skb1~begru skb1~bukrs skb1~erdat skb1~fstag
        skb1~mitkz skb1~mwskz skb1~saknr skb1~waers skb1~xkres
        skb1~xmwno skb1~xopvw skb1~xsalh skb1~zuawa
  INTO (skat-ktopl , skat-mandt , skat-saknr , skat-txt20 , skat-txt50 ,
        ska1-bilkt , ska1-gvtyp , ska1-ktoks , ska1-ktopl ,
        ska1-saknr , ska1-xbilk , skb1-begru , skb1-bukrs , skb1-erdat ,
        skb1-fstag , skb1-mitkz , skb1-mwskz , skb1-saknr ,
        skb1-waers , skb1-xkres , skb1-xmwno , skb1-xopvw , skb1-xsalh ,
        skb1-zuawa )
  FROM ( skat
    INNER JOIN ska1
      ON ska1~ktopl = skat~ktopl
     AND ska1~saknr = skat~saknr
    INNER JOIN skb1
      ON skb1~saknr = skat~saknr )
  WHERE skat~ktopl IN sp$00001
    AND skat~spras = sy-langu
    AND skat~saknr IN sp$00002
    AND skb1~bukrs IN sp$00003
    AND skb1~erdat IN sp$00005
    AND skb1~fstag IN sp$00004.

ENDSELECT.
[/code]3
    AND skb1~erdat IN sp[code]
TABLES: skat, ska1, skb1.

RANGES: sp$00001 FOR skat-ktopl,
        sp$00002 FOR skat-saknr,
        sp$00003 FOR skb1-bukrs,
        sp$00004 FOR skb1-fstag,
        sp$00005 FOR skb1-erdat.

SELECT  skat~ktopl skat~mandt skat~saknr skat~txt20 skat~txt50
        ska1~bilkt ska1~gvtyp ska1~ktoks ska1~ktopl ska1~saknr
        ska1~xbilk skb1~begru skb1~bukrs skb1~erdat skb1~fstag
        skb1~mitkz skb1~mwskz skb1~saknr skb1~waers skb1~xkres
        skb1~xmwno skb1~xopvw skb1~xsalh skb1~zuawa
  INTO (skat-ktopl , skat-mandt , skat-saknr , skat-txt20 , skat-txt50 ,
        ska1-bilkt , ska1-gvtyp , ska1-ktoks , ska1-ktopl ,
        ska1-saknr , ska1-xbilk , skb1-begru , skb1-bukrs , skb1-erdat ,
        skb1-fstag , skb1-mitkz , skb1-mwskz , skb1-saknr ,
        skb1-waers , skb1-xkres , skb1-xmwno , skb1-xopvw , skb1-xsalh ,
        skb1-zuawa )
  FROM ( skat
    INNER JOIN ska1
      ON ska1~ktopl = skat~ktopl
     AND ska1~saknr = skat~saknr
    INNER JOIN skb1
      ON skb1~saknr = skat~saknr )
  WHERE skat~ktopl IN sp$00001
    AND skat~spras = sy-langu
    AND skat~saknr IN sp$00002
    AND skb1~bukrs IN sp$00003
    AND skb1~erdat IN sp$00005
    AND skb1~fstag IN sp$00004.

ENDSELECT.
[/code]5
    AND skb1~fstag IN sp[code]
TABLES: skat, ska1, skb1.

RANGES: sp$00001 FOR skat-ktopl,
        sp$00002 FOR skat-saknr,
        sp$00003 FOR skb1-bukrs,
        sp$00004 FOR skb1-fstag,
        sp$00005 FOR skb1-erdat.

SELECT  skat~ktopl skat~mandt skat~saknr skat~txt20 skat~txt50
        ska1~bilkt ska1~gvtyp ska1~ktoks ska1~ktopl ska1~saknr
        ska1~xbilk skb1~begru skb1~bukrs skb1~erdat skb1~fstag
        skb1~mitkz skb1~mwskz skb1~saknr skb1~waers skb1~xkres
        skb1~xmwno skb1~xopvw skb1~xsalh skb1~zuawa
  INTO (skat-ktopl , skat-mandt , skat-saknr , skat-txt20 , skat-txt50 ,
        ska1-bilkt , ska1-gvtyp , ska1-ktoks , ska1-ktopl ,
        ska1-saknr , ska1-xbilk , skb1-begru , skb1-bukrs , skb1-erdat ,
        skb1-fstag , skb1-mitkz , skb1-mwskz , skb1-saknr ,
        skb1-waers , skb1-xkres , skb1-xmwno , skb1-xopvw , skb1-xsalh ,
        skb1-zuawa )
  FROM ( skat
    INNER JOIN ska1
      ON ska1~ktopl = skat~ktopl
     AND ska1~saknr = skat~saknr
    INNER JOIN skb1
      ON skb1~saknr = skat~saknr )
  WHERE skat~ktopl IN sp$00001
    AND skat~spras = sy-langu
    AND skat~saknr IN sp$00002
    AND skb1~bukrs IN sp$00003
    AND skb1~erdat IN sp$00005
    AND skb1~fstag IN sp$00004.

ENDSELECT.
[/code]4.

ENDSELECT.

Rob

Re: ABAP SQL tuning

Former Member — Wed, 06 Dec 2006 16:55:42 GMT

To satisfy my own curiosity, I'd like to know if this helped at all.

Rob

Re: ABAP SQL tuning

Former Member — Wed, 06 Dec 2006 17:21:21 GMT

H Bob.

I am still in the testing stage. I will be sure to get back to you and award pts.

Currently updating all stats on tables/indexes involved.

Upon initial test no data was returned from the abap query which was strange so not sure what is occurring. On the oracle level things seem fine.