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