topic Re: Performance issue when using select count on large tables in Application Development and Automation Discussions

Performance issue when using select count on large tables

Former Member — Sat, 12 Sep 2009 10:26:26 GMT

Hello Experts,

I have a requirement where i need to get count of data from a database table.Later on i need to display the count in ALV format.

As per my requirement, I have to use this select count inside a nested loops.

Below is the count snippet:

LOOP at systems assigning <fs_sc_systems>.

LOOP at date assigning <fs_sc_date>.

SELECT COUNT( DISTINCT crmd_orderadm_i~header )

FROM crmd_orderadm_i

INNER JOIN bbp_pdigp

ON crmd_orderadm_i_{client EQ bbp_pdigp}client "MANDT is referred as client

AND crmd_orderadm_i_{guid EQ bbp_pdigp}guid

INTO w_sc_count

WHERE crmd_orderadm_i~created_at BETWEEN <fs_sc_date>-start_timestamp

AND <fs_sc_date>-end_timestamp

AND bbp_pdigp~zz_scsys EQ <fs_sc_systems>-sys_name.

endloop.

In the above code snippet,

<fs_sc_systems>-sys_name is having the system name,

<fs_sc_date>-start_timestamp is having the start date of month

and <fs_sc_date>-end_timestamp is the end date of month.

Also the data in tables crmd_orderadm_i and bbp_pdigp is very large and it increases every day.

Now,the above select query is taking a lot of time to give the count due to which i am facing performance issues.

Can any one pls help me out to optimize this code.

Thanks,

Suman

Re: Performance issue when using select count on large tables

Sandra_Rossi — Sun, 13 Sep 2009 20:18:00 GMT

Try only one select (remove the loops), and use a cursor so that to read by package into a first internal table with the system, timestamp and the guid. For each package, collect this internal table into another internal table made of system, time interval, count.

Re: Performance issue when using select count on large tables

Former Member — Mon, 14 Sep 2009 03:38:46 GMT

Hi Suman,

Build you logic in such a way that you select all the records from the database table in one go into an internal table.

And then you read this internal table to get the count of desired records.

This is surely give improvement in performance.

Re: Performance issue when using select count on large tables

Former Member — Mon, 14 Sep 2009 04:53:27 GMT

use for all entries on one table on which you are looping and for that date you can create a range.

remove those two loops.

and its better not to keep nonkey elements at all in the where conditions. because for each nonkey element it will any ways hit each and every lines of database table.

fetch the records and filter them in a loop.

Re: Performance issue when using select count on large tables

Former Member — Tue, 22 Sep 2009 21:38:43 GMT

Hi Choudhary Suman ,

Try this:

SELECT crmd_orderadm_i~header

INTO it_header " interna table

FROM crmd_orderadm_i

INNER JOIN bbp_pdigp

ON crmd_orderadm_i_{client EQ bbp_pdigp}client

AND crmd_orderadm_i_{guid EQ bbp_pdigp}guid

FOR ALL ENTRIES IN date

WHERE crmd_orderadm_i~created_at BETWEEN date-start_timestamp

AND date-end_timestamp

AND bbp_pdigp~zz_scsys EQ date-sys_name.

SORT it_header BY header.

DELETE ADJACENT DUPLICATES FROM it_header

COMPARING header.

describe table it_header lines v_lines.

Hope this information is help to you.

Regards,

José

Re: Performance issue when using select count on large tables

Former Member — Wed, 23 Sep 2009 07:53:17 GMT

Your issue has nothing to do with the COUNT(*), I see the usual 2 problems:

There is a nested loop with 2 full tables no conditions, how many lines are in each of the two internal tables,

do you really need all combinations?

Most important, I don't think that there are proper indices for your select statement, i.e. the join. Therefore it is slow.

=> Combine the two internal tables into one, which really holds the combinations you need.

=> Do the SELECT with an FOR ALL ENTRIES

=> Most important, take care about the indices, either you must create indices or if your application is one time execution

you must create a batch program which will need its time.

Siegfried

Re: Performance issue when using select count on large tables

Former Member — Wed, 23 Sep 2009 09:22:44 GMT

Hello All,

I cant merge the 2 loops into one.Also when i checked in debug mode select count for taking a bit time.Anyways,, now i have sceduled my report for background running.

Thanks for all ur response.

Suman

Re: Performance issue when using select count on large tables

HermannGahm — Wed, 23 Sep 2009 10:30:33 GMT

Hi,

if you really need all combinations you could still try to use FAE (FOR ALL ENTRIES) in order to reduce the number of database calls. And checking the execution plan for tuning potential (with and without FAE) is a must.

Kind regards,

Hermann

Re: Performance issue when using select count on large tables

Former Member — Thu, 24 Sep 2009 05:47:46 GMT

Hi Suman,

If you can perform a SELECT inside two LOOP's you can use the two LOOP's to fill a new internal table with the fields for date and system. After that use this internal table as parameter of FOR ALL ENTRIES on suggested SELECT.

Another issue on your SELECT is index, there's no standard index on table crmd_orderadm_i for field created_at, you need to create a Z index for it go faster.

The best way to check this table/index access is use ST05. There's a explanation about how use it on the stick on this forum.

Regards, Fernando Da Ros

Re: Performance issue when using select count on large tables

Former Member — Thu, 24 Sep 2009 13:05:02 GMT


> Thanks for all ur response.

If your question is answered, please assign po(i)nts to the helpful answers and mark this as closed.

Rob