Performance when using wildcard % in Select LIKE

Question

I have the following, horribly slow and inefficient statement where I want to select all the entries in e071 where the obj_name field CONTAINS the value of the obj_name field in tadir followed by wildcard.

LOOP AT tadir ASSIGNING <fs_tadir>.

   CONCATENATE <fs_tadir>-obj_name '%' INTO gv_obj_name.

    SELECT * FROM e071 APPENDING TABLE gt_e071 
       WHERE obj_name LIKE gv_obj_name.

ENDLOOP.

Is there any way to make this more efficient since you can't use the LIKE statement with 'For all entries in' or joins?

Answer 1

LIKE is pretty horrible for the database and since you have this like inside a loop you are likely scanning the entire E071 in every loop iteration. You should try swapping this around and selecting everything in E071 and then checking your table inside the SELECT...ENDSELECT. I know this sounds counter intuitive but it has saved me before on a custom REGUH index we built.

SELECT * FROM e071 APPENDING TABLE gt_e071 
   WHERE obj_name LIKE gv_obj_name.

  READ TABLE tadir WITH KEY.... ASSIGNING <fs_tadir>.

ENDSELECT.

As an alternative instead of selecting with a single like build a range of values and send them to a select in bulk. It will still perform badly but better.

data lt_obj_range type range of e071-obj_name.
data ls_obj_range like line of lt_obj_range.

LOOP AT tadir ASSIGNING <fs_tadir>.
   ls_obj_range-sign = 'I'.
   ls_obj_range-option = 'CP'.
   CONCATENATE <fs_tadir>-obj_name '*' INTO ls_obj_range-low.
   append ls_obj_range to lt_obj_range.

endloop.

SELECT * FROM e071 APPENDING TABLE gt_e071 
       WHERE obj_name it lt_obj_range.

Note that the DB has a limit on the size of a select statement so if there are too many items in your range you'll get a short dump so break it down into ranges of about 200-300 lines.