topic Re: Performance issue with data retrieval in Application Development and Automation Discussions

Performance issue with data retrieval

Former Member — Thu, 02 Apr 2009 23:12:34 GMT

Do not cross post, and DO check your email - and important message is waiting for you

Hi All,

I have a date base view linked with tabbles EKKO,EKPO,EKBE.

I have a selection screen with Vendor No(ekko-lifnr),Material No(ekpo-matnr), Material Group(elpo-maktl) and Posting Date(EKBE-budat) and none of them are mandatory for user to specify a value

I have created a database view with EKKO,EKPO and EKBE but when i 'm trying to retrieve records its taking too much time and there is an performance issue

Can anyone suggest a better approach

Thanks

Edited by: Matt on Apr 3, 2009 11:12 AM

Re: Performance issue with data retrieval

Peranandam — Fri, 03 Apr 2009 05:38:39 GMT

Hi,

its not necessary to create data base view for your requirement.

get the purchase document number (EBELN) if they enter Vendor No(ekko-lifnr) or Material No(ekpo-matnr) or Material Group(elpo-maktl) or Posting Date(EKBE-budat) . based on purchase document number fetch remaining dat using select for all entries.

you said selection screen parameters are optional only. so no one is part of primary key of the table. in this case you have to create secondary index for three table by combination of primary key.

create secondary index in three table

ekko table which is having fieds of ebeln and lifnr

ekpo consist of ebeln, matnr and maktl

ekbe consis of ebeln and budat.

after creating secondary index fetch data based on secondary index.

I think it will resolve your problem.

Regards,

Peranandam

Re: Performance issue with data retrieval

Former Member — Fri, 03 Apr 2009 06:48:53 GMT

Hi ,

Try using an inner join for EKKO and EKPO and compare the performance.

Regards,

Deepthi

Re: Performance issue with data retrieval

ThomasZloch — Fri, 03 Apr 2009 08:51:52 GMT

cross-post

Re: Performance issue with data retrieval

matt — Fri, 03 Apr 2009 09:13:34 GMT

Do not cross post, and DO check your email - and important message is waiting for you

Re: Performance issue with data retrieval

Former Member — Fri, 03 Apr 2009 13:09:07 GMT

Sorry Guys.

I have learned the lesson and moving forward i would close the thread as soon as the issue is resolved

Thanks again for all your advice

Re: Performance issue with data retrieval

Former Member — Fri, 03 Apr 2009 13:21:41 GMT

This is my selection screen

SELECT-OPTIONS:

costctr FOR ekkn-kostl MATCHCODE OBJECT kost,

order FOR ekkn-aufnr MATCHCODE OBJECT orde,

vendor FOR ekko-lifnr,

mat FOR ekpo-matnr,

s_matgrp FOR ekpo-matkl,

agree FOR ekko-konnr,

so_werks FOR ekpo-werks,

desc FOR ekpo-txz01 NO INTERVALS,

p_idnlf FOR ekpo-idnlf NO INTERVALS,

so_date FOR ekbe-budat.

This is my DB VIEW

EKPO MANDT = EKKO MANDT

EKPO EBELN = EKKO EBELN

EKPO MANDT = EKBE MANDT

EKPO EBELN = EKBE EBELN

EKPO EBELP = EKBE EBELP

LFA1 MANDT = EKKO MANDT

LFA1 LIFNR = EKKO LIFNR

SELECT * FROM DBVIEW INTO TABLE ITAB WHERE

lifnr IN vendor AND

matnr IN mat AND

matkl IN s_matgrp AND

txz01 IN desc AND

konnr IN agree AND

budat IN so_date.

Let me know how can i improve this statement

Or some other way as the above seklect statement is taking too much time

Thanks

Re: Performance issue with data retrieval

Former Member — Sat, 04 Apr 2009 19:27:50 GMT

what do you expect as an answer, there are 6 select-options, each one can be off and on, used or unused, which gets you 2**6 = 64 different combinations.

There are no way that all will be fast, some combinations will not be selective.

So better with a combination, which makes sense and which is slow. Check the SQL Trace.

What is done, which order of processing, which index etc.

In that way you can check all combinations which make sense,

Maybe there indexes missing, maybe the optimizer chooses a wrong index. Unfortunately even with all

information these check can take a while.

It can be necssary,you must offer different coding option depending on the used options.

Siegfried

Re: Performance issue with data retrieval

Former Member — Fri, 17 Apr 2009 15:41:51 GMT

Issue resolved on own