program executing for more than 28 hours in quality

[Former Member]

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

[adam_krawczyk1]

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

[abdul_hakim]

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.

[Former Member]

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!.

[Former Member]

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