Performance issue with data retrieval

[Former Member]

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

[Peranandam]

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

[Former Member]

Hi ,

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

Regards,

Deepthi

[ThomasZloch]

cross-post

[matt]

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

[Former Member]

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

[Former Member]

Hi

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

[Former Member]

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

[Former Member]

Issue resolved on own