Skip to Content
0
Former Member
Jan 12, 2011 at 04:41 AM

Inner Join queries on 6 tables

613 Views

Dear Gurus,

Somebody has written inner join query on 6 tables to get citywise and statewise report, query is on VTTK, VTTP, LIKP,LIPS, KNA1 and T005S tables, I have checked that all tables are required to get the details.

In Development system this inner join query fetches record arround 50,000 in some 5 secs while if I write for all entries on VTTK,VTTP after that on LIKP, LIPS and then after KNA1, t005S the to fetch records in different internal tables it takes less time than inner join but as I need data as per inner join means altogether so I required to do loop on it_VTT tables and then inside reading delievery(LIKP,LIPS) entries and kunnr(KNA1) entries.

Due to looping on arround 50000 records, my performance is getting reduced more than this inner join query, can any one plz give some helpfull comment on it?

Below is the reference code, here inner join query I have commented.

TYPES: BEGIN OF ty_delvry,

vbeln TYPE likp-vbeln,

lfart TYPE likp-lfart,

kunnr TYPE likp-kunnr,

matnr TYPE lips-matnr,

lfimg TYPE lips-lfimg,

END OF ty_delvry.

TYPES: BEGIN OF ty_kna1,

kunnr TYPE kna1-kunnr,

ort01 TYPE kna1-ort01,

regio TYPE kna1-regio,

END OF ty_kna1.

TYPES: BEGIN OF ty_vtt,

tknum TYPE vttp-tknum,

shtyp TYPE vttk-shtyp,

erdat TYPE vttk-erdat,

vbeln TYPE vttp-vbeln,

END OF ty_vtt.

TYPES: BEGIN OF ty_t005u,

bland TYPE t005u-bland,

bezei TYPE t005u-bezei,

END OF ty_t005u.

DATA:it_delvry TYPE TABLE OF ty_delvry,

wa_delvry TYPE ty_delvry.

DATA:it_kna1 TYPE TABLE OF ty_kna1,

wa_kna1 TYPE ty_kna1.

DATA: it_vtt TYPE TABLE OF ty_vtt,

wa_vtt TYPE ty_vtt.

DATA: it_t005u TYPE TABLE OF ty_t005u,

wa_t005u TYPE ty_t005u.

SELECT atknum ashtyp aerdat bvbeln

INTO TABLE it_vtt

FROM vttk AS a INNER JOIN

vttp AS b

ON atknum = btknum

WHERE a~shtyp IN ra_shtyp

AND a~erdat IN so_erdat . "SHIPPING DATE

IF it_vtt[] IS NOT INITIAL.

SELECT avbeln alfart akunnr bmatnr b~lfimg

INTO TABLE it_delvry

FROM likp AS a INNER JOIN lips AS b

ON avbeln = bvbeln

FOR ALL ENTRIES IN it_vtt

WHERE a~vbeln = it_vtt-vbeln

AND ( alfart = 'NL' OR alfart = 'LF' )

AND b~matnr = pa_matnr. "MATERIAL NO

IF it_delvry[] IS NOT INITIAL.

SELECT kunnr ort01 regio

FROM kna1

INTO TABLE it_kna1

FOR ALL ENTRIES IN it_delvry

WHERE kunnr = it_delvry-kunnr.

ENDIF.

  • SELECT avbeln btknum bshtyp berdat

  • INTO TABLE it_vtt

  • FROM vttp AS a INNER JOIN

  • vttk AS B

  • ON atknum = btknum

  • FOR ALL ENTRIES IN it_delvry

  • WHERE vbeln = it_delvry-vbeln

  • And b~SHTYP IN RA_SHTYP

  • AND b~ERDAT IN SO_ERDAT . "SHIPPING DATE

ENDIF.

IF it_kna1[] IS NOT INITIAL.

SELECT bland bezei

FROM t005u

INTO TABLE it_t005u

FOR ALL ENTRIES IN it_kna1

WHERE spras = 'E' AND

land1 = 'IN' AND

bland = it_kna1-regio.

ENDIF.

IF it_delvry[] IS NOT INITIAL.

LOOP AT it_delvry INTO wa_delvry.

it_table_spo-lfimg = wa_delvry-lfimg.

it_table_spo-lfart = wa_delvry-lfart.

it_table_spo-vbeln = wa_delvry-vbeln.

READ TABLE it_kna1

INTO wa_kna1

WITH KEY kunnr = wa_delvry-kunnr.

it_table_spo-ort01 = wa_kna1-ort01.

  • IT_TABLE_SPO-REGIO = wa_kna1-REGIO.

READ TABLE it_vtt

INTO wa_vtt

WITH KEY vbeln = wa_delvry-vbeln.

it_table_spo-shtyp = wa_vtt-shtyp.

READ TABLE it_t005u

INTO wa_t005u

WITH KEY bland = wa_kna1-regio.

it_table_spo-bezei = wa_t005u-bezei.

IF ( it_table_spo-shtyp = 'Z001' OR it_table_spo-shtyp = 'Z003' ) OR (

it_table_spo-shtyp = 'Z011' OR it_table_spo-shtyp = 'Z015' ) OR

it_table_spo-shtyp = 'Z017'.

it_table_spo-lfimg_road = it_table_spo-lfimg.

  • MODIFY it_table_spo.

ELSE.

it_table_spo-lfimg_rail = it_table_spo-lfimg.

  • MODIFY it_table_spo.

ENDIF.

APPEND it_table_spo.

CLEAR: it_table_spo,

wa_kna1,

wa_vtt,

wa_t005u.

ENDLOOP.

ENDIF.

***********End Of addtion by UM11012011

************Comment by UM11012011

*SELECT

  • LIPSLFIMG LIKPLFART LIKP~VBELN

  • KNA1ORT01 KNA1REGIO T005U~BEZEI

  • VTTK~SHTYP

  • INTO CORRESPONDING FIELDS OF TABLE IT_TABLE_SPO

*

*FROM

*LIKP INNER JOIN LIPS ON

*LIKP~VBELN = LIPS~VBELN

*

*INNER JOIN VTTP ON

*VTTP~VBELN = LIPS~VBELN

*

*INNER JOIN VTTK ON

*VTTK~TKNUM = VTTP~TKNUM

*

*INNER JOIN KNA1 ON

*KNA1~KUNNR = LIKP~KUNNR

*

*INNER JOIN T005U ON

*T005U~BLAND = KNA1~REGIO

*

*WHERE

*( LIKPLFART = 'NL' or LIKPLFART = 'LF' ) AND

*LIPS~MATNR = PA_MATNR AND "MATERIAL NO

*VTTK~ERDAT IN SO_ERDAT AND " SHIPPING DATE

*VTTK~SHTYP IN RA_SHTYP AND

*T005U~LAND1 = 'IN' AND

*T005U~SPRAS = 'E'.

**********End Of Comment by UM11012010