Replacement for inner join.

[Former Member]

I have the following SELECT stmt with inner join which is taking more time to execute......Kindly help me how to improve performance

SELECT A_{WERKS A}MATNR A_{MBLNR A}MJAHR A_{MENGE A}BWART A~SHKZG

A_{AUFNR B}BUDAT

INTO CORRESPONDING FIELDS OF TABLE TRANSTAB

FROM MSEG AS A INNER JOIN MKPF AS B ON A_{MBLNR = B}MBLNR AND

A_{MJAHR = B}MJAHR

WHERE A~BUKRS = BUKRS AND

A~WERKS IN R_WERKS AND

A~MATNR IN S_MATNR AND

B~BUDAT >= YFDATE AND

B~BUDAT <= P_DATUM AND

A~BWART IN (101,102,601,602,641,642).

YOUR HELP IS HIGHLY APPRECIATED....

[Former Member]

Hi

remove Into corresponding and try for all entries for the same

Regards

Shiva

[Former Member]

How to use FOR ALL ENTRIES.

[Former Member]

try this

SELECT A_{WERKS A}MATNR A_{MBLNR A}MJAHR A_{MENGE A}BWART A~SHKZG

A_{AUFNR B}BUDAT

FOR ALL ENTRIES OF TABLE TRANSTAB

FROM MSEG AS A INNER JOIN MKPF AS B ON A_{MBLNR = B}MBLNR AND

A_{MJAHR = B}MJAHR

WHERE A~BUKRS = BUKRS AND

A~WERKS IN R_WERKS AND

A~MATNR IN S_MATNR AND

B~BUDAT >= YFDATE AND

B~BUDAT <= P_DATUM AND

A~BWART IN (101,102,601,602,641,642).

[Former Member]

I do not think that this question is answered ....

The example at the end does not work, because the FOR ALL ENTRIES does not appear in the WHERE clause and the INNER JOIN is still there!

Actually the FOR ALL ENTRIES will not improve anything.

The problem is as always INDEXES, INDEXES, INDEXES ... but nobody seems to understand here.

[Former Member]

I wouldn't say nobody here understands

rob

[Former Member]

In this case, the use of IN takes some time to retrieve.

The problem is not always INDEXES.

[Former Member]

Goldie - IN may or may not take more time. It depends on selectivity. If the IN is wide open, then the database will not be able to use and index effectively and may have to use a full index scan. I encourage you to run this:

REPORT ztest_indexed_selects.

PARAMETERS: p_bukrs   LIKE bkpf-bukrs,
            p_blart   LIKE bkpf-blart,
            p_budat   LIKE bkpf-budat,
            p_gjahr   LIKE bkpf-gjahr.
DATA: bkpf  TYPE bkpf.

DATA: bkpf_int TYPE TABLE OF bkpf .

DATA: cc      LIKE bkpf-bukrs,
      doc     LIKE bkpf-belnr,
      start   TYPE i,
      end     TYPE i,
      dif     TYPE i.

START-OF-SELECTION.

* Hardcoded values
* Preliminary select.
  REFRESH bkpf_int.
  SELECT  *
    FROM bkpf
    INTO TABLE bkpf_int
    WHERE bukrs EQ p_bukrs
    AND gjahr EQ p_gjahr
    AND bstat IN (' ', 'A', 'B', 'D', 'M', 'S', 'V', 'W', 'Z')
    AND blart = p_blart
    AND budat = p_budat.

  DO 5 TIMES.
    REFRESH bkpf_int.
    GET RUN TIME FIELD start.
    SELECT  *
      FROM bkpf
      INTO TABLE bkpf_int
      WHERE bukrs EQ p_bukrs
      AND gjahr EQ p_gjahr
      AND blart = p_blart
      AND budat = p_budat.
    GET RUN TIME FIELD end.
    dif = end - start.
    WRITE: /001 'Time for SELECT without BSTAT', ':', dif,
                 'microseconds'.

    REFRESH bkpf_int.
    GET RUN TIME FIELD start.
    SELECT  *
      FROM bkpf
      INTO TABLE bkpf_int
      WHERE bukrs EQ p_bukrs
      AND gjahr EQ p_gjahr
      AND bstat IN (' ', 'A', 'B', 'D', 'M', 'S', 'V', 'W', 'Z')
      AND blart = p_blart
      AND budat = p_budat.
    GET RUN TIME FIELD end.
    dif = end - start.
    WRITE: /001 'Time for SELECT with    BSTAT', ':', dif,
                 'microseconds'.
ENDDO.

Rob

[Former Member]

Hi Abhishek,

The same query can be improved by

1. declaring the fields in the internal table in the order they are present in the database table.

2. Declaring only the required fields which needs to be fetched from the database table.

3. Also if you have declared only the required fields from the database you can remove into corresponding fields of table....instead you can use into table itab

4. Create a range object for BUDAT and provide it in the where clause.

5. Provide the details in the where clause also in the order in which the fields are declared in teh database table.

Make the above changes and try now.

Hope this helps.

If the query is still taking more time then try to create an idex on the frequently used fields only if required.

Regards,

Ranjani.

[Former Member]

Hi Abhishek,

First fetch the data from mkpf with where condition.

check the itab.

and use for all entries.

If helpful give me the points.