2012 Jun 25 7:14 PM
Hi Experts,
I’m trying to improve the performance of an interface. This requires a check on KNVV internal table based it’s KUNNR value. I need to delete all KUNNR in KNA1 internal table if they are not found in KNVV table. I’ve accomplished this task if I use loop and work area. But performance is my concern and that’s why I tried to use DELETE statement (as shown below) to remove all customers from KNA1 which are not found in KNVV. However, the following piece of code deletes everything. Can somebody point out what’s wrong with the following DELETE statement OR I must have to use loop and workarea to achieve my goal.
delete p_li_data_kna1
where kunnr eq p_li_data_knvv-kunnr.
if sy-subrc eq 0.
sort p_li_data_kna1 by kunnr.
endif.
For comparison of two table I've attached a screen shot. (In this example I want to delete '0000010668' from LI_DATA_KNA1)
Many thanks in advance.
2012 Jun 25 7:53 PM
I would suggest making use of an extra field MARK and set it to 'X' for all the rows to be deleted rather than deleting in a loop.
And once out of the loop Delete all entries where MARK = 'X'.
Regards,
Shyam
2012 Jun 25 7:34 PM
Hi,
You are expecting it to work like 'FOR ALL ENTRIES' for a select. This is an incorrect usage. 'p_li_data_knvv-kunnr' in your statement represents the header of internal table p_li_data_knvv. Instead loop at your KNA1 internal table. READ KNVV internal table (Sorted and then binary search addition preferred) and then delete.
Regards,
Shravan
2012 Jun 25 7:45 PM
Hi Shravan,
Many thanks for your input. I understand my mistake now. But as per your suggestion READ KNVV will only work for a single record. What if I've 50 row to delete in KNA1 which do not exit in KNVV.
2012 Jun 25 7:53 PM
I would suggest making use of an extra field MARK and set it to 'X' for all the rows to be deleted rather than deleting in a loop.
And once out of the loop Delete all entries where MARK = 'X'.
Regards,
Shyam
2012 Jun 25 8:08 PM
Many thanks guys, I've been able to write the following code to accomplish my task.
form F_CHECK_PI_KNA1 tables p_li_data_kna1 type tt_data_kna1
p_li_data_knvv type tt_data_knvv.
data: wa_cust_knvv type t_cust_knvv,
wa_cust_kna1 type t_cust_knb1.
if lines( p_li_data_kna1 ) gt 0 and lines( p_li_data_knvv ) gt 0.
loop at p_li_data_knvv into wa_cust_knvv.
read table p_li_data_kna1 into wa_cust_kna1
with key kunnr = wa_cust_knvv-kunnr
BINARY SEARCH.
if sy-subrc ne 0.
delete p_li_data_kna1
where kunnr eq wa_cust_kna1-kunnr.
endif.
endloop.
if sy-subrc eq 0.
sort p_li_data_kna1 by kunnr.
endif.
endif.
endform.
I can not find a way where I can avoid using Loop (which is my concern). Any suggestions how I can improve the above piece of code.
2012 Jun 25 8:53 PM
Hi,
"sort p_li_data_kna1 by kunnr" should be before the read statement. Read ... Binary search without sort will give wrong results. Put it just before the 'loop at p_li_data_knvv'.
Regards,
Shravan
2012 Jun 25 8:54 PM
You can avoid multiple DELETE statements, by getting it out of the loop
form F_CHECK_PI_KNA1 tables p_li_data_kna1 type tt_data_kna1
p_li_data_knvv type tt_data_knvv.
data: wa_cust_knvv type t_cust_knvv,
wa_cust_kna1 type t_cust_knb1.
if lines( p_li_data_kna1 ) gt 0 and lines( p_li_data_knvv ) gt 0.
loop at p_li_data_knvv into wa_cust_knvv.
CLEAR wa_cust_knvv.
read table p_li_data_kna1 into wa_cust_kna1
with key kunnr = wa_cust_knvv-kunnr
BINARY SEARCH.
if sy-subrc ne 0.
wa_cust_knvv-MARK = 'X'.
MODIFY p_li_data_knvv FROM wa_cust_knvv TRANSPORTING MARK.
endif.
endloop.
if sy-subrc eq 0.
sort p_li_data_kna1 by kunnr.
endif.
endif.
endform.
DELETE p_li_data_knvv WHERE MARK EQ 'X'.
Regards,
Shyam
2012 Jun 25 9:16 PM
Many thanks guys for such a great help. Your suggestions have solved my issue and more importantly I've learned something new today
2012 Jun 25 9:23 PM
Hi zero,
first thing to do: No LOOP INTO anymore. Replace with LOOP ASSIGNING <field-symbol>.
Use sorted or hashed tables to boost performance, try this:
FIELD-SMBOLS:
<data_knvv> like line of p_li_data_knvv,
<data_kna1> like line of p_li_data_kna1.
DATA:
lt_kunnr TYPE SORTED TABLE OF knvv-kunnr
WITH UNIQUE KEY table_line.
LOOP AT p_li_data_knvv ASSIGNING <data_knvv>.
INSERT <data_knvv>-kunnr INTO TABLE lt_kunnr.
ENDLOOP.
LOOP AT p_li_data_kna1 ASSIGNING <data_kna1>.
READ TABLE lt_kunnr TRANSPORTING NO FIELDS
WITH TABLE KEY table_line = <data_kna1>-kunnr.
CHECK SY-SUBRC <> 0.
DELETE p_li_data_kna1.
ENDLOOP.
Still, it depends on the number of rows to be kept/deleted. You may also put the KUNNR in a sorted range and then DELETE p_li_data_kna1 WHERE NOT KUNNR IN range.
Looks like
FIELD-SMBOLS:
<data_knvv> like line of p_li_data_knvv,
<data_kna1> like line of p_li_data_kna1,
DATA:
lt_r_kunnr TYPE RANGE OF knvv-kunnr,
ls_r_kunnr LIKE LINE OF lt_r_kunnr,
lt_r_kunnr_s LIKE SORTED TABLE OF ls_r_kunnr
WITH UNIQUE KEY table_line.
ls_r_kunnr = 'IEQ'.
LOOP AT p_li_data_knvv ASSIGNING <data_knvv>.
ls_r_kunnr = <data_knvv>-kunnr.
INSERT ls_r_kunnr INTO TABLE lt_r_kunnr_s .
ENDLOOP.
DELETE p_li_data_kna1 WHERE NOT kunnr IN lt_r_kunnr_s .
But RANGES are evaluated comparably slow if they have more lines.
Try what works for you. Both is faster than any LOOP INTO.
Regards,
Clemens
2012 Jun 25 9:28 PM
Many thanks for excellent examples Clemens. Certainly I will try them