Select on VBEP-EDATU

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

Thanks for your answer ...

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

[Former Member]

Can you paste your select query here?

- Guru

[Former Member]

Hi Sturni,

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

Can you paste your query once?

Regards,

Vivek

[Former Member]

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

[Former Member]

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

[Former Member]

Hi eric,

Fist you paste your code then we can suggest.

[Former Member]

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.

[Former Member]

Hi,

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

[Former Member]

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

[Former Member]

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.

[Former Member]

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***********

[Former Member]

Hi,

I use EDATU and WERKS

Bye