Performance problem

[Former Member]

The logic that the below code tries to accomplish is that it selects the data from a z table and deletes all the data except the most recent two records for every key combination of the sales_grp, cust_pgrp, cust_grp5 and product .

I have this piece of code which I would like to fine tune because it is running forever (more than 8 hrs in background) becuase the table has 2.5 million records.

****************************************************************************************

*Get the data from production/staging tables

SELECT *

INTO TABLE t_itab

FROM ztable

WHERE interface_id = p_infid AND

eff_date LE l_date.

IF sy-subrc = 0 AND NOT t_itab[] IS INITIAL.

SORT t_itab DESCENDING BY sales_org

cust_pgrp

cust_grp5

product

eff_date

created_date

created_time.

LOOP AT t_itab INTO wa_itab.

LOOP AT t_itab INTO wa_itab1 WHERE

sales_org = wa_itab-sales_org AND

cust_pgrp = wa_itab-cust_pgrp AND

cust_grp5 = wa_itab-cust_grp5 AND

product = wa_itab-product.

CHECK sy-tabix GT 2. "keep the most recent two records

*Delete invalid entries which no longer needed

APPEND wa_itab1 TO t_delete.

ENDLOOP.

DELETE t_itab WHERE sales_org = wa_itab-sales_org AND

cust_pgrp = wa_itab-cust_pgrp AND

cust_grp5 = wa_itab-cust_grp5 AND

product = wa_itab-product.

ENDLOOP.

****************************************************************************************

Ideas?

[Former Member]

Hi Srinivas ,

A> Do as follows :

1 > create a secondary index and use that in your select query (by putting the fields in where clause exactly in the same sequence as that of they appear the index you create)

2 > Make use of PACKAGE statement in your select query so that it will process eg 1000 records at a time instead of 2.5 million records in one shot. This will definetly improve the performance to greater extend.

3 > Please see do you really need all the fields to be fetched from z table , if not then replace select * with select fields list.

4 > Also see if instead of only one condition for date if you can put range of date. Eg

instead of

eff_date LE l_date ,

see if you can have something like below

eff_date IN BETWEEN DATE1 and DATE2 .

B > I think your code is doing nothing but than keeping the first 2 records intact and deleting the rest all. If thats true then see the f1 help on delete with where condition and see if you can directly have this accomplished with dete statement on transparent tables.

See this

DELETE FROM transperenttable_name

WHERE zflag <> X .

Have one field added to your transparent table and while populating records in that table see if you can set it to X for first 2 records in that. and use above logic to delete all except first 2.

Regards,

Uma

Edited by: UmaDave on Jun 10, 2010 6:17 AM

Edited by: UmaDave on Jun 10, 2010 6:18 AM

Edited by: UmaDave on Jun 10, 2010 6:22 AM

[Former Member]

Remove the nested loops it is not required.

Use control break statement to have the check for the two recent reocrds. I hope thats the case.

No need of separate internal table for t_delete here .

Just to give u some idea ..

logic should be like this .

sort the entries ..

loop at entries .

check for the combinaiton of

sales_org cust_pgrp cust_grp5 entries

if ok for two entries leave the ztable "check for case of one record also

else.

delete the entries there after for the comination of sales_org cust_pgrp cust_grp5 entries

endif.

endloop.

use ctrl break statements at new to check the combination by setting some flag and holding the count of value for two and more than two conditon.

you can achieve this with a single loop .

[Former Member]

You can try using parralel cursor.

SELECT *

INTO TABLE t_itab

FROM ztable

WHERE interface_id = p_infid AND

eff_date LE l_date.

IF sy-subrc = 0 AND NOT t_itab[] IS INITIAL.

SORT t_itab DESCENDING BY sales_org

cust_pgrp

cust_grp5

product.

LOOP AT t_itab INTO wa_itab.

READ TABLE t_itab INTO wa_itab1

WITH KEY sales_org = wa_itab-sales_org

cust_pgrp = wa_itab-cust_pgrp

cust_grp5 = wa_itab-cust_grp5

product = wa_itab-product

BINARY SEARCH.

IF sy-subrc = 0.

l_index = sy-tabix.

LOOP AT t_itab INTO wa_itab1 from l_index.

IF wa_itab1-sales_org NE wa_itab-sales_org

wa_itab1-cust_pgrp NE wa_itab-cust_pgrp

wa_itab1-cust_grp5 NE wa_itab-cust_grp5

wa_itab1-product NE wa_itab-product.

EXIT.

ELSE.

CHECK sy-tabix GT 2.

wa_itab1-flag = 'X'.

MODIFY t_itab FROM wa_itab1 INDEX sy-tabix.

ENDIF.

ENDLOOP.

ENDLOOP.

DELETE TABLE t_itab1 where flag = 'X'.

Edited by: Mrinmoy Dutta on Jun 10, 2010 8:11 AM

[Former Member]

Solved it like this:

DATA lv_count TYPE sytabix.

CLEAR lv_count. REFRESH t_delete.

LOOP AT t_itab INTO wa_itab.

IF wa_itab1-sales_org = wa_itab-sales_org AND

wa_itab1-cust_pgrp = wa_itab-cust_pgrp AND

wa_itab1-cust_grp5 = wa_itab-cust_grp5 AND

wa_itab1-product = wa_itab-product.

lv_count = lv_count + 1.

IF lv_count > 2.

DELETE t_itab INDEX sy-tabix.

APPEND wa_itab TO t_delete.

ENDIF.

ELSE.

lv_count = 1.

wa_itab1 = wa_itab.

ENDIF.

ENDLOOP.

Thank you for all your suggestions .