Performance issue in a report

[Former Member]

I have a performance issue in one report.I ran the report and find in ST05 that average time / record is more for BSID and BSID.

The respective code is as follow for this :

Is this the right approcah to improve the performance ? and can I use select single * instead of COUNT ( * ) and check sy-subrc <> 0 ,for that purpose?

LOOP AT it_with_item.

• write:/ it_with_item-belnr,

• ' ',

• it_with_item-buzei.

SELECT COUNT( * )

FROM bsid

WHERE belnr = it_with_item-belnr

AND buzei = it_with_item-buzei

AND budat IN p_budat

AND blart IN p_blart.

IF sy-dbcnt > 0.

ELSE.

SELECT COUNT( * )

FROM bsad

WHERE belnr = it_with_item-belnr

AND buzei = it_with_item-buzei

AND budat IN p_budat

AND blart IN p_blart.

IF sy-dbcnt = 0.

DELETE it_with_item.

ENDIF.

ENDIF.

ENDLOOP.

[Former Member]

I have a performance issue in one report.I ran the report and find in ST05 that average time / record is more for BSID and BSID.

The respective code is as follow for this :

Is this the right approcah to improve the performance ? and can I use select single * instead of COUNT ( * ) and check sy-subrc <> 0 ,for that purpose?

LOOP AT it_with_item.

• write:/ it_with_item-belnr,

• ' ',

• it_with_item-buzei.

SELECT COUNT( * )

FROM bsid

WHERE belnr = it_with_item-belnr

AND buzei = it_with_item-buzei

AND budat IN p_budat

AND blart IN p_blart.

IF sy-dbcnt > 0.

ELSE.

SELECT COUNT( * )

FROM bsad

WHERE belnr = it_with_item-belnr

AND buzei = it_with_item-buzei

AND budat IN p_budat

AND blart IN p_blart.

IF sy-dbcnt = 0.

DELETE it_with_item.

ENDIF.

ENDIF.

ENDLOOP.

[Former Member]

Hi,

*1.Here it is better to use SELECT SINGLE * instead of SELECT COUNT()

Because SELECT SINGLE can select existence of record.

But SELECT COUNT – can SEARCH FOR number of records matching the criteria which consumes the data base time.

2.if Number of entries r more (1000>) then go to use

Select data into itab1 from table1, data into itab2 from table2 and use read table statement and use sy-subrs

regards,

Mekala Vijay

[ThomasZloch]

Hi,

the problem is that you are not using a qualified key to access BSIS/BSAD. You should include BUKRS and KUNNR in your select to speed up processing.

Cheers

Thomas

[Former Member]

The statement about the count( * ) is correct, but I think that is not the poerformance issue.

The performance of selects is always determined by indexes and I do not see what index you statements should use.

Is it possible that you can add bukrs or is this unknown?

Check with SE11 the indexes of bsid and bsad and consider that you only use an index up to the last field which appears in your WHERE condition.

So if the first field after mandt is given, then this index will not help.

Siegfried

[Former Member]

Thanks guys,

I later on passed 'bukrs' and 'gjahr' for both BSAD and BSID and found that it inproved a lot without using single *. Anything else can I do further ?

[ThomasZloch]

well, adding BUKRS was a big step forward, now if you'd add KUNNR as well, that will improve things further.

If KUNNR is not available in IT_WITH_ITEM, try to add it and use it in the select, it will be worth it.

Cheers

Thomas

[Former Member]

Beside the index issue that you seem to have fixed now by including BUKRS and GJAHR in the where clause of the select statements, your code has 2 flaws.

1) Using select statements within loops leads to too many database accesses from the Application server to the database server. You need to reduce this by moving your selects outside the loop.

2) Never delete records from an internal table while looping through it. Instead add another field of one character length. Mark the records to be deleted during the looping and then delete the marked records using one mass deletion statement after the loop.

Look at the code below for example.

TYPES: BEGIN OF ty_bsid,

belnr TYPE bsid-belnr,

buzei TYPE bsid-buzei,

END OF ty_bsid.

DATA: w_index TYPE sy-tabix,

it_bsid TYPE HASHED TABLE OF ty_bsid

WITH UNIQUE KEY belnr buzei,

it_bsad TYPE HASHED TABLE OF ty_bsid

WITH UNIQUE KEY belnr buzei,

it_with_item LIKE TABLE OF it_with_item.

IF NOT it_with_item[] IS INITIAL.

it_with_item_tmp[] = it_with_item[].

SORT it_with_item_tmp BY belnr buzei.

DELETE ADJACENT DUPLICATES FROM it_with_item_tmp

COMPARING belnr buzei.

SELECT DISTINCT belnr

buzei

FROM bsid

INTO TABLE t_bsid

FOR ALL ENTRIES IN it_with_item_tmp

WHERE bukrs EQ <company CODE you added to the select>

AND belnr EQ it_with_item_tmp-belnr

AND gjahr EQ <year you added to the select>

AND buzei EQ it_with_item_tmp-buzei

AND budat IN p_budat

AND blart IN p_blart.

SELECT DISTINCT belnr

buzei

FROM bsid

INTO TABLE t_bsad

FOR ALL ENTRIES IN it_with_item_tmp

WHERE bukrs EQ <company CODE you added to the select>

AND belnr EQ it_with_item_tmp-belnr

AND gjahr EQ <year you added to the select>

AND buzei EQ it_with_item_tmp-buzei

AND budat IN p_budat

AND blart IN p_blart.

LOOP AT it_with_item.

w_index = sy-tabix.

READ TABLE t_bsid WITH KEY belnr = it_with_item-belnr

buzei = it_with_item-buzei

TRANSPORTING NO FIELDS.

IF sy-subrc NE 0.

READ TABLE t_bsad WITH KEY belnr = it_with_item-belnr

buzei = it_with_item-buzei

TRANSPORTING NO FIELDS.

IF sy-subrc NE 0.

• Add field DEL of type CHAR1 to internal table it_with_item

MOVE 'X' TO it_with_item-del.

MODIFY it_with_item INDEX w_index TRANSPORTING del.

ENDIF.

ENDIF.

ENDLOOP.

DELETE it_with_item WHERE del EQ 'X'.

ENDIF.

[Former Member]

run the SQL Trace, trace it a few times, tell us how long it takes and how many records are found.

For there you canb conclude whether the performance is already o.k.,

Check the explain!