Table Size and Access Considerations

[Former Member]

Hi All,

I need to write a whole lot of reports which access various values of all employees in the organisation. Some of the reports need to compare the daily/monthly values of an individual vis-a-vis the corresponding values of all employees over a period of one year.

My question is would it be a good idea to build a table which would store all the required values and at run time I'd only need to do a select from this table? There are 6000 employees, one record for each employee daily would mean 180000 records per month and 2160000 records in a year. In ten years there would be in excess of 20 million records. (We need records for only 3 years so the older ones could be archived, yet the table would have about 6 million records in it).

Can SAP handle that (technically category 9 goes upto 340 million records) and what issues need to be considered when accessing the data? What would be some of the main design considerations while designing the table and designing the report that access the data from this table?

Any thoughts would be most welcome.

Many Thanks in Advance.

Kind Regards

Sanjay

[Former Member]

Hi All,

I need to write a whole lot of reports which access various values of all employees in the organisation. Some of the reports need to compare the daily/monthly values of an individual vis-a-vis the corresponding values of all employees over a period of one year.

My question is would it be a good idea to build a table which would store all the required values and at run time I'd only need to do a select from this table? There are 6000 employees, one record for each employee daily would mean 180000 records per month and 2160000 records in a year. In ten years there would be in excess of 20 million records. (We need records for only 3 years so the older ones could be archived, yet the table would have about 6 million records in it).

Can SAP handle that (technically category 9 goes upto 340 million records) and what issues need to be considered when accessing the data? What would be some of the main design considerations while designing the table and designing the report that access the data from this table?

Any thoughts would be most welcome.

Many Thanks in Advance.

Kind Regards

Sanjay

[Former Member]

creating a table is not advisable....instead try to use LDB which is very useful in HR module.

[Former Member]

Hi Sanjay,

I have faced similar situation where in i had to capture performance details of transactions on daily, weekly and monthly basis.

I created 3 tables which will capture daily stats,weekly stats n then monthly stats. at the end of the week, the weekly table will be updated and the daily details will be put as an entry with some key as identifer for that whole week.

for example...jan 1st week can ve identified as 0101. by this way u can keep the daily n weekly table accessible.similarly archive it accordingly.

Hope this helps u a bit.

regards,

madhu

[Former Member]

SAP will be able to handle this. the main design problem I think you'll face is designing the key fields so that you can select only the records you want for any particular run. It sounds like the first key may actually just be the date the record was created.

Rob

[Former Member]

Hi!

1. Due to the high number of recors, you have to reduce the size of your structure for the minimum. I mean, do not read unneccessary data, use field to field data read instead of all (*) data read.

For example:

Forget the SELECT * FROM paXXXX ... "WRONG

Use the SELECT pernr begda einda FROM pa0001 INTO ... "CORRECT

2. Use the minimal size internal tables, with types instead of copy the whole table structure.

TYPES: BEGIN OF t_pa0001,

pernr LIKE pa0001-pernr,

TYPES: END OF t_pa0001.

3. After using clear unnecessary internal tables with REFRESH command.

4. Try NOT to join more than 2-3 tables, because more joins will highly reduce the performance and consumes memory as well.

Try using FOR ALL ENTRIES after the main join.

Regards

Tamá

[Former Member]

3. After using an internal table, if it is no longer required free the alocated memory with the FREE command.

Refresh does not de-allocate the memory.