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

program executing for more than 28 hours in quality

Hi,

As part of a job a program is running for more than 28 hours in quality but executing in 20 mins in production.

It is observed that in the program the select query on A902 table is taking long time for execution.

When I checked the SQL trace for this query in production it is using the index YRA.While in quality

the same select query is using the index YVL.

Please find below the select query and the indices.

SELECT
a~kschl
a~matnr
a~datbi
a~datab
a~knumh
FROM a902 AS a
INNER JOIN konp AS b
ON a~knumh EQ b~knumh
INTO TABLE gt_a902
FOR ALL ENTRIES IN gt_mara
WHERE
a~kappl EQ lc_kappl AND
a~kschl IN gr_kschl AND
a~vkorg EQ s_org AND
a~vtweg EQ p_vtweg AND
a~spart EQ p_spart AND
a~matnr = gt_mara-matnr
AND a~datab LE gv_run_date
AND a~datbi GE gv_run_date
* AND b~loevm_ko EQ space.

AND b~loevm_ko EQ space

%_HINTS ORACLE 'INDEX("A902" "A902~YRA")'.

Even I tried using index YRA through HINTS.But still the program is executing for long time.

Please find attached the document showing the indeces used in production and quality.

Please let me know what would be the reason for long execution in quality.

Regards,

Swapna

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • Best Answer
    Posted on Apr 25, 2013 at 10:14 AM

    Hi Swapna,

    Production systems are usually faster than quality. Also data amount and distribution may be different. Are you sure that problem is with database and not internal tables handling for example (SAT/SE30 measurements will confirm if database is bottleneck)?

    Your query is complex. It is not recommended to mix JOIN with FOR ALL ENTRIES statement - this results in performance degradation. It is probably better to use JOIN on three tables, or 3 times FOR ALL ENTRIES (I do not know how gt_mara was selected).

    First statement should return less rows possible, so check IF SELECT on A902 or select on KONP returns less rows. Lets assume it is select on A902 which returns less rows. Then try to run code like below (modified version of your version):

    SELECT a~kschl a~matnr a~datbi a~datab a~knumh FROM a902 as a INTO lt_a902  FOR ALL ENTRIES IN gt_mara. WHERE   a~kappl EQ lc_kappl AND  a~kschl IN gr_kschl AND  a~vkorg EQ s_org AND  a~vtweg EQ p_vtweg AND  a~spart EQ p_spart AND  a~matnr = gt_mara-matnr AND  a~datab LE gv_run_date AND  a~datbi GE gv_run_date.    %_HINTS ORACLE 'INDEX("A902" "A902~YRA")'.    SELECT FROM konp AS b TABLE gt_result  FOR ALL ENTRIES IN lt_a902            WHERE b~loevm_ko EQ space AND             b~knumh = lt_a902~knumh.

    Other option is to run SELECT FROM A902 to lt_a902, then run SELECT FROM KONP FOR ALL ENTRIES in lt_a902 and finally join in memory with gt_mara (using loop over sorted tables with binary search), or first from KONP, then A902 FOR ALL ENTRIES IN lt_konp and then join with gt_mara. The key thing is to filter most at the beginning. If we have smaller result set, then next processing will require less iterations and final algorithm is faster. Everything depends on data distribution - how much which query returns. I would recommend you to test different approaches and do comparable measurements by ST05 or SAT/SE30.

    By the way - there are no attachments in the blog.

    Regards 😊

    Adam

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Apr 26, 2013 at 09:15 AM

    Are you checking IF gt_a902 IS NOT INITIAL before the select query?? Also performance differs across the system due to available system resources / CPU etc.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 23, 2013 at 12:41 PM

    Hello,

    I think the deciding of index is randomly happens since it is picked up dynamically.

    Also, as Abdul said, the performance differs system to system based on memory parameters, other system parameters as well. Check this as well.

    Most of the time, the data is huge in quality systems since more number of testing are done in quality.

    Thanks!.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 31, 2013 at 07:01 AM

    Hi Swapna,

    Small Question, before For All Entries did you check

    ( If GT_MARA is not initial ) as it is required for optimal performance.

    Regards,

    Ankit

    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.