topic Re: [Bug] Select query doesnt follow basic performance optimization rules in Application Development and Automation Discussions

[Bug] Select query doesnt follow basic performance optimization rules

Former Member — Thu, 11 Apr 2019 06:25:32 GMT

Hi Team,

We recently noticed in DBACOCKPIT, that background job scheduled for standard program "RFITEMAP" was taking very high CPU utilization (more than 64%).

SQL query which is causing the issue is as below :

Name of the Include file : RFITEM_INC

Query:

Around line no. 537:

SELECT (gt_spec_fields)
FROM bkpf
INTO CORRESPONDING FIELDS OF wa_bkpf
FOR ALL ENTRIES IN gt_pos_posted
WHERE bukrs = gt_pos_posted-bukrs
AND belnr = gt_pos_posted-belnr
AND gjahr = gt_pos_posted-gjahr.

On analyzing further , we found that this is a performance bug as this query doesn't follow basic best practices for writing select query with for all entries.

1) Deletion of the duplicate data is not done before using for all entries

2) Sorting of the data is not done before using for all entries

Below is the data we noticed in DBACOCKPIT (which is duplicate and not sorted):

1. Duplicate data being passed in Where clause

@P80 nvarchar(10) N'0100009323'
@P81 nvarchar(4) N'2018'
@P82 nvarchar(4) N'8750'
@P83 nvarchar(10) N'0100009323'
@P84 nvarchar(4) N'2018'
@P85 nvarchar(4) N'8750'
@P86 nvarchar(10) N'0100009323'
@P87 nvarchar(4) N'2018'
@P88 nvarchar(4) N'8750'

2. Non sorted Data being passed in where clause

@P39 nvarchar(4) N'2018'
@P40 nvarchar(4) N'8370'
@P41 nvarchar(10) N'4600000023'
@P42 nvarchar(4) N'2019'
@P43 nvarchar(4) N'8370'
@P44 nvarchar(10) N'5900000016'
@P45 nvarchar(4) N'2018'

There is no note available for this fix. Please fix this performance issue and let us know!

Thanks & Regards,
Ramandeep Kaur

Re: [Bug] Select query doesnt follow basic performance optimization rules

DoanManhQuynh — Fri, 12 Apr 2019 00:39:15 GMT

gt_pos_posted is a sorted table with key bukrs, belnr, gjahr, buzei, budat and it sort acording to those key automatically so you dont have to sort it. also it cant be dupplicate due to:

https://help.sap.com/doc/abapdocu_750_index_htm/7.50/en-US/abaptypes_primary_key.htm#!ABAP_ALTERNATIVE_1@1@

so you should check your input first

Re: [Bug] Select query doesnt follow basic performance optimization rules

Former Member — Fri, 12 Apr 2019 04:34:05 GMT

Hi Quynh Doan Manh

Thanks for your reply, I get that data is sorted.

But the select query is using duplicate date as I mentioned above :

Reason for this issue is as you mentioned this table has key (bukrs, belnr, gjahr, buzei, budat),

so this means the combination of the 5 fields will be unique, but that doesn't mean that combination of 3 fileds "bukrs , belnr and gjahr cant be duplicate".

In the where clause this query is using these 3 fields which are duplicate just as the data I posted.

Proposed fix : put the data of this internal table into some temporary table.

Then delete the duplicate entries on the basis of these three fields (bukrs, belnr and gjahr).

then use this temporary table in the where clause.

This will fix the performance issue which this query is causing.

Thanks & Regards,

Ramandeep Kaur

Re: [Bug] Select query doesnt follow basic performance optimization rules

matt — Fri, 12 Apr 2019 05:54:14 GMT

If you have found a bug in standard sap code, then report it at support.sap.com

Posting details here and requesting a fix is unlikely to get you anywhere.

Re: [Bug] Select query doesnt follow basic performance optimization rules

DoanManhQuynh — Mon, 15 Apr 2019 02:50:57 GMT

ok i get your ideal. but at that point of program there is no enhancement you could use to modify that table. maybe you should raise this issue to sap. beside, i suggest you should try that query in a test program to make sure the performance issue is because of duplicate in that table...