ABAP performance vs. T-SQL performance

[dustin_self2]

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 =

<TABLE1_FS>-COLUMN1.

if sy-subrc <> 0.

delete table TABLE1 from <TABLE1_FS>.

endif.

endloop.

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.)

Regards,

Dustin

[Former Member]

This should help:

read table TABLE2 assigning <TABLE2_FS> with key
  TABLE2.COLUMN1 = <TABLE1_FS>-COLUMN1 
  BINARY SEARCH.                                                "<======

But it's not clear from your post which are database tables and which are internal tables.

Rob

Message was edited by:

Rob Burbank

[dustin_self2]

In ABAP, they are all internal tables....

Thanks for the code, I will try it out to see....

Dustin

[Former Member]

Make sure TABLE2 is sorted by COLUMN1 before doing the read.

Rob

[dustin_self2]

It is a sorted table, and I put the "binary search" into the code....but it still takes 5 minutes to run.....

Dustin

[Former Member]

Your problem lies elsewhere. There is no way in the world that this should take so long. Would you post your <b>ABAP</b> code please?

Rob

[Former Member]

Hi Dustin,

You might want to try Hash table on TABLE2.

You can't compare T-SQL with internal table processing in ABAP. Database tables in SAP are accessed and manipulated in much the same way as T-SQL.

It would be easier to help if we knew what data is in your tables and how they were loaded as it might be possible to load your TABLE1 from the database with the correct data.

But the performance you are getting is about right. You won't get performance in seconds no matter how you cut it.

Hope this helps.

Fil

[chaouki_akir]

Hello,

if TABLE1 and TABLE2 are two internal table, you can try :

Prerequisite : you must add a field named 'FLAG' to table1.

DATA wv_idx LIKE sy-tabix VALUE 1.

SORT                            table1        BY column1.
SORT                            table2        BY column1.
DELETE ADJACENT DUPLICATES FROM table2 COMPARING column1.
LOOP AT table2.
         LOOP AT table1 FROM wv_idx.
                  IF  table1-column1 GT  table2-column1.
                       MOVE sy-tabix TO wv_idx. EXIT.
                  ELSE.
                  IF  table1-column1 EQ  table2-column1.    
                       MOVE 'X'         TO table1-flag." COLUMN1 value exist in TABLE2
                       MODIFY table1 TRANSPORTING flag.               
                  ENDIF. ENDIF.
         ENDLOOP.
ENDLOOP.
DELETE table1 WHERE NOT flag EQ 'X'.

[Former Member]

Dustin,

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.

[dustin_self2]

Thanks! The concept of range tables worked great! It ran that delete statement in just seconds. I really appreciate your help.

Regards,

Dustin

[former_member194613]

> In this example, TABLE1 has 100,000 records, and TABLE2 has 100 records.

It should be obvious how to solve the problem with that distribution of entries.

MOVE the 100 lines from table1 into a new table!

What type is table1, if sorted then o.k., otherwise if memory is no problem (if table1 is not too wide) then move table1 into hash1

LOOP AT table2 INTO wa2.

READ TABLE table1

INTO wa1

WITH TABLE KEY coulmn1 = wa2-column1.

IF (sy-subrc eq = 0 ).

APPEND wa1 to new_table1.

ENDIF.

ENDLOOP.

REFRESH table1.

table1[] = new_table1[].

Siegfried

[former_member194613]

.... ranges is only a good option as long as table 2 is small.

If your setting changes, and table 2 has several 1000 entries, then it might not work anymore.