For Repair Service Report - FOR ALL ENTRIES IN is giving Time Out Error

[former_member435508]

Hi,

My Report is for Repair Service Details.

(from Service order (iw33) , Notification (iw53) to Sales order and Outbound Delivery )

This works fine if few Records are selected.

But, with a surprise, This Report is giving me Time out Error - while searching ITAB from table LIPS.

  SELECT vbeln posnr vbelv posnv " At this point it stuck and gives time out
     FROM lips
      INTO TABLE gi_lips
       FOR ALL ENTRIES IN gi_caufv
       WHERE vbelv = gi_caufv-kdauf
         AND posnv = gi_caufv-kdpos
         AND vbeln IN so_vbeln.

In fact, records are not that much. ( ITAB - gi_caufv has 5200 records and Database Table LIPS has not more than 20,000 records).

For LIPS - both the primary keys vbeln & posnr is taken.

[Former Member]

Hope you have done the check IF GI_CAUFV[] IS NOT INITIAL. What is the definition of SO_VBELN (hope this contain some values). LIPS doesn't have index on VBELV & POSNV.

[former_member435508]

Thanks Vinod.

Yes, i did it.

SELECT-OPTIONS: so_aufnr FOR caufv-aufnr,
                  so_qmnum FOR viqmel-qmnum,
                  so_matnr FOR viqmel-matnr,
                  so_bemot FOR caufv-bemot,
                  so_ilart FOR afih-ilart,
                  so_beber FOR viqmel-beber,
                  so_ntanf FOR afvv-ntanf,
                  so_ntend FOR afvv-ntend,
*                  so_gstrp FOR caufv-gstrp,
                  so_gltrp FOR caufv-gltrp,
                  so_getri FOR caufv-getri,
                  so_strmn FOR viqmel-strmn,
                  so_ltrmn FOR viqmel-ltrmn,
                  so_qmdat FOR viqmel-qmdat,
                  so_pern2 FOR afvc-pernr,
                  so_pern3 FOR afvc-pernr,
                  so_pern4 FOR afvc-pernr,
                  so_vbeln FOR lips-vbeln,
                  so_wadat FOR likp-wadat_ist,
                  so_vaplz FOR caufv-vaplz.

SELECT aufnr auart autyp ktext objnr gstrp gltrp getri
           aufpl rueck rmzhl bemot kdauf kdpos vaplz
      FROM caufv
      INTO TABLE gi_caufv
      WHERE aufnr IN so_aufnr
        AND autyp EQ c_30
        AND auart IN (c_zm03, c_zm04)
*        AND gstrp IN so_gstrp
        AND gltrp IN so_gltrp
        AND getri IN so_getri
        AND bemot IN so_bemot
        AND vaplz IN so_vaplz.

    IF gi_caufv IS NOT INITIAL.

[Former Member]

Hi Priya,

Must check with Sachin's comments about gi_caufv, and also there is no indexing for your where clause,

1 thing you can try is to change the sequence of where clause, using VBELN first followed by other two fields.

I think the resone behind time out is indexing, please check the trace as well.

Thanks,

Anmol.

[Former Member]

Hi Priya,

I dont know the correct answer,

but please check line in your code:

IF gi_caufv IS NOT INITIAL.

please make it as

IF gi_caufv[] IS NOT INITIAL.

if I am not getting it wrong, you missed [] if gi_caufv is an internal table.

Thanks and regards

Sachin Bhatt IN

[raymond_giuseppi]

As LIPS don't have index for vbelv, posnv, the access is certainly not optimised (check via [ST05|http://www.sdn.sap.com/irj/scn/advancedsearch?query=st05#sdn_content_category_value_wiki])

