2
\$\begingroup\$

The code runs through multiple tables to perform a count for a given ID. the ID is given by the user and then the program would look through all of the tables to check how many records each table has, that also has that ID. This is then returned to the user in an ALV with the table ID, the table Name and the number of records.

The program is run an pretty large sized tables and takes about 4 minutes to finish. Since I am fairly new with ABAP and OpenSQL I was wondering how this query could be improved to boost performance. I tried first checking if the ID exists in the table with SELECT SINGLE, and if it exists launch the count but that only made it slower with about a minute.

LOOP AT lt_where_used_data_of_coll ASSIGNING <fs_curr_tab>.

  SELECT ddtext AS table_description
  FROM dd02t
  INTO <fs_curr_tab>-table_description
  WHERE tabname = <fs_curr_tab>-table_id AND ddlanguage = 'EN'.
  ENDSELECT.

  lv_curr_table_name = <fs_curr_tab>-table_id.

        SELECT COUNT(*) AS nr_of_records
        FROM (lv_curr_table_name)
        INTO <fs_curr_tab>-nr_of_records
        WHERE id = p_c_id.
ENDLOOP.

The internal table in the program that is being filled already has the ID's of the tables to search through. The first SELECT inside the loop gets the name of the table corresponding with the ID. The second SELECT statement goes through all those tables and counts the number of records that contain the ID given by the user.

How can this statement be improved to run faster?

\$\endgroup\$
  • \$\begingroup\$ Is the ID column on each of the possible source tables indexed in any way? \$\endgroup\$ – vwegert Jul 13 '17 at 6:55
  • \$\begingroup\$ No, they are not indexed. It could be done but that is not the intention. \$\endgroup\$ – Erik Jul 13 '17 at 7:24
  • \$\begingroup\$ What do you expect then? How would you expect some ABAP code to magically speed up the database processing? \$\endgroup\$ – vwegert Jul 13 '17 at 8:19
  • \$\begingroup\$ When I responded to you it seems I didn't quite understood what you mean with being indexed. After some research I found that they all of them are indeed indexed. I changed my query so that i first find which indexes I should delete from the table and load those in an internal table. With those indexes SELECT FOR ALL ENTRIES into an internal table it_records_to_delete. Then I perform DELETE table FROM itab. Thanks for making me aware what indexes are. Just the select statement went from 4minutes to 3ms :) @vwegert \$\endgroup\$ – Erik Jul 26 '17 at 12:59
1
\$\begingroup\$

Your first query will only keep 1 value for <fs_curr_tab>-table_description so you might as well go for

  SELECT SINGLE ddtext AS table_description
  FROM dd02t
  INTO <fs_curr_tab>-table_description
  WHERE tabname = <fs_curr_tab>-table_id AND ddlanguage = 'EN'.

The AS table_description is not needed since you are not using CORRESPONDING, I also do not like the hardcoded language, I would take the user language. I also prefer EQ over =

  SELECT SINGLE ddtext
  FROM dd02t
  INTO <fs_curr_tab>-table_description
  WHERE tabname EQ <fs_curr_tab>-table_id 
    AND ddlanguage EQ sy-langu.

If this is called with a large amount of tables (say over 50), then you should definitely consider reading all the table names at once with FOR ALL ENTRIES IN before the loop.

Finally, this could go horribly wrong if that table does not have an 'ID' column. If it were me I would check first the DD03L table to make sure the table does have an ID column. If I am not mistaken, DBIF_RSQL_INVALID_REQUEST is a non-catchable exception and would send the program straight into an abend.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.