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?