select query taking long on covp view

[Former Member]

Hi techies ,

issue is want to get data from covp view for the list of company codes available ,
can you please look into the statement and edit .
heard we need to split the data from tables coep and cobk and then copy to covp .

awaiting replies ,
Thanks,

Praveen bindla.

[Former Member]

It looks like you are trying to use the index based on TIMESTAMP. But if you look at that index in your system, you'll probably find that the index does not exist in the database.

Rob

[Former Member]

Hi rob,

do you mean to say i need to create index on timestamp , kokrs , wrttp , kstar , vrgng and bukrs ?

[Former Member]

No, but I don't think the JOIN or FOR ALL ENTRIES is the problem either. It is probably the lack of using an index in the SELECT. There are indexes on both TIMESTAMP and KSTAR, but they are probably not in the database in your system. Please check to see if they exist in your system.

Rob

[michael_kozlowski]

Trx ST05 'SQL trace' will give you insight about use of indices during runtime.

[matt]

If you are selecting first into g_comp_code and then from covp, forget about the FOR ALL ENTRIES and use an INNER JOIN.

[Former Member]

Hi Matthew ,
Similarly i have did it , but in different way , like
COVP is the  DB view which is getting data from COEP and COBK , hence i did like :
select some fields from COEP into table it_COEP  for all entries in G_COMP_CODE .
select some fields from COBK into table it_COBK for all entries in IT_COEP .

LOOP AT IT_COEP .
READ TABLE IT_COBK .
move fields from coep and cobk into it_COVP_FINAL_TABLE.
ENDLOOP.

Can you please share your explanation using inner join please .

Thanks,

Praveen Bindla.

[matt]

What's to explain? INNER JOIN is well documented and there are many examples of how to use it. It's basic SQL and part of ABAP since release 31I.

[Former Member]

I agree, here should be used a join instead of for all entries.

For all entries creates a loop and a single DB Fetch for each elemen in the table specified in the for all entries clause.

Might you tell us how many elements are in that internal table you use for the "FOR ALL ENTRIES" ?

[Former Member]

Hi Markus ,

in the table g_comp_code , we have only one field i.e compcode .

[Former Member]

I believe the question is "how many entries in the table?" not "how many fields in the structure?"

If you have multiple entries for the same company code, it will slow the SELECT dramatically (for no gain).

Rob

[Former Member]

Hi Rob ,

yes in the table it has 53 entries non duplicate company codes .

like :
1155

1185

1169

2682

2693

A012

B015
X515

etc..

[Former Member]

Like I said before, the problem is that you cannot use an index in the SELECT. You can change FOR ALL ENTRIES to JOINS, use range tables instead of FOR ALL ENTRIES and it's not going to make enough difference to make it worthwhile.

I'd be trying to use the Cost Center (object number) in the SELECT or, failing that, looking for a different table - one that's indexed on KSTAR. You cold look at COSS or COSP if you just need totals.

Rob

[Former Member]

Hi Rob ,
i have implemented the below code , still it takes very long time.

[Former Member]

I would try this and run a SQL trace to analyze more.

1. I will put KOKRS as the first field in WHERE clause as KOKRS is the key field(And the first field) for both COEP and COBK. At lest it will give a chance to the optimizer to use an index range scan. Also since KOKRS are normally very less for a system and it will surely have an impact as the probability to get an hit is high in this case.

2. I am the pretty sure the for all entries is not required here . Company codes are normally not many for any system. Rather we can populate a range table for BUKRS using the entries in g_comp_code and use that in WHERE clause. And offcourse remove duplicates.

3. Remove "BETWEEN" and create a range table using OPTION = 'BT' for timestamp and use that in where clause.

R

[Former Member]

Hi Rudra ,

Thanks for your reply , surely apply ur points and see .
but pointing to ur 2nd point , g_comp_code table has 53 entries like :

1155

1185

1169

2682

2693

A012

B015
X515

etc..

it has alphanumeric entities also , will range table include alphanumeric values ?
if i create range table ?

Thanks ,

Praveen Bindla.

[Former Member]

you can create a range tabel with 53 singel entries, yes,

a single line of the range table consist of:

• SIGN
• OPTION
• LOW
• HIGH

so in your case you want to create 53 single lines that have:

