topic Re: Select query getting too slow. in Application Development and Automation Discussions

Select query getting too slow.

Former Member — Wed, 25 Mar 2009 11:39:02 GMT

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?

Re: Select query getting too slow.

Former Member — Wed, 25 Mar 2009 12:02:18 GMT

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 )

Re: Select query getting too slow.

Former Member — Wed, 25 Mar 2009 13:16:47 GMT

Hi Rony,

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

Prasad GVK.

Re: Select query getting too slow.

Former Member — Wed, 25 Mar 2009 13:34:15 GMT

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

Re: Select query getting too slow.

Former Member — Wed, 25 Mar 2009 13:41:16 GMT

How many records are there typically in S_KUNNR?

Rob

Re: Select query getting too slow.

Former Member — Thu, 26 Mar 2009 07:44:50 GMT

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.

Re: Select query getting too slow.

Former Member — Thu, 26 Mar 2009 13:10:06 GMT

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

Re: Select query getting too slow.

Former Member — Thu, 26 Mar 2009 13:33:54 GMT

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