topic Re: Performance Issue in Application Development and Automation Discussions

Performance Issue

Former Member — Wed, 02 Jul 2014 14:21:48 GMT

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.

Re: Performance Issue

former_member201275 — Wed, 02 Jul 2014 14:32:09 GMT

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.

Re: Performance Issue

FredericGirod — Wed, 02 Jul 2014 14:34:12 GMT

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

Fred

Re: Performance Issue

Former Member — Wed, 02 Jul 2014 15:12:27 GMT

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

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

Re: Performance Issue

former_member201275 — Wed, 02 Jul 2014 15:23:02 GMT

As requested previously, please post the code here.

Re: Performance Issue

Former Member — Wed, 02 Jul 2014 15:44:49 GMT

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.

Re: Performance Issue

FredericGirod — Thu, 03 Jul 2014 06:55:03 GMT

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

Re: Performance Issue

Private_Member_7726 — Fri, 04 Jul 2014 05:07:57 GMT

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

Re: Performance Issue

Former Member — Fri, 04 Jul 2014 05:23:19 GMT

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

Re: Performance Issue

Former Member — Mon, 07 Jul 2014 21:49:17 GMT

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