Community
Application Development and Automation Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

ABAP SQL tuning

Former Member

‎2006 Dec 04 2:41 PM

0 Likes
882

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#

5 REPLIES 5
Read only

Former Member

‎2006 Dec 04 2:46 PM

0 Likes
814

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

Rob

Read only

Former Member
In response to Former Member

‎2006 Dec 04 2:51 PM

0 Likes
814

Sorry..And thanks for looking:

select SKATKTOPL SKATMANDT SKATSAKNR SKATTXT20 SKATTXT50 SKA1BILKT SKA1GVTYP SKA1KTOKS SKA1KTOPL SKA1SAKNR SKA1~XBILK

SKB1BEGRU SKB1BUKRS SKB1ERDAT SKB1FSTAG SKB1MITKZ SKB1MWSKZ SKB1SAKNR SKB1WAERS SKB1XKRES SKB1XMWNO SKB1~XOPVW

SKB1XSALH SKB1ZUAWA

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 SKA1KTOPL = SKATKTOPL

and SKA1SAKNR = SKATSAKNR

inner join SKB1

on SKB1SAKNR = SKATSAKNR )

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.

Read only

Former Member
In response to Former Member

‎2006 Dec 04 3:02 PM

0 Likes
814

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

Read only

Former Member
In response to Former Member

‎2006 Dec 06 4:55 PM

0 Likes
814

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

Rob

Read only

Former Member

‎2006 Dec 06 5:21 PM

0 Likes
814

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.