Regarding Performance pbl in SQL query.

[Former Member]

hey guys,

Could somebody help me over this posting.

I have created TAX report. which retrieved records from BSIS and BSAS table to calculate consumption tax.

From SE30 and SQL trace i found that loop at BSIS and BSAS takes round trip

for each Input Period value.

could you please give me best coding alternate to my below coding part.

*Main code--

---

loop at itab_t001.(itab_t001-bukrs is company code list,for each company code)

WK_MONAT = '01'.

DO.

IF WK_MONAT = P_MONAT. "p_monat is parameter input for period

FLG_CUR_PERIOD = CNS_TRUE.

ENDIF.

PERFORM FRM_READ_DATA. " RETRIEVE DATA FROM BSIS AND BSAS

WK_MONAT = WK_MONAT + 1.

IF WK_MONAT > P_MONAT.

EXIT.

ENDIF.

ENDDO.

-

---

END of Main code----

---

&----

---

• Read FI documents from table BSIS/BSAS

----

---

FORM FRM_READ_DATA.

• Select data from table BSIS

SELECT BELNR BUZEI BLART MWSKZ HKONT SHKZG DMBTR

INTO CORRESPONDING FIELDS OF TABLE ITAB_BSIS

FROM BSIS WHERE BUKRS = itab_t001-BUKRS AND GJAHR = P_GJAHR AND MONAT = WK_MONAT AND

( MWSKZ <> ' ' AND MWSKZ <> '**' ).

• Select data from table BSAS

SELECT BELNR BUZEI BLART MWSKZ HKONT SHKZG DMBTR

APPENDING CORRESPONDING FIELDS OF TABLE ITAB_BSIS

FROM BSAS WHERE BUKRS = itab_t001-BUKRS AND GJAHR = P_GJAHR AND MONAT = WK_MONAT AND

( MWSKZ <> ' ' AND MWSKZ <> '**' ).

• Select data from table BSIS that includes non-consumption tax accounts

SELECT BELNR

INTO TABLE ITAB_NO_TAX_DOC

FROM BSIS WHERE BUKRS = itab_t001-BUKRS AND GJAHR = P_GJAHR AND

MONAT = WK_MONAT AND HKONT IN S_NT_ACC.

• Select data from table BSAS that includes non-consumption tax accounts

SELECT BELNR APPENDING TABLE ITAB_NO_TAX_DOC

FROM BSAS WHERE BUKRS = itab_t001-BUKRS AND GJAHR = P_GJAHR AND

MONAT = WK_MONAT AND HKONT IN S_NT_ACC.

...Proceeded with ABOVE fetched internal tables.

ENDFORM.

-

---

Note:

I found that BSIS and BSAS table have 10000s of records it takes 3hrs time to run this report(if i give 12months period). (for each month(ie 01month report takes 15minutes to execute.)

so i think instead calling BSiS twice, calling BSIS ones is wise.

could you pls help me how can i modify the code and should i have

to created any index here(if so mention how and which field)

pls feel free to ask if any addition code is required.

could you pls confirm.

ambichan.

Message was edited by: ambi chan

[Former Member]

Can somebody take over this message!could be more apprecialble.

ambichan

[Former Member]

Hi Ambichan,

Few things that can be done to the below code.

1. Dont put select queries inside the "loop at itab_t001....endloop" statement.

Instead use <b>FOR ALL ENTRIES</b> IN itab_t001 for the queries. Then you can use READ stmt to get the values.

NOTE: Make sure that you select all <b>key fields</b> from the databse when using FOR all Entries.

<i>This will avoid hitting of the DB table multiple time.</i>

2. Avoid "INTO CORRESPONDING FIELDS OF TABLE" in select queries. Modify the internal table to suit the strucutre of the databse table.

<i>This will make the query faster</i>

3. Make sure that the fields in the select query are in the same order as that in DB table.

<i>This will also make the query faster</i>

Try these changes and performance should increase.

Regards,

Saji.