topic Re: Replacement for inner join. in Application Development and Automation Discussions

Replacement for inner join.

Former Member — Wed, 30 Jan 2008 04:08:34 GMT

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

Re: Replacement for inner join.

Former Member — Wed, 30 Jan 2008 04:28:51 GMT

remove Into corresponding and try for all entries for the same

Regards

Shiva

Re: Replacement for inner join.

Former Member — Wed, 30 Jan 2008 05:11:18 GMT

How to use FOR ALL ENTRIES.

Re: Replacement for inner join.

Former Member — Wed, 30 Jan 2008 06:10:28 GMT

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).

Re: Replacement for inner join.

Former Member — Wed, 30 Jan 2008 08:27:43 GMT

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.

Re: Replacement for inner join.

Former Member — Wed, 30 Jan 2008 18:48:35 GMT

I wouldn't say nobody here understands

rob

Re: Replacement for inner join.

Former Member — Thu, 31 Jan 2008 06:58:25 GMT

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.

Re: Replacement for inner join.

Former Member — Fri, 01 Feb 2008 02:21:56 GMT

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

The problem is not always INDEXES.

Re: Replacement for inner join.

Former Member — Fri, 01 Feb 2008 15:59:31 GMT

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

Re: Replacement for inner join.

Former Member — Sun, 03 Feb 2008 05:53:28 GMT

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.