Skip to Content

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

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.

Add a comment
10|10000 characters needed characters exceeded

Related questions

10 Answers

  • Best Answer
    Posted on Oct 12, 2010 at 12:04 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Priya B

      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.

  • Posted on Oct 12, 2010 at 12:42 PM

    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?

    Add a comment
    10|10000 characters needed characters exceeded

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

  • Posted on Oct 13, 2010 at 05:35 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo Former Member
    Former Member
    Posted on Oct 12, 2010 at 12:34 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 12, 2010 at 12:36 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 13, 2010 at 07:36 AM
    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 13, 2010 at 09:28 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 13, 2010 at 09:42 AM

    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 .

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 13, 2010 at 01:01 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 13, 2010 at 01:18 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.