select count (*)

[Former Member]

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,

[Former Member]

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,

m.

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

[Former Member]

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

[yuri_ziryukin]

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

[volker_borowski2]

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

[Former Member]

Hi,

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

Brgds

Julien