Performance Issue

[Former Member]

Hi Guys, I am facing one performance issue. I have two custom reports which selects the data from some standard SAP tables and will load the data into one Z table.  Now in report 2, one of the existing SELECT queries, I have selected one extra field in addition to the existing fields retrieved. Functionality wise, both the reports will load the same number of records but the second report will load the value for additional field. After making this change in report2, runtime of the report 2 doubled than the run time of the report1. What will be main cause for this run time issue?

I have already done the SAT analysis and it points to the query where I have added an extra field. It will be helpful if anyone of you through some insights on this issue.

[former_member201275]

Could you please post the code here for both reports so that i can see what tables and fields you are using. It could be that the extra field is messing up with existing indexes.

[FredericGirod]

Could you make an SQL trace, with the Explain of the SELECT with the field and without the field ?

Fred

[Former Member]

with the field run time : 477,327,183 ms

without the field run time is : 9,475,320ms

[former_member201275]

As requested previously, please post the code here.

[FredericGirod]

Hi,

when you create a SQL trace you have this kind of screen

select the line (on the OPEN operation) and press the button

You will get this :

this is, what we need to understand you problem !

regards

Fred

[Former Member]

Here is the code. Please note that this works without any performance issues in production. But after adding the field zfield( 6 digits numeric field), the run time increased to 3 times the run time of the existing one. No other changes have made except the new field addition.

LOOP AT gt_tab1 INTO wa_tab1.

     CONCATENATE `( (` wa_tab1-tab_name  'AS a INNER  JOIN'

        'dfkkinvbill_h  AS b ON  a~billdocno EQ b~billdocno ) LEFT OUTER JOIN'

      'ztable  AS c ON b~billdocno EQ c~zdoc'

       '                      )' INTO gv_table_name SEPARATED BY space.

     SELECT a~field1

           a~field2

           a~field3

           a~zfield -----------> "NEW FIELD ADDED

FROM  (gv_table_name) INTO TABLE lt_temp

   FOR ALL ENTRIES IN gt_tab1

   WHERE a~appl EQ gt_tab1-appl

   AND a~code EQ gt_tab1-key01+0(4)

   AND ( b~srcdoctype LIKE 'X%' OR b~srcdoctype LIKE 'Y%' OR b~srcdoctype LIKE 'Z%' )

   AND b~reversaldoc  IN s_revdoc

   AND b~reverseddoc  IN s_rev_f.

     APPEND LINES OF lt_temp TO gt_final.

     CLEAR: lt_cons_temp.

   ENDIF.

ENDLOOP.

[Private_Member_7726]

Hi,

How many records are in GT_TAB1 and how many approximately are expected in gt_final?

Could it be that the zfield in the result set is the "straw the breaks camels back" in that  the memory use of program grows to the point system needs to stat swapping to disc?

cheers

Jānis

[Former Member]

Adding z field will not be any problem, could be issue with table update statistics or storage index quality issue.

do the following sequence

Get the trace and see the CPU cost, if its in red then problem with your query, and may be wrong index selection.

Execute the report RS*QN and see the Index storage quality(rebuild the index if its less than 60%)

Activate the table histogram.

Still your issues persist the contact BASIS to ask re-org the table.

Regards,

Vadamalai A

[Former Member]

No issue in the code . DBA guys resolved the issue.