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

Long runtime in table HRP1001

Hi,

We have recently done Unicode Conversion of our ECC 6 System. After Unicode Conversion, we have found that there is one customer program which is taking too much time to Execute. This was working fine when the system was non-unicode.

We have found out through SQL Trace that the customer program is taking too much time in fetching records from table HRP1001. We have checked the program and indexes for the table. These are same as they are in other non-unicode systems where it is working fine.

Kindly Advise.

Regards,

Prasad

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jul 08, 2008 at 04:10 PM

    Hi Prasad,

    have you checked how the execution plan for the query looks like? You should also check your statistics. May be the statistics are ok, but you get a different plan due to the reorganization effect of the migration. In this case you may copy "good" statistics from an other system into your Unicode system. This is a database depended task. You should ask you database administrator for this.

    Sending the plan (of your SQL statement) would may be help too.

    Regards

    Ralph

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Prasad,

      after all I found an inconsistency in my understanding of your code. In your ABAP statement:

      select single * from hrp1001 where

      otype = 'S' and

      objid = w_plans and

      plvar = '01' and

      rsign = 'A' and

      relat = '002' and

      sclas = 'S' and

      endda ge begda and

      begda le endda.

      I missed that begda and endda are filled by the module. Is this your intention? I thought that you mean this statement?:

      select single *

      into wa

      from hrp1001 as h

      where h~otype = 'S'

      and h~objid = w_plans

      and h~plvar = '01'

      and h~rsign = 'A'

      and h~relat = '002'

      and h~sclas = 'S'

      and hendda ge hbegda

      and hbegda le hendda.

      Is this standard SAP coding or customer code. I thought originally that you're using a SAP module, but now I think different.

      The difference is that your original code uses a table with header line and the SQL statement uses the content of the header line. This is very dangerous because I'm not sure if the result is what you want in any case. If my statement is what you originally want, then this probably will solve your problem too.

      Regards

      Ralph

  • Posted on Jul 10, 2008 at 09:22 AM

    Hi,

    you are going down into details very fast.

    This does not really look as if there is a problem.

    > SELECT STATEMENT ( Estimated Costs = 4 , Estimated #Rows = 1 )

    And I am missing the information of the Summary by SQL Statements:

    What are

    executions:

    duration:

    records:

    min time per record:.

    Please better run it several times and check whether these number are always the same.

    Siegfried

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Siegfried,

      The program runs for an awfully long time (6-7 hrs). Hence getting an average is not possible. However, here's a sample output..

      Execution:408

      Duration:106,916,911

      Records:38

      Min Time per Record:443

      One point to note is that in the sql trace, I notice its the 'FETCH' operation taking a long time and not the 'REOPEN'.

      Regards,

      Prasad.

  • Posted on Jul 10, 2008 at 11:29 AM

    Execution:408

    Duration:106,916,911

    Records:38

    Min Time per Record:443

    o.k. 100sec is slow!

    But minimal time per record of 443 is o.k., so there are fast executions

    38 records with that speed would need 16ms, no problem, 400 executions maybe 160ms not a real problem.

    Are there identical executions?

    The REOPEN is only the opening of the database connection that is always fast, it is the FETCH.

    Check the extended list, I guess that there are executions where no record is found and I would guess that these are the slower ones.

    Check the variables for the no records found accesses (see details in extended list), sometimes the variables are filled with values which just not exist,

    sometimes there is initial values which are of course not there and which cause slow accesses.

    Siegfried

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 08, 2014 at 09:30 AM

    The simple solution this problem is to delete INDEX~7 from table HRP1001. Even if we put all primary key fields like OTYPE, OBJID, PLVAR, ISTAT, RSIGN, RELAT ENDDA, BEDGA, ETC.

    The Optimizer selects INDEX~7 because INDEX~7 matches all fields from the primary key. The major issue with INDEX~7 is that it is BAD because the fields from this index don't form a key to find a unique record in the table. So, this make the select very hard to retrieve data when you are running a big report.

    So, delete this INDEX from table and the Optimizer automatically uses Primary Index and the performance will get much better.

    ALL THE BEST AND ENJOY SAP!!

    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.