topic Re: COSP table : performance issue in Application Development and Automation Discussions

COSP table : performance issue

Former Member — Fri, 28 Nov 2008 04:57:31 GMT

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

Re: COSP table : performance issue

Former Member — Fri, 28 Nov 2008 05:19:56 GMT

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

Regards

Re: COSP table : performance issue

Former Member — Fri, 28 Nov 2008 05:55:37 GMT

No, it doesnt work..

Re: COSP table : performance issue

Former Member — Fri, 28 Nov 2008 06:06:03 GMT

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.

Re: COSP table : performance issue

Former Member — Fri, 28 Nov 2008 06:33:28 GMT

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..

Re: COSP table : performance issue

Former Member — Fri, 28 Nov 2008 14:39:21 GMT

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