topic Re: Table query performance improvement in Application Development and Automation Discussions

Table query performance improvement

former_member709662 — Thu, 19 Jan 2023 15:38:30 GMT

Hi All,

I would like to make one querry about them to improve performance. The table has 93 million records:

SELECT COUNT(*) INTO lv_erdk_count_one FROM erdk WHERE items_archived = '1'.
SELECT COUNT(*) INTO lv_erdk_count_two FROM erdk WHERE items_archived = '2'.
SELECT COUNT(*) INTO lv_erdk_count_three FROM erdk WHERE items_archived = '3'.
SELECT COUNT(*) INTO lv_erdk_count_four FROM erdk WHERE items_archived = '4'.
SELECT COUNT(*) INTO lv_erdk_count_five FROM erdk WHERE items_archived = '5'.

Is it possible to make one query?

Thank your help in advance!

Re: Table query performance improvement

shantraj6 — Thu, 19 Jan 2023 16:12:20 GMT

SELECT COUNT(*) From erdk INTO (  lv_erdk_count_one,  
				  lv_erdk_count_two,  
				  lv_erdk_count_three,  
				  lv_erdk_count_four,  
				  lv_erdk_count_five  ) 
	where items_archived = '1' and
	      items_archived = '2' and
	      items_archived = '3' and 
	      items_archived = '4' and 
	      items_archived = '5'.

Check this.

Re: Table query performance improvement

jmodaal — Thu, 19 Jan 2023 16:26:25 GMT

Hello,

this would return no records. No record can fulfill the criteria items_archived = '1' and items_archived = '2'...

I guess the above given 5 statements can not be covered with a single statement. My first though on this was using the ':' for chained statements (one might think it looks a little bit cleaner).

select:
  count( * ) into @lv_erdk_count_one from ERDK where items_archived = '1',
  count( * ) into @lv_erdk_count_two from ERDK where items_archived = '2',
  count( * ) into @lv_erdk_count_three from ERDK where items_archived = '3',
  count( * ) into @lv_erdk_count_four from ERDK where items_archived = '4',
  count( * ) into @lv_erdk_count_five from ERDK where items_archived = '5'.

But also with this notation there will be 5 separated statements - no gain regarding performance improvement 😞

Kind regards

Jan

Re: Table query performance improvement

former_member709662 — Thu, 19 Jan 2023 16:35:35 GMT

I got error message: The field list and the INTO list must have the same number of elements.

😞

Re: Table query performance improvement

Maciej_DomagaBa — Thu, 19 Jan 2023 16:59:05 GMT

data:
  begin of itab occurs 5,
    items_archived  type ERDK-items_archived,
    counter         type i,
  end of itab.

select 
  items_archived 
  count(*) 
  into table itab 
  from ERDK 
  where items_archived in ('1','2','3','4','5') 
  group by items_archived.

Re: Table query performance improvement

former_member709662 — Thu, 19 Jan 2023 20:28:51 GMT

Thank you maciej.domagaa! Put a smile on my face 🙂
Query be faster 🙂 (17 sec to 5 sec)

Re: Table query performance improvement

Maciej_DomagaBa — Fri, 20 Jan 2023 13:26:28 GMT

You're welcome.

If you want to speed it up even more, you may consider creating your own index on that database table, an index with fields: MANDT and ITEMS_ARCHIVED (tcode SE11 -> button Indexes...) - it could make sense if you are going to use that query pretty often.

regards