COEP table query taking longer time

[Former Member]

Hi ABAP guru's,

I have a problem with the performance of a Select query on table COEP(91 million records in QA),

i want to tune the program for better performance.

Presenlty it took nearly 6 hrs to execute the program in Background, if in foreground givig dump with

message for maximum time exceed.

the code which exists in the program is

SELECT WOGBTR OBJNR KSTAR OWAER PERIO FROM COEP

INTO TABLE T_COEP

WHERE KOKRS = P_KOKRS AND

OBJNR IN R_COSTCENTER AND

KSTAR IN R_COSTELEMENT AND

PERIO LE P_PERIO AND

GJAHR EQ G_YEAR.

I am in a support project and i need to fix this issue ASAP plz help me out in tuning the program.

I have seen some other posts in the forum for similar issues, the outcome of that is to use LEDNR = '00'.

I am not sure if this works for me or not.

I cant take chance of trail and error , as it has to move to QA untill i know the status of the change and it takes a minimum of week time.

Regards

Sunil kumar

[Former Member]

hi ,

Create a secondary index in the table with all the fields specified in the where condition of your select statement.... Hope this reduces your time to a greater extent.

Regards,

Santosh

[ThomasZloch]

DON'T create another index for that beast.

If your selection on OBJNR is rather narrow and you include LEDNR = '00', the select should use index COEP~1 (LEDNR, OBJNR, GJAHR) for a nice performance improvement.

Greetings

Thomas

Edit: index COEP~2 has the fields OBJNR, KSTAR, GJAHR which would actually be a good one to choose for the given selection, as long as R_COSTCENTER has only few values! please check, whether these two indexes are actually active in the database.

[Former Member]

Thomas,

the index is active.and shall i need to edit this and add some more fields in this index?

and in the r_costcenter i ahve 557 values coming when the select query is executed.

Please suggest how to proceed.

Regards

Sunil Kumar

[ThomasZloch]

Hi again,

no, don't tamper with the existing indexes. Do as Rob and others suggested and add the selection on LEDNR = '00'. With 557 cost centers and almost 100 million records, your selection will still take some time, but hopefully you can count in minutes and not in hours...

Greetings

Thomas

[Former Member]

Thanks to THOMAS and specially to Rob. I have already seen the post message from Rob for similar issue before posting this one.

I need to wait for one more week to see the results of this change.I hope this works for me, if not I will get back to you .

Thanks again.

Regards

Sunil Kumar

[Former Member]

The same question has come up before. If you search the forum before taking any action, you can save yourself time and effort.

If you have created a secondary index, delete it. You don't need it.

Change your SELECT to:

SELECT WOGBTR OBJNR KSTAR OWAER PERIO
  FROM COEP
  INTO TABLE T_COEP
  WHERE LEDNR = '00'             "<====
    AND OBJNR IN R_COSTCENTER
    AND KSTAR IN R_COSTELEMENT
    AND PERIO LE P_PERIO
    AND GJAHR EQ G_YEAR
    AND KOKRS = P_KOKRS.

LEDNR can only have one value (from the domain). Adding it to the WHERE allows it to use index COEP_{1 rather than COEP}2 which you are trying to use, but probably hasn't been created in the database.

Rob

[Former Member]

Hi Experts,

I got a similiar issue, though I follow code specified above still the performance is slow. Please suggest what need to be done.

select dintinct objnr

                     uspob

            INTO TABLE lt_srcc

            FROM coep

            WHERE kokrs  EQ 'ESAM'
            AND perio EQ p_period

            AND lednr = gc_00

*          AND objnr  LIKE 'KSESAM%'

            AND gjahr EQ p_fisyr

            AND kstar  LIKE '0000901%'

            AND vrgng  EQ 'RKIU'

            AND uspob LIKE 'KSESAM%'
            %_HINTS ORACLE 'INDEX("COEP" "COEP~1")'.

Thanks

Chandramouli