topic Re: Performance issue with BSAS table in Application Development and Automation Discussions

Performance issue with BSAS table

Former Member — Tue, 06 Feb 2007 12:30:40 GMT

Hi,

I am considering 1 lac gls for BSAS selection. It is giving runtime error DBIF_RSQL_INVALID_RSQL and exception CX_SY_OPEN_SQL_DB.

To overcome this issue i used the followiing code :

DO.

PERFORM f_make_index USING sy-index.

REFRESH lr_hkont.

CLEAR lr_hkont.

APPEND LINES OF gr_hkont FROM gv_from TO gv_to TO lr_hkont.

IF lr_hkont[] IS INITIAL.

EXIT.

ENDIF.

SELECT bukrs hkont gjahr belnr buzei budat augbl augdt waers wrbtr

dmbtr dmbe2 shkzg blart FROM bsas

APPENDING CORRESPONDING FIELDS OF TABLE

gt_bsas

FOR ALL ENTRIES IN gt_bsis

WHERE bukrs = gt_bsis-bukrs

AND hkont IN lr_hkont

AND gjahr = gt_bsis-gjahr

AND augbl = gt_bsis-belnr

and budat = gt_bsis-budat.

enddo.

I am passing 500 gls for each BSAS selection and appending to GT_BSAS internal table. This code it is taking 50 hours to fetch the data.

Please suggest me how to improve the performance of the report.

Thanks,

Re: Performance issue with BSAS table

Former Member — Tue, 06 Feb 2007 12:44:11 GMT

first get all the G/L Account then put like this

if gt_bsis[] is not initial.

SELECT bukrs hkont gjahr belnr buzei budat augbl augdt waers wrbtr

dmbtr dmbe2 shkzg blart FROM bsas

into CORRESPONDING FIELDS OF TABLE

gt_bsas

FOR ALL ENTRIES IN gt_bsis

WHERE bukrs = gt_bsis-bukrs

AND hkont IN lr_hkont

AND gjahr = gt_bsis-gjahr

AND augbl = gt_bsis-belnr

and budat = gt_bsis-budat.

endif.

Re: Performance issue with BSAS table

Former Member — Tue, 06 Feb 2007 13:57:34 GMT

HI,

I am checking gt_bsis[] not initial and then writing the select query.

Thanks,

Re: Performance issue with BSAS table

Former Member — Tue, 06 Feb 2007 14:03:15 GMT

Hi,

1. check whether the SELECT inside the DO statement is required. this shud be the culprit.

2. In the SELECT query avoid using APPENDING CORRESPONDING TABLES caluse. directly populate into another internal table and append it later.

3. check whether the internal table gt_bsis is initial before using FOR ALL ENTRIES in the select query. check if it has duplicate entries too. if it has....delete the duplictaes.

regards,

madhu

Re: Performance issue with BSAS table

Former Member — Tue, 06 Feb 2007 14:07:58 GMT

Inside DO ENDDO select statement is required because I am spliting the GLs into 500 and appending it to internal table gt_bsas.

And also checked gt_bsis is not initial.

Thanks,

Re: Performance issue with BSAS table

Former Member — Tue, 06 Feb 2007 14:17:54 GMT

hi,

pls check the exit condition of the DO ENDDO loop.

u r appending the lines to lr_hkont table and checking if its initial for EXIT condition.

regards,

madhu

Re: Performance issue with BSAS table

Former Member — Tue, 06 Feb 2007 14:30:30 GMT

You haven't shown how you fill gt_bsis, but I assume you are just using lr_hkont. If that is the case, why don't you use the same criteria against bsas. GT_BSIS will have many duplicate records (unless you have already deleted duplicates) and I think that is slowing it down.

Rob