How to evaluate performance

[Former Member]

Hi,

In SE30 we have 3 componants called <b>ABAP</b>,<b>Database</b> and <b>System</b>.What is the upper limits of theses?

If in my program---

ABAP = 74.5%

Database = 2.4%

Systems = 23.2%

-

---

Total = 100%

Then how good is my program? How can I evaluate my program after this report? What are the performances parameters?

Help needed.

[Former Member]

Hi,

In SE30 we have 3 componants called <b>ABAP</b>,<b>Database</b> and <b>System</b>.What is the upper limits of theses?

If in my program---

ABAP = 74.5%

Database = 2.4%

Systems = 23.2%

-

---

Total = 100%

Then how good is my program? How can I evaluate my program after this report? What are the performances parameters?

Help needed.

[Former Member]

SE30 does not tell you how good your program is, except in the total time it reports for the program to run - the smaller the better. Rather it is a tool to help you analyse where to look for improvement in a program that you know is not performing as well as it could.

In SE30 it is not the percentages that affect performance - they just show you how much of your program run time is spent in each of the three areas. This helps with analysis - your example has 2.4% in database, so if you are trying to speed it up loking at the SELECT and other database statements would be a waste of time - the program is spending most time in ABAP so that is the first place to look.

If you ran SE30 with the Measurement Restriction variant "DEFAULT" on the first screen you are only given minimal options on the results screen to explore further. You get buttons for "Hit List" and Filter" only. The hit list gives some details of where in the program the time is being spent.

Creating your own variant can give more detailed information - with the restriction that more detail means more space for the analysis and it may fall over if it runs out of space. Use the copy button to copy DEFAULT to your own variant and then click change for this variant. On the three tabs shown, the first two are mostly self-explanatory - use them to limit what is analysed. On the third tab you can set the file size (there are limits on this set by basis) and aggregation. An Aggregation level of "None" gives the most detail in the analysis.

When run with no aggregation, you have a dozen or so different buttons available to drill into the results. You can see which database statements take the most time, which forms are the slowest, Hit lists for Classes, Methods, Events, etc. All this lets you see where in your program most time is being spent.

One major reason that poor performance occurs is the result of doing something slow a lot of times - when you can see where your program is doing this you can often see how moving or changing a few statements can have a major effect on performance.

Hope this helps.

Andrew

[Former Member]

Andrew has answered your question nearly completely.

You should check the total time and conclude from it whether your program should be faster.

Th percentages can only be guidelines, the always add up to 100%! So if your program is a dummy program, i.e. very fast, or has big problems, i.e. very slow, then the percentages are useless. A good program should have less than 10% system, and more ABAP than DB (data are read from DB and processed).

High system => many loads, you should never trace the initial execution!!!!!!

High DB => please check SQL Trace!

For beginners the hitlist is the most interesting information, order by net times and check to Top 10 to 20.

Do not change the measurement setting, the aggreagtion 'none' is usually much more information than you can interpret.

Better use:

+ third tab aggregation 'by call', larger file size

+ second tab all active, but not the internal tables and not the kernel.

+ first tab, no restriction

Display, go to filter and activate everything.

Siegfried

[Former Member]

SQL Trace transaction ST05

The trace list has many lines that are not related to the SELECT statement in the ABAP program. This is because the execution of any ABAP program requires additional administrative SQL calls. To restrict the list output, use the filter introducing the trace list.

The trace list contains different SQL statements simultaneously related to the one SELECT statement in the ABAP program. This is because the R/3 Database Interface - a sophisticated component of the R/3 Application Server - maps every Open SQL statement to one or a series of physical database calls and brings it to execution. This mapping, crucial to R/3s performance, depends on the particular call and database system. For example, the SELECT-ENDSELECT loop on the SPFLI table in our test program is mapped to a sequence PREPARE-OPEN-FETCH of physical calls in an Oracle environment.

The WHERE clause in the trace list's SQL statement is different from the WHERE clause in the ABAP statement. This is because in an R/3 system, a client is a self-contained unit with separate master records and its own set of table data (in commercial, organizational, and technical terms). With ABAP, every Open SQL statement automatically executes within the correct client environment. For this reason, a condition with the actual client code is added to every WHERE clause if a client field is a component of the searched table.

To see a statement's execution plan, just position the cursor on the PREPARE statement and choose Explain SQL. A detailed explanation of the execution plan depends on the database system in use

[Former Member]

Hi all,

