The (complex) art of code tuning

I was tunning a new program I made (yes, the first thing to be tuned must be the programmer, but my customer still thinks I’m a good one πŸ™‚ ) and had a severe problem with two SQLs. Looking around for more information about it, I found some nice things to share…

Here are the SQLs:
SELECT field_list
INTO TABLE lt_vbap
FROM VBAP
WHERE conditions.
CHECK sy-subrc = 0. "Sample code, don't blame me
SELECT field_list
INTO TABLE lt_lips
FROM LIPS
FOR ALL ENTRIES IN lt_vbap
WHERE conditions.

Both SQL are using the proper (appropiate, doc) indexes, but I’m having two problems:

1. performance: I need my program to do the work as fast as possible
2. memory: had a nice DUMP in my test system because memory leaks

I made a lot of changes to my programs, sacrifying a very impressive log (made with WRITEs :P) to fix my memory leaks (which memory leaks?) and changing all global internal tables into local ones (defined at FORM levels, and FREE’d -yes, I know if they are local, it’s supposed they are cleaned at the ENDFORM, but I don’t trust that SAP help now-).

Now I’m facing the mother of the lamb (Spanish expression: “La madre del cordero” referring to the most important piece of the problem… keep the jokes for the GD forum, please) with VBAP + LIPS.

My first though has been: “hey dude, drop the second SELECT FOA and use a nested SELECT SINGLE loop”.

Before do such that thing, I used some time reading the net about those things and found three interesting blogs (all from SDN :/, I missed abaplog this time for time reasons):

1-
JOINS vs. FOR ALL ENTRIES – Which Performs Better?, by Rob Burbank
The most important thing of that blog, in my modest opinion, is this comment from Peter Inotai:

I believe the result also depends on the DB tuning. There are several FOR ALL ENTRIES relevant profile parameters.
It’s explained in the following OSS notes:
Note 48230 – Parameters for the SELECT … FOR ALL ENTRIES statement
Note 652634 – FOR ALL ENTRIES performance with Microsoft SQL Server
Note 634263 – Selects with FOR ALL ENTRIES as of kernel 6.10
It’s worth to check these parameters, before making any decision, which way to choose.

Impressive! I must not rely in SAP information, because it doesn’t come all toguether (ok, I knew that). I must check my syntax and for some special cases, ask my basis guys (eek!) about the DB tuning… Then I moved to the next one

2- Performance – what will kill you and what will leave you with only a flesh wound, by Rob Burbank too
Not bad. Some interesting facts, but again, one comment made me raise an eyebrow. The comment lead me to the next blog:

3- Anyone Got Some Real Benchmark Stats on “For all Entries”???,
by David Halitsky
Just the header made me smile. I believed from a long time we (coders) rely a lot in urban legends and/or “guru” comments. That guy doesn’t? I love it!
But the main fact found there was the performance of fine tuned loop’ed SELECTs. It’s just the thing I was thinking to do with my program… I though about it a bit, and I agree with that man.

The last blog, comes from the same person, and it’s the second part of the number 3 πŸ˜‰

4- “Yes, Virginia, the check is in the mail” (or, why you can’t trust SQL),
by David Halitsky too
Nothing really new under the sun, but shows some nice conclusions I loved to get by myself πŸ˜‰

—————————————
I will do it, of course, but I’d love to read some extra wise words about the whole thing. I posted the same though in this thread of SAPFans.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s