topic Re: performance problem in Application Development and Automation Discussions

performance problem

Former Member — Wed, 26 Mar 2008 05:19:49 GMT

hi,

I am facing a performance problem due to abap code written in below format.

select kunnr ......

from z.....

where kunnr IN s_kunnr

and l... = 'X'

here s_kunnr is select-option and is having 2000 entries in the selection screen.

the execution plan produced is as below.

SELECT

FROM

"Z...."

WHERE

"MANDT" = :A0 AND ( "KUNNR" IN ( :A1 , :A2 , :A3 , :A4 , :A5 , :A6 , :A7 , :A8 , .................................................:A1000 ) OR "DELIVPT" IN ( :A1001 , :A1002...................................:A2000) ) AND L.... = :A2001.

i think we can solve the above problem is we use AND in place for OR in the above case.

But why after using IN condition in the select statement this OR came in the execution plan.

Can anyone give an explanation with any other solution for the issue.(Note - Even if we pass more primary fields that OR will still be an issue.)

Re: performance problem

Former Member — Wed, 26 Mar 2008 07:02:19 GMT

Hi,

I dont think that as you have many entries in Select-option field you have this performance problem, I agree that there will be some but which can be ignored.

I had worked on for mare than 3000 entries and it did not effect the performance much. May be just check if the keys are not been used .

If nothing can be done on other parts, you can split the data fetching into two select querires with first 1000 in one select and other in another select with using Appending into table.

I hope this would solve your problem.

Regards

Sunil Kumar P.

Re: performance problem

Former Member — Wed, 26 Mar 2008 08:12:56 GMT

Hi Kumar,

what you show is actually not the execution plan, but the statement details, the execution plan comes below (in SQL Trace) and shows you the used index.

If you have an SQL trace, then try 'Table' 'Summarize by SQL statements', there you get one line for this statement,

What is the duration, executions, the number of records, the identicals and the minimum and average time per record?

see for details:

/people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy

=> This shows you whether there is actually a performance problem, maybe it is just slow, 2000 entries are already a lot.

Siegfried

Edited by: Siegfried Boes on Mar 26, 2008 9:13 AM

Re: performance problem

Former Member — Wed, 26 Mar 2008 12:09:21 GMT

Hello Siegfried Boes ,

thanks for your response but sorry i checked the SQL trace and the response time is also low.

Should we create an index for the kunnr in z... table if field s_kunnr is mandatory .

I went through your BLOG its helpful....but can i know info about some other statements that comes in execution plan like IINLIST ITERATOR , NESTED LOOPS OUTER...etc.

thanks in advance.

Re: performance problem

valter_oliveira — Wed, 26 Mar 2008 12:10:39 GMT

Hello.

Is kunnr the key for the Z ... table?

If not, think the possibility of creating an index for it.

Best regards.

Valter Oliveira.

Re: performance problem

Former Member — Wed, 26 Mar 2008 12:10:53 GMT

Hello Siegfried Boes ,

thanks for your response but sorry i checked the SQL trace and the response time is also low.

Should we create an index for the kunnr in z... table if field s_kunnr is mandatory .

I went through your BLOG its helpful....but can i know info about some other statements that comes in execution plan like IINLIST ITERATOR , NESTED LOOPS OUTER...etc.

thanks in advance.

Re: performance problem

Former Member — Wed, 26 Mar 2008 12:12:03 GMT

kunnr is the one of the key fields in the table.there are 5 key fields.

Re: performance problem

valter_oliveira — Wed, 26 Mar 2008 12:14:59 GMT

Ok, but it's the first field of the key?

if it's not the first field of the key, the result will be bad ... the program will look at the entire table, to check if the second field is yours ... or the thirth .. and so on.

Create an index if performance is vital.

Remeber that you should always try to have the FIRST field of the key if you are selecting data from BIG tables.

Best regards.

Valter Oliveira.

Re: performance problem

ThomasZloch — Wed, 26 Mar 2008 12:20:32 GMT

> select kunnr ......

> from z.....

> where kunnr IN s_kunnr

> and l... = 'X'

post your complete statement and the primary key (and possible secondary keys) of your Z-table and we might be able to help.

Re: performance problem

Former Member — Wed, 26 Mar 2008 18:54:27 GMT

2000 rows is a lot for a select option.

If all the rows are Inclusive EQual then you can use For All Entries instead of IN within the select statement.

MattG.