11-13-2007 3:42 PM
Hello to everyone,
I have to improve the performance of this select, because it takes several minutes.
Could someone help me ?
SELECT LIKPVBELN LIKPLFDAT LIKP~WADAT_IST
LIPSPOSNR LIPSLFIMG LIPSMEINS LIPSVGBEL LIPS~VGPOS
INTO CORRESPONDING FIELDS OF TABLE LT_LIPS
FROM LIKP JOIN LIPS ON LIKPVBELN = LIPSVBELN
FOR ALL ENTRIES IN P_ITEM
WHERE LIPS~VGBEL = P_VBELN
AND LIPS~VGPOS = P_ITEM-POSNR.
11-13-2007 3:52 PM
You aren't using a key filed in the SELECT, so there's not much you can do. But try this anyway:
SORT p_item BY posnr.
DELETE ADJACENT DUPLICATES FROM p_item COMPARING posnr.
SELECT likp~vbeln likp~lfdat likp~wadat_ist
lips~posnr lips~lfimg lips~meins lips~vgbel lips~vgpos
INTO CORRESPONDING FIELDS OF TABLE lt_lips
FROM likp JOIN lips ON likp~vbeln = lips~vbeln
FOR ALL entries IN p_item
WHERE lips~vgbel = p_vbeln
AND lips~vgpos = p_item-posnr.
Rob
11-13-2007 4:43 PM
Hi,
Avoid <b>CORRESPONDING FIELDS OF</b>, this major impact on this query.
Declare internal table table only with the required fields.
Reward if it helps,
Satish
11-13-2007 5:28 PM
the move into corresponding is no issue with the join!
Check the indexes on the two tables LIKP and LIPS and the size of tables.
Read this blog on the SQL trace:
/people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy
Check the explain and try to understand selectivity, only with a selective index support you can get good performance.
Siegfried
11-15-2007 8:42 AM
I created a new index in LIPS and now the performance is better.
Thank you
11-15-2007 8:52 AM
Sorry i didn't flag my message as question while creating . How can i reward your answer ?
11-15-2007 2:19 PM
Sure - the performance of your single SELECT statement is better, but the creation of the new index will have a negative impact every time a new delivery is created. The new index will now have to be kept upd to date as well.
It's better to find a programming solution, let the users run the program in the background or live with slow performance on this single program.
Rob
11-14-2007 9:32 PM
Try using this code. Let me know if this helps.
TYPES: BEGIN OF ty_lips,
vbeln TYPE lips-vbeln ,
posnr TYPE lips-posnr ,
lfimg TYPE lips-lfimg ,
meins TYPE lips-meins ,
vgbel TYPE lips-vgbel ,
vgpos TYPE lips-vgpos ,
lfdat TYPE likp-lfdat ,
wadat_ist TYPE likp-wadat_ist,
END OF ty_lips,
BEGIN OF ty_likp,
vbeln TYPE likp-vbeln ,
lfdat TYPE likp-lfdat ,
wadat_ist TYPE likp-wadat_ist,
END OF ty_likp.
DATA: w_lips TYPE ty_lips ,
w_likp TYPE ty_likp ,
w_index TYPE sy-tabix,
lt_item LIKE TABLE OF p_item ,
lt_lips TYPE TABLE OF ty_lips ,
lt_lips_tmp TYPE TABLE OF ty_lips ,
lt_likp TYPE HASHED TABLE OF ty_likp
WITH UNIQUE KEY vbeln.
IF NOT p_item[] IS INITIAL.
lt_item[] = p_item[].
SORT lt_item BY posnr.
DELETE ADJACENT DUPLICATES FROM lt_item COMPARING posnr.
SELECT vbeln
posnr
lfimg
meins
vgbel
vgpos
FROM lips
INTO TABLE lt_lips
FOR ALL ENTRIES IN lt_item
WHERE vgbel EQ p_vbeln
AND vgpos EQ lt_item-posnr.
IF sy-subrc EQ 0.
SORT lt_lips BY vbeln posnr.
lt_lips_tmp[] = lt_lips[].
DELETE ADJACENT DUPLICATES FROM lt_lips_tmp COMPARING vbeln.
SELECT vbeln
lfdat
wadat_ist
FROM likp
INTO TABLE lt_likp
FOR ALL ENTRIES IN lt_lips_tmp
WHERE vbeln EQ lt_lips_tmp-vbeln.
ENDIF.
LOOP AT lt_lips INTO w_lips.
w_index = sy-tabix.
AT NEW vbeln.
READ TABLE lt_likp INTO w_likp WITH KEY vbeln = w_lips-vbeln
TRANSPORTING
lfdat
wadat_ist.
IF sy-subrc NE 0.
CLEAR w_likp.
ENDIF.
ENDAT.
w_lips-lfdat = w_likp-lfdat.
w_lips-wadat_ist = w_likp-wadat_ist.
MODIFY lt_lips FROM w_lips INDEX w_index
TRANSPORTING
lfdat
wadat_ist.
ENDLOOP.
ENDIF.
11-15-2007 7:59 AM
hi
<b>Select Statements contd For All Entries</b> The for all entries creates a where clause, where all the entries in the driver table are combined with OR. If the number of entries in the driver table is larger than rsdb/max_blocking_factor, several similar SQL statements are executed to limit the length of the WHERE clause.
The plus
Large amount of data
Mixing processing and reading of data
Fast internal reprocessing of data
Fast
The Minus
Difficult to program/understand
Memory could be critical (use FREE or PACKAGE size)
<u>Points to be must considered FOR ALL ENTRIES</u> Check that data is present in the driver table
Sorting the driver table
Removing duplicates from the driver table
<u>Consider the following piece of extract</u>
Loop at int_cntry.
Select single * from zfligh into int_fligh
where cntry = int_cntry-cntry.
Append int_fligh.
Endloop.
The above mentioned can be more optimized by using the following code.
Sort int_cntry by cntry.
Delete adjacent duplicates from int_cntry.
If NOT int_cntry[] is INITIAL.
Select * from zfligh appending table int_fligh
For all entries in int_cntry
Where cntry = int_cntry-cntry.
Endif.