topic Re: Query Optimisation in Application Development and Automation Discussions

Query Optimisation

Former Member — Wed, 08 Nov 2006 11:39:48 GMT

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.

Re: Query Optimisation

Former Member — Wed, 08 Nov 2006 11:44:43 GMT

IF NOT T_SKB1[] IS INITIAL.

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 NOT T_BSIS_TEMP[] IS INITIAL.

DELETE T_BSIS WHERE NOT GSBER IN S_GSBER.

T_BSIS[] = T_BSIS_TEMP[].

SORT T_BSIS BY BUKRS HKONT GSBER.

ENDIF.

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

Re: Query Optimisation

Former Member — Wed, 08 Nov 2006 11:58:33 GMT

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

Re: Query Optimisation

Former Member — Wed, 08 Nov 2006 14:51:15 GMT

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

Re: Query Optimisation

Former Member — Wed, 08 Nov 2006 15:49:41 GMT

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

Re: Query Optimisation

Former Member — Tue, 28 Nov 2006 09:17:35 GMT

Apart from all the above solutions.

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

Thank you all.

Re: Query Optimisation

Former Member — Tue, 28 Nov 2006 09:52:04 GMT

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