topic Select on VBEP-EDATU in Application Development and Automation Discussions

Select on VBEP-EDATU

Former Member — Tue, 12 Dec 2006 20:25:06 GMT

Hi,

I need to find all sales order which have to be delivered to a selected date.

So I make Querry with an acces on VBEP-EDATU but the performance are very bad. How can I increase the performance ?

Thanks

Eric

Re: Select on VBEP-EDATU

Former Member — Tue, 12 Dec 2006 20:37:09 GMT

Hi Sturni,

I see that this VBEP is an APPL1 data class with size category '3' (Data records expected: 29,000 to 110,000). So, you can get this data into an internal table and sort it and then retrieve the required records using EDATU by binary search.

Regards,

Vivek

Re: Select on VBEP-EDATU

Former Member — Tue, 12 Dec 2006 20:52:36 GMT

Thanks for your answer ...

But in my case I have about 500 000 records ...

Re: Select on VBEP-EDATU

Former Member — Tue, 12 Dec 2006 20:54:42 GMT

Can you paste your select query here?

- Guru

Re: Select on VBEP-EDATU

Former Member — Tue, 12 Dec 2006 21:07:24 GMT

Hi Sturni,

Are you filling the internal table using table in select statement?

Can you paste your query once?

Regards,

Vivek

Re: Select on VBEP-EDATU

Former Member — Tue, 12 Dec 2006 21:17:34 GMT

Why don't you give Vivek's answer a try - you may run into memory problems; but on the other hand it might work.

Rob

Re: Select on VBEP-EDATU

Former Member — Wed, 13 Dec 2006 03:28:33 GMT

Hi eric,

Fist you paste your code then we can suggest.

Re: Select on VBEP-EDATU

Former Member — Wed, 13 Dec 2006 05:55:06 GMT

Hi ,

Give select statement fetch record by dividing it into few sections .

Suppose you have 1000 records ,break it into 100's .And then process.

Re: Select on VBEP-EDATU

Former Member — Wed, 13 Dec 2006 09:47:30 GMT

select

VBEP_{EDATU VBEP}POSNR VBEP_{VBELN VBAP}WERKS VBAP_{MATNR VBAP}VRKME VBAP_{KBMENG VBAP}KWMENG VBAP_{ABGRU VBAP}UEPOS VBAP_{POSNR VBAP}VBELN VBAP_{MEINS VBUP}LFSTA VBUP_{LFGSA VBUP}ABSTA VBUP_{GBSTA VBUP}POSNR VBUP_{VBELN VBUK}GBSTK VBUK_{ABSTK VBUK}CMGST VBUK_{LFSTK VBUK}SPSTG VBUK_{VBELN VBPA}KUNNR VBPA_{PARVW VBPA}VBELN

from ( VBEP

left outer join VBAP

on VBAP_{POSNR = VBEP}POSNR

and VBAP_{VBELN = VBEP}VBELN

inner join VBUP

on VBUP_{POSNR = VBAP}POSNR

and VBUP_{VBELN = VBAP}VBELN

inner join VBUK

on VBUK_{VBELN = VBAP}VBELN

inner join VBPA

on VBPA_{VBELN = VBAP}VBELN )

where VBEP~EDATU in SP$00002

and VBUP~LFSTA in SP$00010

and VBUP~LFGSA in SP$00011

and VBUP~ABSTA in SP$00012

and VBUP~GBSTA in SP$00013

and VBUK~GBSTK in SP$00005

and VBUK~ABSTK in SP$00006

and VBUK~CMGST in SP$00007

and VBUK~LFSTK in SP$00008

and VBUK~SPSTG in SP$00009

and VBPA~PARVW in SP$00016 .

endif.

I see my problem is on "where VBEP~EDATU in SP$00002", the program check the full table VBEP (15 544 589 records)

Thanks for your help

Re: Select on VBEP-EDATU

Former Member — Wed, 13 Dec 2006 15:19:04 GMT

Hi,

select

from ( VBEP

left outer join VBAP

on VBAP_{POSNR = VBEP}POSNR

and VBAP_{VBELN = VBEP}VBELN

inner join VBUP

on VBUP_{POSNR = VBAP}POSNR

and VBUP_{VBELN = VBAP}VBELN

inner join VBUK

on VBUK_{VBELN = VBAP}VBELN

inner join VBPA

on VBPA_{VBELN = VBAP}VBELN )

where VBEP~EDATU in SP$00002

and VBUP~LFSTA in SP$00010

and VBUP~LFGSA in SP$00011

and VBUP~ABSTA in SP$00012

and VBUP~GBSTA in SP$00013

and VBUK~GBSTK in SP$00005

and VBUK~ABSTK in SP$00006

and VBUK~CMGST in SP$00007

and VBUK~LFSTK in SP$00008

and VBUK~SPSTG in SP$00009

and VBPA~PARVW in SP$00016 .

endif.

I see my problem is on "where VBEP~EDATU in SP$00002", the program check the full table VBEP (15 544 589 records)

Thanks for your help

Re: Select on VBEP-EDATU

Former Member — Wed, 13 Dec 2006 15:38:45 GMT

EDATU is just one of your problems. There is only one index field in the entire where clause. VBUK~SPSTG is a secondary index, but probably not very selective.

As a test, try running this with SP$00009 set that it only picks up une status (but not blank).

Also - how many records in <b>each</b> of the tables in your select?

Rob

Message was edited by:

Rob Burbank

Re: Select on VBEP-EDATU

Former Member — Wed, 13 Dec 2006 16:03:53 GMT

I would suggest you first select orders and then go for VBEP.

If you need to select based on material - you can start form VAPMA table.

OR you can create a view with VBAK&VBUK (or just select from these tables first) to limit docs by status (exclude rejected & complete docs, since I assume you are doing it anyway in your join)...you can limit by VBAK-ERDAT here since obviously VBEP-EDATU can't be less than VBAK-ERDAT.

Then, based on that - select form VBEP.

Even if you are going to have a tablescan - better to do it on the table with less volume

VBAK < VBAP < VBEP as far as number of records is concerned usually.

Re: Select on VBEP-EDATU

Former Member — Thu, 14 Dec 2006 12:48:25 GMT

Hello,

1. Can you let us know what are the keys you have specified in your select query?

2. If you are using edatu directly in where condition surely it will take lot of time to scan required records. therefore create one secondary index on edatu. It will surely improve select query performance.

*********Poorna***********

Re: Select on VBEP-EDATU

Former Member — Thu, 14 Dec 2006 16:54:37 GMT

Hi,

I use EDATU and WERKS

Bye