Performance issue when using select count on large tables

[Former Member]

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.

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

[Sandra_Rossi]

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.

[Former Member]

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.

[Former Member]

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.

[Former Member]

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é

[Former Member]

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

[Former Member]

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

[HermannGahm]

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

[Former Member]

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

[Former Member]

>

> 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