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?

Depending on how many entries there are in tables TADIR and E071, it may be more efficient to select all entries from E071, and then delete the ones you don't want. Also try not to use select * unless you really need all the fields. You can use transaction SAT to measure the performance of each transaction. Keep in mind that buffering may affect any subsequent runs, so make sure that you don't always run one test first, but mix them up to get better results. — Esti, Nov 01 '13 at 01:03
There's a lot of fields... Isn't Select * faster than selecting fields by name (Assuming RAM is not a limiting factor) since you don't need to look up which columns to return? — baarkerlounger, Nov 01 '13 at 02:13
Yes, if you need the majority of the fields SELECT * probably is faster, and admittedly easier to maintain. You're always treading a fine line between reducing the load on the database vs reducing the load on the network, and keeping your code maintainable. — Esti, Nov 01 '13 at 03:24
You could build an internal table with all the object names you need by going against TADIR with the repetitive wild card selects. Then you can use this table to do a FOR ALL ENTRIES select against E071. This should be faster since TADIR has every object only once while E071 can have it many times. — Gert Beukema, Nov 01 '13 at 04:26

score 2 · Accepted Answer · answered Nov 02 '13 at 03:18

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.

score 1 · Answer 2 · edited May 23 '17 at 12:30

Use the SQL trace (transaction ST05) to analyze your query. One of the major issues - apart from the fact that you're potentially throwing thousands of queries at the database - would be that you're not using any index at all, not even to perform a range scan. This will probably force the DBMS to perform thousands of full-table scans. If you would supply PGMID and OBJECT, it should speed up things quite significantly.

It might also be a good idea to restrict the number of transport requests you're looking at by using its prefix. I just checked several of our systems - depending upon the age of the system, half of the entries in E071 do not belong to transports at all. In one system, only 75,000 entries of over 4,5 million entries were created locally, the others are piece lists of support packages and the like.

From an earlier question, I've got an idea of what you're trying to achieve. Be aware that you can't rely on the main object name being at the very beginning of the partial object name. You may want to check the coding of the function module TR_CHECK_TYPE to get an idea of how partial (LIMU) entries can be mapped to entire object entries (R3TR). However, I don't know of a function module that would work the opposite direction.

I would not worry about selecting individual fields instead of SELECT * at this stage. Contrary to what you might read in other responses or comments, E071 is a relatively narrow table with very few fields, and you already need the largest fields in your query. There's probably very little to gain by only selecting individual fields.

This loop and select statement is specifically just for class method objects - in our system at least this pattern seems to work for that scenario - limiting by prefix sounds like a good idea though — baarkerlounger, Nov 01 '13 at 07:44
You should worry about SELECT * when querying TADIR as this is a wide table. Moreover, in a column store architecture you should always worry about it. — hennes, Nov 01 '13 at 08:38
@hennes: If you read the question carefully, you will notice that he is NOT querying TADIR but E071. — vwegert, Nov 01 '13 at 11:17
I know but I assume that at some point he must fill the internal table tadir which he loops over. — hennes, Nov 01 '13 at 11:30

Performance when using wildcard % in Select LIKE

2 Answers2