EKBE select taking too long

[Former Member]

Hello guys

I need some guidance here.. it is fairly straight forward select but takes for ever to run as EKBE is huge and i am not givin it the proper key ( because I do not have a choice. ) . I need to get GR created for a particular date. CPUDT

SELECT ekbe_{ebeln ekbe}ebelp ekbe~belnr

ekbe_{buzei ekbe}budat ekbe~bwart

INTO CORRESPONDING FIELDS OF TABLE grtab

FROM ekbe

INNER JOIN mkpf

ON mkpf_{mblnr = ekbe}belnr

WHERE ekbe~ebeln IN s_ebeln

AND ekbe~ebelp IN s_ebelp

AND ekbe~matnr IN s_matnr

• AND budat LE l_today

• AND budat GE l_chdate

AND ekbe~werks IN s_werks "Exclude BS@H

AND ekbe~vgabe = '1' "GRs Only

AND mkpf~cpudt LE l_today

AND mkpf~cpudt GE l_chdate.

EKBE is a huge table with 52 million records.

what i want is a list of good reciepts created for a particulat day . i only have cpudt field as a selection. everything else is blank.

Any ideas ?

[Former Member]

Hello guys

I need some guidance here.. it is fairly straight forward select but takes for ever to run as EKBE is huge and i am not givin it the proper key ( because I do not have a choice. ) . I need to get GR created for a particular date. CPUDT

SELECT ekbe_{ebeln ekbe}ebelp ekbe~belnr

ekbe_{buzei ekbe}budat ekbe~bwart

INTO CORRESPONDING FIELDS OF TABLE grtab

FROM ekbe

INNER JOIN mkpf

ON mkpf_{mblnr = ekbe}belnr

WHERE ekbe~ebeln IN s_ebeln

AND ekbe~ebelp IN s_ebelp

AND ekbe~matnr IN s_matnr

• AND budat LE l_today

• AND budat GE l_chdate

AND ekbe~werks IN s_werks "Exclude BS@H

AND ekbe~vgabe = '1' "GRs Only

AND mkpf~cpudt LE l_today

AND mkpf~cpudt GE l_chdate.

EKBE is a huge table with 52 million records.

what i want is a list of good reciepts created for a particulat day . i only have cpudt field as a selection. everything else is blank.

Any ideas ?

[Former Member]

instead of inner join, try using for all entries.

[Former Member]

hi

try this:

split the join/

select the mblnr from the mkpf table for the condition specified into i_mkpf(for ex). then delete the duplictae entries comparing belnr. then go to ekbe table n fetch the data for all the entries in i_mkpf table.

before using for all entries check if i_mkpf[] is initial.

avoid into corresponding fields in the select query

**reward if helpful

regards,

madhu

[Former Member]

Thanks madhumita. thats exactly what i have done trying to improve the performance in a copied version of the program. But since EKBE key is made up of EBELN , EBELP and a few others ( including BELNR) it is still doing a full table scan on EKBE which is not helping much.

[Former Member]

hi

did u try creating an index with the fileds in the where clause. this shud def help you and u can guide the select to use the index u created.

say:

SELECT * into itab FROM csks

WHERE kokrs = .. AND

kostl = ..

%_HINTS ORACLE 'index(csks"J")'.

ebeln and ebelp input is always blank???

regards,

madhu

[Former Member]

Yes Madhu. They are both blank as I want the select to be based on a date.

[Former Member]

hi

in that case create an index with mandt and mblnr.try this lemme know if it is of any help.

regards,

madhu

[Former Member]

use package size and checkout if it minimises ur time

SELECT ekbe_{ebeln ekbe}ebelp ekbe~belnr

ekbe_{buzei ekbe}budat ekbe~bwart

INTO CORRESPONDING FIELDS OF TABLE grtab

FROM ekbe

INNER JOIN mkpf

ON mkpf_{mblnr = ekbe}belnr

WHERE ekbe~ebeln IN s_ebeln

AND ekbe~ebelp IN s_ebelp

AND ekbe~matnr IN s_matnr

• AND budat LE l_today

• AND budat GE l_chdate

AND ekbe~werks IN s_werks "Exclude BS@H

AND ekbe~vgabe = '1' "GRs Only

AND mkpf~cpudt LE l_today

AND mkpf~cpudt GE l_chdate

<b>PACKAGE SIZE 100000.</b>

[Former Member]

Table EKBE contains Invoice Receipts and Freight Postings as well as Goods Receipts. Have you thought of reading table MSEG? This is keyed by document number and GR document numbers are usually created by the system.

You need some way of linking the CPU Date to the Document Number, one way is to store the run date of the program and last number read in a Z table so you know where to start the read from. At the end of the program update the table with the new last number and date.

This kind of technique works very well if the ABAP can run in batch overnight when there are no GR's going on.

[Former Member]

Have you checked the SQL explain plan in ST05? If you are using an oracle database as the back end, try using a hint to force a hash join in the background.

Although this consumes more database memory, it can dramatically improve performance for large table joins in some scenarios. There are some SAP OSS notes on oracle hints, but note that they are not formally supported - so use at your discretion.