First, read this OSS [Note 185530 - Performance: Customer developments in SD|https://service.sap.com/sap/support/notes/185530] (You could try loading first VBFA and then LIPS)

Regards,

Raymond

[former_member435508]

Thanks Sachin & Raymond,

@ Sachin

But in this case ITAB is without header line. so I need to go with ITAB and not ITAB[]

DATA: gi_caufv TYPE TABLE OF gt_caufv,

@ Raymond

Sorry, I am unable to open the Notes (dnt have authority)

Yes, It's not in index.

Before this, I worked on VGBEL & VGPOS fields of LIPS for fetching Records.

But for Repair Service, the exact Item NO. is found in POSNV and not in VGPOS !!!

How can I continue with it?

[Former Member]

Hi,

You can use VGBEL for selection and LIPS is indexed on VGBEL. After that you could remove those entries where POSNV doesn't match with KDPOS in gi_caufv.

SELECT vbeln posnr vbelv posnv

FROM lips

FOR ALL ENTRIES IN gi_caufv

WHERE vgbel = gi_caufv-kdauf

AND vbeln IN so_vbeln.

Hope it helps.

Sujay

[Former Member]

Hi Priya,

The order of the fields in your WHERE clause is not as per the order of fields in the table LIPS. Try changing as below.

SELECT vbeln posnr vbelv posnv 
      FROM lips
      INTO TABLE gi_lips
       FOR ALL ENTRIES IN gi_caufv
       WHERE vbeln IN so_vbeln
         AND vbelv = gi_caufv-kdauf
         AND posnv = gi_caufv-kdpos.

Also check if any index exist for the fields VBELV and POSNV. Run the SQL trace and check if the query is using any index or not. If not try creating one.

Regards,

Immanuel.

[raymond_giuseppi]

Try to use VBFA (also request your super-administrator for an OSS id, this is a must have)

Extract from [Note 185530|https://service.sap.com/sap/support/notes/185530]

SELECT FROM vbfa WHERE VBELV  = ... and VBTYP_N = 'J'    
SELECT FROM lips WHERE vbeln = vbfa-vbeln

NB: I hope that so_vbeln is not only a deception for the Code Inspector.

Regards,

Raymond

[Former Member]

Exactly. As mentioned by Raymond, get the delivery number from VBFA by passing the preceeding document number ,line item, document type in VBFA-VBELV , VBFA-POSNV, VBFA-VBTYP_N ('J'). Pass the Delivery Number (VBFA-VBELN) & Line item (VBFA-POSNN) to table LIPS to get the delivery information.

[former_member435508]

Thank You All.

@ Anmol

Yes, Index is not there.

Even changing fields are not useful. I think problem is with vbelv & posnv.

@ Sujay

I thought to do this way but in some cases it's not possible -->

like entries are

kdauf = 1290 & kdpos - 10030
IN LIPS
vbeln    vgbel  vgpos  vbelv  posnv  
8400094   1290   10000  1290  10030              " With Del. type 'LR'
34000171  1290    10000  1290  10030            " With Del. type 'LF' for item 10030
34000172  1290    10000  1290  10080            "With Del. type 'LF' for item 10080

@ Immanuel

I tried to change the sequence, Also Delete the so_vbeln, Still the problem is there.

I think it's because of Indexing of vbelv & posnv.

@ Raymond & Vinod

As I write for Sujay, there are Multiple Delivery for different Items.

(After Repair Multiple items has OBD on diff. Date)

DATA IN VBFA
preceding doc.  item     follow doc.   item
1290             10000       34000171     10
1290             10000       34000172      10

My Required o/p is -->

1290 10030 --> 34000171 10

Edited by: Priya.ABAP on Oct 13, 2010 7:40 AM

[Former Member]

On What basis you are selecting only one Delivery Document from the List (for Particular VBELV/POSNV)? I think still you can do this by fetching the data from VBFA (which will extract data using indexes) and applying the logic on the internal table which store VBFA Data. (If there is no specific logic, then may be using DELETE ADJACENT DUPLICATES .....)

[Former Member]

HI,

You can try this

if u are always having so_vbeln

if not so_vbeln[] is initial.

SELECT vbeln posnr vbelv posnv 
      FROM lips client specified
      INTO TABLE gi_lips
       FOR ALL ENTRIES IN gi_caufv
       WHERE mandt = sy-mandt
        and     vbeln IN so_vbeln.

loop at gi_lips.
read table gi_caufv with key vbelv = gi_caufv-kdauf
                                              posnv = gi_caufv-kdpos.
if sy-subrc ne 0.
   delete gi_lips.
endif.
endloop.
endif.

Regards,

Madhukar Shetty

[former_member194613]

SELECT vbeln posnr vbelv posnv 
      FROM lips client specified
      INTO TABLE gi_lips
       FOR ALL ENTRIES IN gi_caufv
       WHERE mandt = sy-mandt
        and     vbeln IN so_vbeln.

This can't be correct:

+ don't use 'Client specified' with sy-mandt, it is done automatically

+ don't reduce the WHERE conditions and expect a performance improvement

+ don't use nested loops with standard tables

Siegfried

[former_member435508]

Thank You All for Your Help.

I think with SELECT statemnt it's not possible to get the Exact OBD no.

@Vinod & Madhukar

In SEKECT statement I am taking vgbel = kdauf as WHERE condition.

And in ITAB comparing all OBDs for posnv = kdpos.

@ Siegfried

Agree with You.

Following all three suggestions.

SELECT vbeln posnr vgbel vbelv posnv 
      FROM lips
      INTO TABLE gi_lips
       FOR ALL ENTRIES IN gi_caufv
       WHERE vbeln IN so_vbeln
         AND vgbel = gi_caufv-kdauf.
        

Now Report is taking Few Second to Execute.

Thanks Again.

[former_member435508]

I got the Solution via Processing Logic in ITAB

But, Is it possible to get the Exact OBD No with SELECT statement .

Using - Database tables like LIPS or VBFA .

[Former Member]

Hi Priya,

I tried your query to my end and found out that, if you do not use for all entries and gi_caufv-kdauf

and gi_caufv-kdpos field in your where clause the performance is considerably higher,

As I mensioned before there is no index to your where clause,

So try to get the data according to VBELN first and then delete the internal table accordingly.

Please refer to this code

if gi_caufv[] is not INITIAL.
SELECT vbeln posnr vbelv posnv " At this point it stuck and gives time out
     FROM lips INTO CORRESPONDING FIELDS OF TABLE gi_lips
       FOR ALL ENTRIES IN gi_caufv  WHERE vbelv = gi_caufv-kdauf
         AND posnv = gi_caufv-kdpos and vbeln IN so_vbeln.
endif.

This code gives timeout error for me as well, Now please refer the other code

SELECT vbeln posnr vbelv posnv    FROM lips
      INTO CORRESPONDING FIELDS OF TABLE gi_lips
       FOR ALL ENTRIES IN gi_caufv
       WHERE vbeln IN so_vbeln.

Thanks,

Anmol.

[ravi_lanjewar]

Hi Priya,

SELECT vbeln posnr vbelv posnv " At this point it stuck and gives time out
     FROM lips
      INTO TABLE gi_lips
       FOR ALL ENTRIES IN gi_caufv
       WHERE vbelv = gi_caufv-kdauf
         AND posnv = gi_caufv-kdpos
         AND vbeln IN so_vbeln.

I don't know your exact requirement where you are putting such type of condition.

if so_vbeln range or select option variable is empty . That case it will gives timeout error and it is feasiable most probably.

In this condition it read whole table for all entries where condition is consider and there where is not secondary index for it alos.

1) read data from table lips using the vbeln

2) after reading data into internal table filter data with internal table gi_caufv i.e. Delete rest of entries from internal table lips.

but must be mind that so_vbeln should not be initial.

I think this will gives better performance.