COSP table : performance issue

[Former Member]

hi all,

COSP table has 13 fields as primary key.

we have a report which has a select query which accesses this table with first 6 fields as a criteria in where clause. all 6 fields are used (non of them are blank) and hence considered in the select query.

but the table has huge number of entries: more than 35 million

hence the report takes around 7 hours to execute, hence its run in background. but business is not happy with the performance of the report.

The table is being archived on monthly basis (using the appropriate criteria)...

I can not create index, since the 6 fields in where clause are already part of primary key (hence it gives error on syntax check and tells the index will be never used..)

Since the report is used on monthly basis, i doubt if i can use the buffering option (i guess the buffer will be too large, hence impacting overall performance of the system...) {if i can use the buffering option, please let me know}

Since the table has so many entries, i am not able to use "select-into-itab" option, i have to use "select-endselect" to avoid memory dump..so, no further tuning through code changes is possible!

here i am running out of options to performance tune this report, please suggest some way to solve this issue..

Edited by: chinmay kulkarni on Nov 28, 2008 10:27 AM

[Former Member]

Hi

Try using Blank Ranges for next 7 primary keys in the where condtions.

Regards

[Former Member]

No, it doesnt work..

[Former Member]

Hi Chinmay,

Look for the Index available for tbale COSP with the fields which you are passing.

If any index is not available you can create the Secondary index with those fields.

[Former Member]

no, the table is not allowing me to create index as the 6 fields used are already present in the primary index in the same order..

so, no secondary index creation is possible..

[Former Member]

The first six fields of the primary key really ought to be enough in this case.

Two things that I can think of:

Use the explain function of ST05 to make sure that it actually uses the primary key and not something else.

Make sure the statistics are up to date for this table.

Rob