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?
-
\$\begingroup\$ Is the ID column on each of the possible source tables indexed in any way? \$\endgroup\$vwegert– vwegert2017年07月13日 06:55:22 +00:00Commented Jul 13, 2017 at 6:55
-
\$\begingroup\$ No, they are not indexed. It could be done but that is not the intention. \$\endgroup\$Erik– Erik2017年07月13日 07:24:11 +00:00Commented Jul 13, 2017 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– vwegert2017年07月13日 08:19:07 +00:00Commented Jul 13, 2017 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– Erik2017年07月26日 12:59:45 +00:00Commented Jul 26, 2017 at 12:59
1 Answer 1
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.