Query Optimisation

[Former Member]

Please Help optimize the following query.

SELECT HKONT GSBER BUKRS AUGDT BUDAT SHKZG

DMBTR WAERS AUGBL ZUONR GJAHR BELNR BUZEI

INTO TABLE T_BSIS_TEMP

FROM BSIS

FOR ALL ENTRIES IN T_SKB1

WHERE BUKRS EQ T_SKB1-BUKRS

AND HKONT EQ T_SKB1-SAKNR

AND BUDAT LE P_BUDAT.

IF SY-SUBRC = 0.

DELETE T_BSIS WHERE NOT GSBER IN S_GSBER.

T_BSIS[] = T_BSIS_TEMP[].

SORT T_BSIS BY BUKRS HKONT GSBER.

ENDIF.

Thanking you.

[Former Member]

Hi,

From the driver table you may delete all the adjacent duplicates comparing all fields. While using for all entries pl make sure the driver table is not empty .

eg.

IF NOT T_SKB1[] IS INITIAL.

Select Stmt

Endif.

If helpful pl reward.

Cheers....

[Former Member]

Hi

<b>IF NOT T_SKB1[] IS INITIAL.</b>

SELECT HKONT GSBER BUKRS AUGDT BUDAT SHKZG

DMBTR WAERS AUGBL ZUONR GJAHR BELNR BUZEI

INTO TABLE T_BSIS_TEMP

FROM BSIS

FOR ALL ENTRIES IN T_SKB1

WHERE BUKRS EQ T_SKB1-BUKRS

AND HKONT EQ T_SKB1-SAKNR

AND BUDAT LE P_BUDAT.

<b>IF NOT T_BSIS_TEMP[] IS INITIAL.</b>

DELETE T_BSIS WHERE NOT GSBER IN S_GSBER.

T_BSIS[] = T_BSIS_TEMP[].

SORT T_BSIS BY BUKRS HKONT GSBER.

<b>ENDIF.</b>

<b>ENDIF.</b>

-

---

table definition for T_BSIS_TEMP should contain fields in given sequence

HKONT

GSBER

BUKRS

AUGDT

BUDAT

SHKZG

DMBTR

WAERS

AUGBL

ZUONR

GJAHR

BELNR

BUZEI

[Former Member]

There is nothing much you can do with the SELECT query but just take care of the below tips.

1. Check if the T_SKB1 is not initial.

2. Sort the table T_SKB1

3. Make sure to delete adjacent duplicates for Bukrs and Saknr.

Regards

anurag

[Former Member]

In addition to the others, you can move the criteria for GSBER ito the SELECT statement:

SELECT  hkont gsber bukrs augdt budat shkzg
        dmbtr waers augbl zuonr gjahr belnr buzei
  INTO TABLE t_bsis_temp
  FROM bsis
  FOR ALL ENTRIES IN t_skb1
  WHERE bukrs EQ t_skb1-bukrs
    AND hkont EQ t_skb1-saknr
    AND budat LE p_budat
    AND gsber IN s_gsber.

IF sy-subrc = 0.
  t_bsis[] = t_bsis_temp[].
  SORT t_bsis BY bukrs hkont gsber.
ENDIF.

Rob

[Former Member]

If Performance is still an issue then you might want to consider creation of a new index on the bsis table for fields BUKRS, HKONT and BUDAT.

Nanda

[Former Member]

Apart from all the above solutions.

i broke down the data into logical units of 400 and processed it.

Thank you all.

[Former Member]

Hi,

From the driver table you may delete all the adjacent duplicates comparing all fields. While using for all entries pl make sure the driver table is not empty .

eg.

IF NOT T_SKB1[] IS INITIAL.

Select Stmt

Endif.

If helpful pl reward.

Cheers....