I use SE30 for runtime analysis evaluation. After executing my program, SE30 give me:

ABAP 1.1%

Database 98.5%

System 0.4%

Then, I use ST05 - SQL Trace for performance analysis. In this, I activated the trace. It gave me a long list of extended trace. Since my database performance is 98.5%, I go to the FETCH operation under column Op. in the trace list. It seems like my problem occurs during the FETCH operation. How do I reduce this duration? Can I do anything about it?

The details of trace list:

11:17:33.241 4 YZZ_SDP COEP OPEN 110 0 R/3 SELECT WHERE "KOKRS" = 'MY01' AND "OBJNR" = 'OR000000729608' AND "WRTTP" = '11' AND "MANDT

11:17:33.241 45,843,037 YZZ_SDP COEP FETCH 110 575 1 1403 R/3

Please advice on how do I go about reducing the FETCH duration.

Thanking you in advance.

[Former Member]

Hi,

you need to check out the sequense of all fields used in where condition ie

WHERE "KOKRS" = 'MY01' AND

"OBJNR" = 'OR000000729608' AND

"WRTTP" = '11' AND "MANDT

I think following reasons may be it has taking more time for database

1) you have to check all the fields in where condition are key-fields or not, if not obiously perforamnce makes slow.

2) you have to check the sequense which is according to database table or not, if not change the sequence.

Rewards with points if it is helpful.

Regards,

Vijay

[Former Member]

The problem with your select from COEP appears to be that it is not using any index efficiently.

The fields in your WHERE block are: KOKRS, OBJNR, WRTTP, MANDT

The fields in the primary key of COEP are: MANDT, KOKRS, BELNR, BUZEI

- so if the select uses this key it will scan every record in the table to find any matching your requirements.

The indexes (standard) on COEP are:

1: MANDT, LEDNR, OBJNR, GJAHR, WRTTP, VERSN, .....

2: MANDT, OBJNR, KSTAR, GJAHR, PERIO, PAROB1

4: MANDT, TIMESTMP, OBJNR

Your select will not use index 4 as TIMESTMP is not supplied, leaving a match only on MANDT. (Index matching is done in order till a missing field occurs)

On index 2 it gets a better match, MANDT and OBJNR.

Index one would potentially give the best match, but is missing LEDNR and GJAHR for a match only on MANDT again.

Given your ST05 trace is showing a time of 45,843,037 to return a single record, I believe the database optimizer is choosing to do a full table scan for the record and not using any key. This could be because the order your fields are specified in appears wrong.

It appears you have coded "CLIENT SPECIFIED" in your SELECT statement, and specified MANDT at the end of the WHERE block.

I suggest you try without "CLIENT SPECIFIED" (which normally causes MANDT to be put as the first field in the WHERE block), and specify the LEDNR field (and also the GJAHR, VERSN, ... fields if you know them). Specify the fields in the order they appear in the index. You can specify further fields that do not match the index fields after you have given the 3 to 5 fields matching the INDEX.

Do not leave any of the index fields out, even if they only ever have a single fixed value - you know this, but the optimiser doesn't and scans the table just to make sure. Once you have a match on 3 or more index fields, the optimiser will generally decide to do an index range scan rather than a table scan - it reads all index values matching the start you have specified and then gets these records from the database. It may still decide to scan the whole table if the initial check against the index indicates more than a set percentage of the total records in the table are to be read. It bases this on the record numbers from database statistics for the index and table, so if either of these are out of date and innacurate you get the wrong result.

Also note, that specifying too many or too complex conditions in the where block can also result in a table scan - effectively the optimizer throwing its hands in the air and saying "I don't know how to work this out - so I'll do it this way"

Fields not in the index can also be loaded and checked after the select using an IF test or CHECK statement. This is useful when trying to reduce the complexity of a where block. Sometimes reading 10 records and discarding 9 can work out quicker than trying to read just the one record by specifying extra conditions.

So I suggest trying the fields matching the index in order and seeing what happens. The final option is to use a database hint to force use of the correct index, but this is not recommended as the index specified may not be the best method in all cases - a table scan is the best way if you actually want to read all or most of the table records.

Andrew

[Former Member]

Hi Vijay,

Thanks for your reply. I have re-arrange the conditions to follow the sequence. Then, I activate the program and run it in SE30 and ST05 again following the required steps. It still give me high duration of FETCH operation.

[Former Member]

Hi Andrew,