SELECT ekbe_{ebeln ekbe}ebelp ekbe~belnr

ekbe_{buzei ekbe}budat ekbe~bwart

INTO CORRESPONDING FIELDS OF TABLE grtab

FROM ekbe

INNER JOIN mkpf

ON mkpf_{mblnr = ekbe}belnr

WHERE ekbe~ebeln IN s_ebeln

AND ekbe~ebelp IN s_ebelp

AND ekbe~matnr IN s_matnr

• AND budat LE l_today

• AND budat GE l_chdate

AND ekbe~werks IN s_werks "Exclude BS@H

AND ekbe~vgabe = '1' "GRs Only

AND mkpf~cpudt LE l_today

AND mkpf~cpudt GE l_chdate

%_HINTS ORACLE 'USE_HASH(T_00 T_01)'.

[Former Member]

Thank you guys for trying to help. really appreciate. Daniel, pardon my ignorance, but I don;t have a clue what hash joins are

I am going to try and get EKBE out of the equation and go for MKPF/ MSEG instead. at least I can do an index read on those tables. MSEG is bigger than EBKE ( EKBE = 52 Mill and MSEG = 1 billion approx) but i am still going to try.

[Former Member]

Hi,

Check this code. Its a sure solution to your problem. You are getting performance issue because you are reading EKBE with some key field ( EBELN, EBELP, VGABE) but also with some not key field WERKS. Also you are reading MKPF with not-key field too ( CPUDT ).

The code given below works like this.

- First, it does a JOIN on table EKBE and MKPF. In where condition, it uses EBELN and EBELP only. Since these are key fields on table EKBE read would be fast.

- Now it deletes all unwanted entries based on selection cirteria and range tables. The deletion is based on MATNR, WERKS, VGABE, CPUDT. Since these operations are on internal tables, they would be fast.

This way you eliminate using non-key fields in select statement, get all data based on key-fields and later delete unwanted entries.

Here, you can also use VGABE in select since it is one of the key field in EKBE but i would suggest to use separate to delete unwanted entries since VGABE is not in order right after EBELN and EBELP.

I hope this solution will help you.

Let me know if you have any other question.

*----------------------------------------------------------------------*
*----------------------------------------------------------------------*
TABLES: ekko, ekpo, mara, t001w.

SELECT-OPTIONS: s_ebeln FOR ekko-ebeln,
                s_ebelp FOR ekpo-ebelp,
                s_matnr FOR mara-matnr,
                s_werks FOR t001w-werks.

CONSTANTS: c_vgabe LIKE ekbe-vgabe VALUE '1'.

DATA: l_today  LIKE mkpf-cpudt,
      l_chdate LIKE mkpf-cpudt.

DATA: BEGIN OF lit_grtab OCCURS 0,
       ebeln LIKE ekbe-ebeln,
       ebelp LIKE ekbe-ebelp,
       belnr LIKE ekbe-belnr,
       buzei LIKE ekbe-buzei,
       budat LIKE ekbe-budat,
       bwart LIKE ekbe-bwart,
       matnr LIKE ekbe-matnr,
       werks LIKE ekbe-werks,
       vgabe LIKE ekbe-vgabe,
       cpudt LIKE mkpf-cpudt,
      END OF lit_grtab.

RANGES: r_vgabe FOR ekbe-vgabe,
        r_today FOR ekbe-cpudt,
        r_chdate FOR ekbe-cpudt.

r_vgabe-sign = 'I'.
r_vgabe-option = 'EQ'.
r_vgabe-low = c_vgabe.
APPEND r_vgabe.

r_today-sign = 'I'.
r_today-option = 'LE'.
r_today-low = l_today.
APPEND r_today.

r_chdate-sign = 'I'.
r_chdate-option = 'GE'.
r_chdate-low = l_chdate.
APPEND r_chdate.

SELECT
      ekbe~ebeln
      ekbe~ebelp
      ekbe~belnr
      ekbe~buzei
      ekbe~budat
      ekbe~bwart
      ekbe~matnr
      ekbe~werks
      ekbe~vgabe
      mkpf~cpudt  FROM ekbe
                  INNER JOIN mkpf
                  ON mkpf~mblnr = ekbe~belnr
                  INTO TABLE lit_grtab
                  WHERE ekbe~ebeln IN s_ebeln AND
                        ekbe~ebelp IN s_ebelp.

*" delete entries based on MATNR
DELETE lit_grtab WHERE NOT matnr IN s_matnr.

*" delete entries based on WERKS
DELETE lit_grtab WHERE NOT werks IN s_werks.

*" Keep on GR data
DELETE lit_grtab WHERE NOT vgabe IN r_vgabe.

*" delete entries based on DATE
DELETE lit_grtab WHERE NOT budat IN r_today.

*" delete entries based on DATEE
DELETE lit_grtab WHERE NOT budat IN r_chdate.
*----------------------------------------------------------------------*
*----------------------------------------------------------------------*

Regards,

RS