topic Re: Comparing SELECTs for performance in Application Development and Automation Discussions

Comparing SELECTs for performance

Former Member — Wed, 19 Mar 2014 18:59:40 GMT

I have a long-running function module and through SAT, I've identified its SELECT from BSEG as a potential hotspot and way that I can improve the run time, etc.

Through some research in SCN, I found I can/should use one of the component tables (BSID) to improve performance, but I don't know *how much* of an improvement it will be.

Rather than change the code of the FM and transport it to QA just to analyze performance gains, I was hoping there was some sort of tool that I could use to analyze separate code comparatively.

I thought of making 2 Quickviewer queries (one for BSEG and one for BSID) and then using ST05 to analyze each. Is there a better way?

Basically in PRD, I'd like to compare isolated SELECTs for performance to see what gains I could make.

Thanks

Jeremy H.

Re: Comparing SELECTs for performance

Former Member — Wed, 19 Mar 2014 19:17:01 GMT

Hi Jeremy Hilden,

1.For performance analysis i prefer SAT over ST05 and SE30.

SAT provides best solutions if you want to run a comparative analysis of selects.I really appreciate your thinking.Yes as a developer we should ensure the performance tuning to be u to the mark especially when dealing with tables like BSEG.

Please go through the following documents for better understanding of the tcode.

Please let us know your findings.

Regards,

Kannan

Re: Comparing SELECTs for performance

Former Member — Thu, 20 Mar 2014 02:31:39 GMT

Hi Kannan,

Thanks for the information. I'm still learning more about SAT and those links are helpful. I see SAT can be used to compare traces, however, my question was more about how to plan and model the correction that I propose to make without making a separate program to analyze comparatively, or without making changes to the existing FM.

I think I want to replace the use of BSEG in the FM with BSID, but I want to "speed test" the use of BSID in a similar select in our PRD environemnt, without having to transport changes. I might find, for instance, that BSID doesn't really improve things.

What I've done so far, is create a quick viewer of a select of BSEG and another with BSID and compare them using SAT or ST05. I was just wondering if there is another tool that is more dynamic and allows the use of custom coding.

Thanks

Jeremy

Re: Comparing SELECTs for performance

Former Member — Thu, 20 Mar 2014 05:09:43 GMT

Hi Jeremy,

as per my knowledge BSEG is cluster table which carries lot of information and having around 360 fileds however every cluster table having some alternative tables which are having limited data.

I suggest instead of using bseg use alternative tables.

for GL use BSIS,BSAS

customer use bsid,bsad vendor bsik,bsak.

Those are sufficient which gives entry bseg information and having very limitted data.

Regards,

Raman

Re: Comparing SELECTs for performance

kiran_k8 — Thu, 20 Mar 2014 06:59:23 GMT

Jeremy,

May be you can try exploring the option PEFROMANCE EXAMPLES in SE38.

SE38>>ENIVRONMENT>>EXAMPLES>>PERFORMANCE EXAMPLES

//http://scn.sap.com/thread/3446324

K.Kiran.

Re: Comparing SELECTs for performance

ThomasZloch — Thu, 20 Mar 2014 08:01:08 GMT

You can check a few things before doing the transport:

Code Inspector does static performance checks, e.g. finding SELECT statements with WHERE-conditions that do not use leading fields of an available index (primary or secondary)
study the indexes of the involved tables yourself and compare with your WHERE-conditions, are you using EQ-conditions on indexed columns?
Simulate the access and compare response times using SE16 in the target system, might be quicker than creating a view every time
In case of JOIN selects, then you could turn to quick views, queries or, if possible, chains of SE16

I can't hurt if you post some code here, e.g. the problematic BSEG-select and what you are planning for BSID.

Thomas

Re: Comparing SELECTs for performance

Former Member — Thu, 20 Mar 2014 16:54:36 GMT

Hello,

This is exactly what I was hoping for....except I cannot edit the left and right sides of the tool.

In the following older post, the inability to edit this is discussed. Is there any way to work around the message / block? I'm going to try to debug. Please let me know if there is someway you have found to edit this tool.

http://scn.sap.com/thread/3446324

Thanks!

Jeremy

Re: Comparing SELECTs for performance

Former Member — Thu, 20 Mar 2014 17:02:41 GMT

Thank you Thomas,

I will check each of those out, and you're right, I likely didn't need the quickview built (could have used SE16), but I used the temp program from quick view to analyze both in SAT.

With regards to applying indexes correctly: is there a good guide / past SCN thread about how to do this best? I will search for that.

Here's what I have done so far regarding use of indexes:

1) look in SE11 at the table (e.g. BSID)

2) hit the indexes button at the top

3) if there is more than one index for the table (for BSID there is), find that it is active and find the most applicable one to the kind of data that I'm trying to select (in BSID this seems to be BSID~4)

4) double click the row to show index details, and then check my SELECT to make sure I use as many of these in an EQ operand as possible in my WHERE clause. (it's a given that all of them may not apply).

Thanks

Jeremy H.

Re: Comparing SELECTs for performance

ThomasZloch — Fri, 21 Mar 2014 08:15:45 GMT

You can find some interesting discussions linked here, some also talk about index access:

Regarding your point 4), make sure your WHERE-condition contains as many fields of the index top down without gaps.

Looking at BSID~4:

MANDT	Client
BUKRS	Company Code
REBZG	Number of the Invoice the Transaction Belongs to
REBZJ	Fiscal Year of the Relevant Invoice (for Credit Memo)
REBZZ	Line Item in the Relevant Invoice
KUNNR	Customer Number
UMSKS	Special G/L Transaction Type
REBZT	Follow-On Document Type

MANDT will be included automatically. BUKRS is not very selective (not many distinct values), but you must include it since it is the leading field of the index. REBZG sounds like a selective field, so you can likely use the index effectively by having just BUKRS and REBZG in your WHERE-condition, but the more the better.

Also have a look at transactions DB05 and TAANA which can give you a good idea about the actual data distribution.

Thomas

Re: Comparing SELECTs for performance

Former Member — Fri, 21 Mar 2014 14:45:52 GMT

Thank you all. I've received some great tips and helps in this thread. I have enough to proceed and make meaningful changes to the original code now, so I'll mark as answered.