SELECTed experiments
Wednesday, February 21, 2007
I have to apologize again for not writing for quite a while. This time my work on the post was interrupted by, uhm, actual work. :) With the go-live date rapidly approaching there is, unfortunately, no time for my little experiments.
Sooner or later we all have to face the performance issues in the ABAP programs. And so we learn, sometimes the hard way, to write the SELECT statements that use the database index, to avoid SELECTs in the loops and to avoid the temptation of SELECT... ENDSELECT, which is for some reason still taught in the BC400 class. But sometimes all the ‘Performance 101’ stuff is already in place, but we still have to scramble for more ways to make the program run faster. Since this is exactly the point where we are in the project right now, I am on a quest to find more techniques to speed up the data selection.
First thing I’ve always been wondering about is whether it would make any difference if I write the JOIN condition in a different way. Would it matter if I replace ‘A join B’ with ‘B join A’, for example?
Here is a simple code that I’ve tested:
DATA: BEGIN OF i_materials OCCURS 0,In the FROM... JOIN... part I’ve tried all possible combinations of VBAP and VBUP being in different order, like this, for example:
matnr TYPE vbap-matnr,
END OF i_materials.
SELECT vbap~matnr
FROM vbap JOIN vbup ON vbap~vbeln = vbup~vbeln AND
vbap~posnr = vbup~posnr
INTO TABLE i_materials
WHERE ( vbup~lfsta = 'A' OR
vbup~lfsta = 'B' )
AND vbap~werks = '2010'.
FROM vbup JOIN vbap ON vbup~vbeln = vbap~vbeln ANDNone of the combinations performed better than others. However, when I switched VBELN and POSNR (VBELN is the first key, POSNR is second):
vbup~posnr = vbap~posnr
FROM vbup JOIN vbap ON vbap~posnr = vbup~posnr
AND vbap~vbeln = vbup~vbeln
performance dropped by 4.5-5%, which kind of makes sense.
Another thing that SAP Help warns about is to avoid selecting too much data. The most outrageous case is, of course, when someone does SELECT * on a very wide table just to get 2 fields. However, I couldn’t find any statistics on how exactly selecting more data would affect the performance. Here is the same example with some modifications:
DATA: BEGIN OF i_materials OCCURS 0,The result: this runs about 5-10% slower than when only MATNR is selected. This number, of course, depends on the number of records and other factors, but it would make me think twice about bringing more data from the database than I really need.
matnr TYPE vbap-matnr,
vbeln type vbeln,
matkl type vbap-matkl,
END OF i_materials.
SELECT vbap~matnr vbap~vbeln vbap~matkl
FROM vbap JOIN vbup ON vbap~vbeln = vbup~vbeln AND
vbap~posnr = vbup~posnr
INTO TABLE i_materials
WHERE ( vbup~besta = 'A' OR
vbup~besta = 'B' ).
My third exercise was to find out the difference between using RANGES and OR conditions in the WHERE clause. Here is the sample code with RANGES:
RANGES r_auart FOR vbak-auart.And modified with OR:
DATA: w_lines TYPE i,
w_runtime TYPE i.
GET RUN TIME FIELD w_runtime.
r_auart-sign = 'I'.
r_auart-option = 'EQ'.
r_auart-low = 'ZCA'.
APPEND r_auart.
r_auart-low = 'ZOR'.
APPEND r_auart.
DATA: BEGIN OF i_vbak OCCURS 0,
vbak-vbeln TYPE vbeln,
END OF i_vbak.
SELECT vbeln
FROM vbak
INTO TABLE i_vbak
WHERE auart IN r_auart.
GET RUN TIME FIELD w_runtime.
w_lines = LINES( i_vbak ).
WRITE: 'Records found:' , w_lines , 'Runtime: ' , w_runtime.
SELECT vbeln
FROM vbak
INTO TABLE i_vbak
WHERE ( auart = 'ZCA' OR auart = 'ZOR' ).
When retrieving about 16,000 records the runtime of the first code (with RANGE) fluctuated between 45 and 46K ms but second code (with OR) - between 44 and 46K ms. Since difference is so insignificant, I’d probably choose between the RANGE or OR based solely on the readability and functional requirements.
The last exercise was to prove my theory that SELECT-OPTIONS should be replaced by the PARAMETER when all the bells and whistles like ranges, etc. are not really necessary. Here is the code example:
TABLES: vbak.When SELECT-OPTIONS is replaced by the single parameter P_VKORG and WHERE clause by “WHERE VKORG = P_VKORG”, the code runs about 10% faster.
SELECT-OPTIONS s_vkorg FOR vbak-vkorg.
DATA: BEGIN OF i_vbak OCCURS 0,
vbak-vbeln TYPE vbeln,
END OF i_vbak.
SELECT vbeln
FROM vbak
INTO TABLE i_vbak
WHERE vkorg IN s_vkorg
AND ( auart = 'ZCA' OR auart = 'ZOR' ).
The examples above are pretty generic and maybe even lame and, of course, my conclusions can not be considered the ultimate truth. It always makes more sense to check the performance of the specific program in the specific environment than rely on somebody else’s (even mine :) ) assurances.
Which brings us to another subject: how exactly do we track the program’s performance? There are some web pages that I’d like to suggest.
ABAP Performance Tuning Tips & Tricks – this is pure “Performance 101”, a good first stop for a beginner. There is also a fantastic weblog on SDN - The journey to tuning NetWeaver components. Unfortunately, it seems that the journey stopped with the part 1, but it is still very informative and to the point.
If you are experimenting with small pieces of code, like me, there is one trick. In the ABAP editor, go to the menu Environment -> Examples -> Performance examples. (There are some very nice examples, by the way.) Then click on any example, which should open two windows with the code. To measure the runtime of your own code, just copy it over SAP’s code and click ‘Measure runtime’ button. Your code must have correct syntax and also this can be done only on the modifiable client (i.e. most likely you won’t be able to do it in production).
The most suggested things for the performance trace are the Runtime Analysis (menu System -> Utilities) and the SQL Trace (transaction ST05). Somehow I’ve never warmed up to the Runtime Analysis (long story), but ST05 is very useful, especially since most of the performance issues have something to do with the database access. ST05 is described quite well in the SAP Press book SAP Performance Optimization Guide. It’s quite expensive, not that fantastic and is geared more towards the Basis guys, so I wouldn’t really suggest buying it. But if you get a chance to borrow it from your local library or your local Basis guy, parts of it are definitely worth reading.
Final word of caution: always take more than one measurement! Most likely the first number that you'll get will be way off; you need at least 3 runs to get somewhat realistic results. For this post I've measured the examples at least 5 times.
posted by Your Friendly ABAPer @ 21:50,
Direct link to this post
