ABAP performance vs. T-SQL performance
I have done a decent amount of coding in T-SQL but have now moved to SAP-BI development where I am learning ABAP. I am having some serious concerns to the performance I am seeing through ABAP. For example:
This code in T-SQL:
delete from TABLE1 where TABLE1.COLUMN1 not in (select TABLE2.COLUMN1 from TABLE2)
...I have been told translates to ABAP as:
loop at TABLE1 assigning <TABLE1_FS>.
read table TABLE2 assigning <TABLE2_FS> with key TABLE2.COLUMN1 =
if sy-subrc <> 0.
delete table TABLE1 from <TABLE1_FS>.
In this example, TABLE1 has 100,000 records, and TABLE2 has 100 records. The time it takes in my system for ABAP to complete this statement is around 5 minutes, while the T-SQL it runs it in just seconds.
Am I missing something here! (.....for I am going to eventually have to rewrite all of my T-SQL dev to ABAP at my company.)
Delete statements within a loop cause performance issues. A better way to write your code would be as follows.
RANGES: r_column1 FOR table2-column1. IF NOT table2 IS INITIAL. SORT table2 BY column1. DELETE ADJACENT DUPLICATES FROM table2 COMPARING column1. r_column1-sign = 'I'. r_column1-option = 'EQ'. LOOP AT table2. r_column1-low = table2-column1. APPEND r_column1. ENDLOOP. DELETE table1 WHERE NOT column1 IN r_column1. REFRESH r_column1. CLEAR r_column1. ENDIF.
Please let me know if this fixes your issue.