Skip to Content
author's profile photo Former Member
Former Member

Select query getting too slow.

Hello Friends,

I have a performance tunning problem.

I have select options like,

Customer : S_KUNNR

Create date : S_CRT_DT

Change Date : S_CHG_DT

for this i am executing following select query.

* Get the customer details.
  SELECT t1~kunnr t2~name1 t2~name2 t2~name3 t2~sort1
      INTO TABLE i_cust_details
      FROM kna1 AS t1
      INNER JOIN adrc AS t2
        ON t2~addrnumber = t1~adrnr
      INNER JOIN cdhdr AS t3
        ON t3~objectid = t1~kunnr
    WHERE t1~kunnr IN s_kunnr            " Customer
      AND t1~erdat IN s_crt_dt           " Creation date
*     Following sub query for the most recent changed date.
      AND t3~changenr = ( SELECT MAX( changenr )
                               FROM cdhdr AS t4
                            WHERE t4~objectclas = wc_objclass
                              AND t4~objectid   = t1~kunnr
                              AND t4~udate     IN s_chg_dt )

      AND t3~objectclas = wc_objclass
      AND t1~ktokd = ZACT1.

but it takes mauch time.

how can i solve this?

Add a comment
10|10000 characters needed characters exceeded

Related questions

6 Answers

  • Best Answer
    Posted on Mar 25, 2009 at 12:02 PM

    Hi Ronny,

    The best option is what retrieves faster for you, the schedule of execution (hourly, daily, monthly) have you help you to choose the correct for your case.

    Anyway, a index on field udate is probably mandatory for date/time acess.

    option 1: Read the pointers before read what changed.

    SELECT DISTINCT objectid INTO TABLE tl_objects FROM cdhdr
    WHERE objectclas = wc_objclass
      AND objectid   IN s_objectid   "Move the s_kunnr to s_objectid
      AND udate     IN s_chg_dt.
    
    CHECK tl_objects[] IS NOT INITIAL.
    
    * move the content tl_objects to a table with kunnr as key
    
    SELECT t1~kunnr t2~name1 t2~name2 t2~name3 t2~sort1
    INTO TABLE i_cust_details
    FROM kna1 AS t1 INNER JOIN adrc AS t2
                    ON t2~addrnumber = t1~adrnr
    FOR ALL ENTRIES IN lt_changes
    WHERE t1~kunnr EQ lt_changes-kunnr
      AND t1~erdat IN s_crt_dt
      AND t1~ktokd = ZACT1.

    option 2: You don't need cdhdr on inner join, try if this work:

    SELECT t1~kunnr t2~name1 t2~name2 t2~name3 t2~sort1
    INTO TABLE i_cust_details
    FROM kna1 AS t1 INNER JOIN adrc AS t2
                    ON t2~addrnumber = t1~adrnr
    WHERE t1~kunnr IN s_kunnr
      AND t1~erdat IN s_crt_dt
      AND t1~ktokd = ZACT1
      AND EXISTS ( SELECT * FROM FROM cdhdr AS t4
                   WHERE t4~objectclas = wc_objclass
                     AND t4~objectid   = t1~kunnr
                     AND t4~udate     IN s_chg_dt )

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Mar 25, 2009 at 01:16 PM

    Hi Rony,

    Try to use FOR ALL ENTRIES.... in place of INNER JOIN.it will improve performance of your query certainly.

    By

    Prasad GVK.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Mar 25, 2009 at 01:34 PM

    Hi,

    check the no of records in the debugging mode

    i can suggest u that if the no records are small then use for all entries condition instead of inner join

    i think it will give better performance .

    Regds

    Sachhi

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Mar 25, 2009 at 01:41 PM

    How many records are there typically in S_KUNNR?

    Rob

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Mar 26, 2009 at 07:44 AM

    Hi,

    Do not join the table CDHDR, its a huge table, check the technical-->size category before joining table . Avoid joining more than 2 tables. If you have to join use all the keys. Use for all entries.

    Regards,

    Prashant.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Prashant:

      Please see the sticky at the top of the forum regarding JOINS and FOR ALL ENTRIES.

      Can you show any SAP documentation that says not to JOIN more than two tables?

      Can you show any SAP documentation that says not to JOIN large tables?

      Rob

  • Posted on Mar 26, 2009 at 01:33 PM

    Performance is a biit complicate, you must be able to read and to understand,

    just looking at patterns, 2 or 3 joins will not help.

    I think here the subselect kills the performance. Subselects are often very good, but

    here it is not done once, but for every customer number, t1~kunnr

    And it is even done when s_chg_dt is empty, I would assume that this is simply a bug!!!

    Try the following, I can not improve the porblem in one step without a system:

     IF  ( s_chg_dt IS INITIAL ).
    
       SELECT t1~kunnr t2~name1 t2~name2 t2~name3 t2~sort1
                INTO TABLE i_cust_details
               FROM kna1 AS t1
                INNER JOIN adrc AS t2
               ON t2~addrnumber = t1~adrnr
               INNER JOIN cdhdr AS t3
              ON t3~objectid = t1~kunnr
              WHERE t1~kunnr IN s_kunnr                        " Customer
                    AND t1~erdat IN s_crt_dt                 " Creation date
                    AND t3~objectclas = wc_objclass
                    AND t1~ktokd = ZACT1.
    
    ELSE.
    
        SELECT objectclas objectid MAX( changenr ) as changenr
                      INTO TABLE .... itab ...                  
                      FROM cdhdr AS t4
                      WHERE t4~objectclas = wc_objclass
                      AND t4~objectid   = t1~kunnr
                      AND t4~udate     IN s_chg_dt.
    
         SELECT t1~kunnr t2~name1 t2~name2 t2~name3 t2~sort1
                       INTO TABLE i_cust_details
                       FROM kna1 AS t1
                       INNER JOIN adrc AS t2
                       ON t2~addrnumber = t1~adrnr
                       INNER JOIN cdhdr AS t3
                       ON t3~objectid = t1~kunnr
                       FOR ALL EMTRIES IN itab
                       WHERE t1~kunnr = itab-object
                       AND      t1~erdat IN s_crt_dt           " Creation date
                       AND t3~changenr = itab-changenr
                       AND t3~objectclas = wc_objclass
                       AND t1~ktokd = ZACT1.
    

    Customer : S_KUNNR and Create date : S_CRT_DT are mandatory

    only Change Date : S_CHG_DT is optional.

    If any combination is allowed, then it becomes more complicated.

    Siegfried

    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.