topic Re: select count (*) in Application Development and Automation Discussions

select count (*)

Former Member — Tue, 11 Oct 2011 12:36:18 GMT

Observing regularly the rows tables we need a program. We want to have a short list of trhe rows entry.

A test program is the following, it delivers what I want but is very slow.

data: rows type i,

rows_1 type i,rows_2 type i,rows_3 type i.

START-of-SELECTION.

select count(*) from RSPCINSTANCE into rows.

select count(*) from RSBERRORLOG into rows_1.

select count(*) from RSRWBSTORE into rows_2.

select count(*) from RSDDSTATAGGRDEF into rows_3.

write: rows,

/ rows_1,

/ rows_2,

/ rows_3,

Do you have any suggestion to speed that statement?

Regards,

Re: select count (*)

Former Member — Tue, 11 Oct 2011 13:47:53 GMT

Hi,

You can already add a SINGLE in front of count:


select SINGLE count(*) from RSPCINSTANCE into rows.

added: my bad, this won't speed-up the process...

Kr,

Edited by: Manu D'Haeyer on Oct 11, 2011 3:51 PM

Re: select count (*)

Former Member — Tue, 11 Oct 2011 13:50:34 GMT

Dear Henning,

I believe you just want to count the total number of rows for each table, right ?

So no use of WHERE clause or INDEXES, am I correct ?

If that is the case, then I would suggest you have this program run on a background job and storing the results somewhere (on a Z table for example.)

You would then read the counters directly from that Z table.

It wouldn't be online data, I know, but could be a solution for your problem nevertheless.

Kind Regards

/Ricardo Quintas

Re: select count (*)

yuri_ziryukin — Tue, 11 Oct 2011 15:11:29 GMT

Henning,

what is a business case for counting records in these tables?

RSPCINSTANCE - Generic Instance Storage (for process chains?)

RSBERRORLOG - Logs for Incorrect Records

RSRWBSTORE - Storage for binary large objects (Excel workbooks)

RSDDSTATAGGRDEF - Statistics data OLAP: Navigation step / aggregate definition

The RSBERRORLOG table looks like an error log table that should be regularly cleaned.

Regarding RSRWBSTORE, I cannot figure out the idea why someone need to regularly count entries in this table.

Do you also need an absolute exact number of entries? If not, you can try to use the database statistics information to quickly get the number or rows. This information may not be up to date, but the deviation should not be very large if the statistics are regulary updated.

Regards,

Yuri

Re: select count (*)

volker_borowski2 — Tue, 11 Oct 2011 16:23:24 GMT

Hi,

how log does each statement run?

How often do you need this? Once a day? every 15 minutes?

In general: Speeding up a statement without a WHERE (keep in mind you will have a

"MANDT=" or "CLIENT=" or "RCLNT=" WHERE if one of the tables is client dependent),

works only with

1) giving a smaller physical object than the table

-> create a non unique index on the smallest physical column (or the MANDT, whatever the field is ... if clientdependent)

---> bad design, costs overhead for several actions on these tables

would only go for this if fast enough and you need it every 30 minutes.

or 2) using parallel query

-> use a hint in the program to achieve this

---> costs overhead for parallel activity anywhere in the system, as it costs cpu and IO resources that others might need.

would only go for this if request is on demand, needs to be speedy, but max. around 10 times a day.

In all other cases I'd schedule one or three jobs every 2, 4, or 24 hours.

Volker

Re: select count (*)

Former Member — Wed, 12 Oct 2011 09:42:09 GMT

Hi,

Perhaps, you could use this program RSTABL11 and the function SAPWL_TABSTAT_SINCE_STARTUP.

Brgds

Julien