topic Re: SELECT statement is not properly executing for CDHDR table? in Application Development and Automation Discussions

SELECT statement is not properly executing for CDHDR table?

Former Member — Wed, 25 Mar 2009 12:00:56 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 statement is not properly executing for CDHDR table?

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

Hi,

This is not so simple. Because CDHDR table has more number of entries in any production system. Usually it records each change occured. In the production system which i work on, CDHDR table has 50,00,00,000 records. Hence this cannot be solved under anycase.

Better schedule it as a background job. Because most of the time TIME_OUT dump will occur.

Instead try to find some other logic for splitting your Select statement without using INNER JOIN. But as of my understanding, there is no logic i suppose.

Please revert back for any corrections needed.

Best Regards,

Suresh

Re: SELECT statement is not properly executing for CDHDR table?

Former Member — Wed, 25 Mar 2009 13:59:28 GMT

Please do not cross post.

rob