Thanks for your reply. I am doing the condition according to functional specifications given by the functional people. They states that I should use field that is not a key as the condition. Below is the COEP select statement:

select kokrs belnr objnr perio gjahr beknz wkgbtr kstar mbgbtr from coep client specified
                  into table it_coep
                    where mandt = sy-mandt
                      and kokrs = it_coas-kokrs
                      and belnr = l_belnr
                      and mbgbtr ne 0.

Other COEP select statement exist in my program:

select kokrs belnr objnr perio gjahr BEKNZ wkgbtr from coep client specified
            into table it_coep
            for all entries in it_coas
                    where mandt = sy-mandt
                      and kokrs = it_coas-kokrs
                      and objnr = it_coas-objnr
                      and wrttp = '11'.

Please advice on how do I go about changing the above line of codes as I am new in performance tuning. Note that, the above condition was given by the functional analyst and they said it is the only field that can be use to extract the required row in COEP table.

Thanks in advance.

[Former Member]

You should try the following:

select kokrs belnr objnr perio gjahr beknz wkgbtr kstar mbgbtr from coep 
*      client specified   "<<<delete this bit
                  into table it_coep
                    where  kokrs = it_coas-kokrs   " remove 'mandt = sy-mandt and'
                      and belnr = l_belnr
                      and mbgbtr ne 0.

Using client specified with mandt = sy-mandt is a waste of time - the SAP system adds this automatically if you leave both out. Only use client specified in the extremely rare cases where an update in another client is required.

For your second statement (which is the one taking all the time), try:

select kokrs belnr objnr perio gjahr BEKNZ wkgbtr from coep
*   client specified   "<<<<  again, remove this
            into table it_coep
            for all entries in it_coas
*                    where mandt = sy-mandt and "<<< remove this line
*                          kokrs = it_coas-kokrs "<<< remove this condition also
                     where lednr = '0'  " or '1' or whichever you are using (see note below)
                       and objnr = it_coas-objnr
* " if you know fiscal year required, include one of the follwing 2 lines:
                       and gjahr = p_gjahr  "if 1 year only needed
                       and gjahr between p_gjahr1 and p_gjahr2  "if many years needed
                       and wrttp = '11'
                       and kokrs = it_coas-kokrs. "<<< add this condition back in at end

Hopefully this will cause the database to pick index 1 on the table which is the most efficient index given the fields you need to select by.

The LEDNR field probably always has a single fixed value - I cant remember if it is one or two chars long - if two, value possibly '01'. If your site is configured with multiple LEDNR values, it is unlikely that transactions/ amounts from different ones should be mixed in the report. The same question arises for the KOKRS field - is it always a single value or are you reporting transactions from accross multiple controlling areas? This would be unusual.

This where block may still be too convoluted for the optimiser - you may have to simplify it down to:

select kokrs belnr objnr perio gjahr BEKNZ wkgbtr WRTTP from coep
            into table it_coep
            for all entries in it_coas
                     where lednr = '0'  " or '1' or whichever you are using
                       and objnr = it_coas-objnr.

delete it_coep where wrttp <> '11'.
sort it_coas by kokrs objnr. 
data: l_index like sy-tabix.
loop at it_coep.     "<<< assuming header line - if not need "into wa_coep"
  l_index = sy-tabix.
  read table  it_coas with key kokrs = it_coep-kokrs 
                                           objnr =  it_coep-objnr
                                     binary search.  "<< table it_coas sorted by these  
  if sy-subrc <> 0.
    delete it_coep index l_tabix.
  endif.
  endloop.

Even though this seems like it will take longer because of dual processing, it is probably a lot faster because of proper use of database index.

Hope this helps

Andrew

[Former Member]

Hi Andrew,

Thanks for your reply. I will try it out as soon as I finish off my 2 other programs.

I will get back to you when I have done changing the condition you had suggested and let you know the outcome.

Thanks alot!

Regards,

Fauzah

[Former Member]

Hi

The percentage across each of the areas ABAP/ Database/System shows the percentage of total time used for those areas and load on these areas while running the program . The lesser the database load faster the program runs.

rewrad if usefull

[Former Member]

Please note the total time from the ABAP trace and not only the percentages!!!

Go to SQL, reduce the long list, by using 'table tist -> Summarize by SQL statement'

The top en try on this list and the top entry in the hitlist of the SE30 (sorted by net times) should refer to the same ABAP coding in your case, as your porblem is on the DB.

Check the explain for this statement in the SQL trace!

Siegfried