• SIGN = 'I'
• OPTION = 'EQ'
• LOW = companycode
• HIGH = ''.

but be aware, there is a limit! that limit is not ABAP based and so we can't tell you if this will work 100%. As the openSQL is transformed into native SQL and the passed to the database, each database has a maximum length for these native SQL statements.

So I would suggest you just give it a try and create the range table and the populate it

as this will reduce the time for the Select statement massively (FOR ALL ENTRIES creates a loop for each element of the internal table and executes the SELECT within)

you can also check Selection tables - ABAP Programming (BC-ABA) - SAP Library

[Former Member]

Yeah why not as long you refer the same data element BUKRS which is of type CHAR4.

[Former Member]

Hi Rudra ,
i have implemented all the suggestions and  consolidated into one select line select query , but still it is taking very long time .

am selecting data from two tables , COBK and COEP into a table .

since it was taking very huge time while selecting data from databaseview COVP which is join of above tables.

[Former Member]

Hi Markus ,
can you suggest my consolidated select query .

[Former Member]

Have you looked for a table indexed on KSTAR?

Can you add the cost center?

Otherwise, it's just going to take time.

Rob

[Former Member]

Hi Rob ,

Thanks for your reply .
KSTAR is in only item table i.e COEP and that field is not key field (coep~kstar in where clause) ,
there is already  one index defined in table COEP  with some fields including our KSTAR .
And company code (BUKRS) is in only item table COEP and that is also not a key field(coep~bukrs in where clause)  .

Thanks,

Praveen Bindla.

[Former Member]

Yes, but have you checked to see if the database index (on KSTAR) actually exists in the databases? I asked you to check this last week.

Rob

[Former Member]

Can you run an SQL trace(ST05 ) and send us the screenshot of the "Explain" of this select statement? I suspect COEP~1 index with high amount of cost is picked up here as you are using WRTTP and VRGNG in the where clause. I may be wrong. Also let me know what database you are using? ( Oracle/ DB2/ DB6 etc )? May be we can try forcing COEP~1 index by database hint if you are using ORACLE database.

R

[Former Member]

Hi Rudra,

Thanks for your reply.

We are using oracle db.

Can you elaborate what we can do and what is coep~1 index ?

U mean we have to create index on fields which are used in where clause?

[Former Member]

I will upload st05 screen shot asap

[Former Member]

COEP~1 is the standard secondary index created by SAP and NO you should not have to create it if you are using a newer version of R/3. Not sure it was available in the old systems as I don't have access to any old system. So please check in SE11 ( Click button Indexes...)

IMO creating custom secondary index should be the last option. We should be very careful creating custom secondary index on table like COEP as it will impact a lot of processes as secondary indexes impact UPDATE/INSERT/DELETE/MODIFY operations also and in reality the select operation may be slower also as the database optimizer may not like it ( )based on the rule that it uses.

Having said that , it can be a good option sometimes but before that we have make sure we have covered all the possibilities without creating a custom secondary index. So please share the Explain plan from ST05 first.

R

[Former Member]

As a first step in trying to use index COEP~1, add the following to your SELECT:

AND lednr = '00'.

It's not likely to help much but...

Rob

[Former Member]

Sure Rob,

I will add it n let you know

[Former Member]

Hi Rudra,

Yes secondary indexes we will keep it last option.

Shall I try as below:

Select from cobk,

Select from coep for all entries in cobk,

Loop at coep,

Read cobk ,

Move coep and cobk fields into fina_covp table.

Endoop

[Former Member]

There is no harm in trying different options but before that analyze a little bit more also(ST05), cause even the way you are suggesting it will end up asking the same question on COEP and usage of index.

R

[Former Member]

This has been analyzed to death by now. COEP~1 cannot be used until the index key fields are used. LEDNR is the first field of the index.

Rob

[Former Member]

yes Rob , it analysed so much , still i will try your all suggestions and try to analyse more in st05 and SAT ,
and Rob you mean , i need to add LEDNR in my where clause with '00'.

Thanks Rob and Rudra ,
i will surely update you all today .

[Former Member]

Unless I see the ST05 trace I would not comment. It will be very premature to comment on this.

[Former Member]

Yes, add:

And LEDNR = '00'

to the WHERE clause.

But like I said, not much hope that this will really help.

Rob