BSAD Performance issue

[Former Member]

Hi all,

We have the following SQL.

SELECT

vdbepi~ssolhab

vdbepi~kunnr

vdbepi~rebzj

vdbepi~rebzg

vdbepi~rebzz

INTO CORRESPONDING FIELDS OF TABLE lt_vdbepi_key

FROM ( vdbeki AS vdbeki INNER JOIN vdbepi AS vdbepi

ON vdbeki_{mandt = vdbepi}mandt

AND vdbeki_{bukrs = vdbepi}bukrs

AND vdbeki_{rbelkpfd = vdbepi}rbelkpfd )

FOR ALL ENTRIES IN ut_loan_bp_key

WHERE vdbeki~bukrs EQ uv_bukrs

AND vdbeki~ranl EQ ut_loan_bp_key-ranl

AND vdbeki~sstorno EQ space.

ENDIF.

• if we don't no Key Value, it means this contract doesn't have Postings

IF lt_vdbepi_key[] IS NOT INITIAL.

SELECT

kunnr "3

augdt "6

augbl "7

gjahr "9

belnr "10

buzei "11

bldat "13

shkzg "21

dmbtr "24

zfbdt "39

rebzg "54

rebzj "55

rebzz "56

vertn "92

vbewa "93

auggj "167

FROM bsad INTO CORRESPONDING FIELDS OF TABLE ct_bsad

FOR ALL ENTRIES IN lt_vdbepi_key

WHERE bukrs EQ uv_bukrs

AND vertn IN s_ranl

AND ( ( gjahr EQ lt_vdbepi_key-rebzj " direct link

AND belnr EQ lt_vdbepi_key-rebzg

AND buzei EQ lt_vdbepi_key-rebzz )

OR ( rebzg EQ lt_vdbepi_key-rebzg " indirect link

AND rebzj EQ lt_vdbepi_key-rebzj

AND rebzz EQ lt_vdbepi_key-rebzz ) ).

...

When we run this program the first time, it'll take 10 min to finish. But it'll take only 1 second when we try this in the second time. We tried to use ST05 and found that it is using indexes already.

We believe that it should be the cache issue becuase we'll see this problem after a few hours when we run this program again. We already talked to our BASIS but they said the system is in the best status. If they tune the system for this SQL, we might have other issues.

Do you have any suggestions that we can tune this SQL?

Note: FAE just have 20+ records max in the internal table.

Thanks in advance,

Tai

[Former Member]

The last OR condition looks suspicious to me. It may not be selective enough if there are many documents where these fields are initial.

Try changing that condition so that it only tries to find fields that are not initial.

Rob

[brad_bohn]

When we run this program the first time, it'll take 10 min to finish. But it'll take only 1 second when we try this in the second time.  We believe that it should be the cache issue becuase we'll see this problem after a few hours when we run this program again.

Right, because as you indicated, the statement has been buffered in the cursor cache.

 We already talked to our BASIS but they said the system is in the best status. If they tune the system for this SQL, we might have other issues.

True, but not a very helpful answer from the Basis team. There's no need to tune because you already have index support for the statement, both with BSEG_{0 (primary key) and with BSAD}4.

Do you have any suggestions that we can tune this SQL?

I would dump the 'OR' as Rob already hinted at. Try using 2 statements with APPENDING TABLE on the second and see how that works. You can hit BSEG for the BUKRS/BELNR/GJAHR/BUZEI combo (also specifying KOART, AUGBL not blank, and other fields to get the right items) and then BSAD for the BUKRS/REBZG/.../ combo.

[former_member186741]

Maybe dividing the sql into two separate selects would clarify things for the database system:

SELECT

kunnr "3 augdt "6 augbl "7 gjahr "9 belnr "10 buzei "11 bldat "13 shkzg "21 dmbtr "24

zfbdt "39 rebzg "54 rebzj "55 rebzz "56 vertn "92 vbewa "93 auggj "167

FROM bsad INTO CORRESPONDING FIELDS OF TABLE ct_bsad

FOR ALL ENTRIES IN lt_vdbepi_key

WHERE bukrs EQ uv_bukrs

AND vertn IN s_ranl

AND gjahr EQ lt_vdbepi_key-rebzj " direct link

AND belnr EQ lt_vdbepi_key-rebzg

AND buzei EQ lt_vdbepi_key-rebzz

.

SELECT

kunnr "3 augdt "6 augbl "7 gjahr "9 belnr "10 buzei "11 bldat "13 shkzg "21 dmbtr "24

zfbdt "39 rebzg "54 rebzj "55 rebzz "56 vertn "92 vbewa "93 auggj "167

FROM bsad appending CORRESPONDING FIELDS OF TABLE ct_bsad

FOR ALL ENTRIES IN lt_vdbepi_key

WHERE bukrs EQ uv_bukrs

AND vertn IN s_ranl

AND rebzg EQ lt_vdbepi_key-rebzg " indirect link

AND rebzj EQ lt_vdbepi_key-rebzj

AND rebzz EQ lt_vdbepi_key-rebzz .

• you may want to sort ct_bsad and remove adjacent duplicates after these selects

[brad_bohn]

Isn't that what I said without actually giving him the code?

[Former Member]

Thanks alot.

I'll try the suggestions.

Tai