A Database-Freak’s View Part 2

In my first post:  Application Development from a Database-Freak’s Point of View, I talked about some of my pet peeves regarding application development.

This time around, I’d like to touch on some of those points in more detail.

Frameworks:

As ABAP developers, we have access to one of the largest, most powerful frameworks out there.

To mention but a few of its parts that fit into the context of the this article:

ABAP Dictionary or Data Dictionary (whichever brand you prefer) 😕

A very awesome feature for defining and maintaining all types of data objects, including database tables, views etc…

Open SQL

Not a database query language in and of itself, but a limited subset of standard SQL with some added specialties for querying data in an SAP application server environment.  While its limitations can really torture a database developer, Open SQL’s real strength lies in the database interface, which is able to translate what the ABAP developer wants to a pretty decent native SQL on all SAP supported database management systems.  The data buffer also provides some key advantages.

• Used properly, Open SQL can be very efficient.
• It’s very easy to use improperly, though, when a developer is not paying attention.

SAP Standard

Each SAP Business Process brings with it a bunch of reuse components for working with its data.  These components, often in the form of function modules, contain a lot of business logic, which makes using them the best and easiest way to load the right data into your program.

The advantages to using these reuse components are pretty easy to see:

• You don’t have to reinvent or maintain the business logic.
• If SAP makes changes in the future, these changes automatically take effect.

There are a couple of disadvantages, though, that a developer needs to keep in mind:

• While these components are easy to use, it can be tempting to use them over and over again in different places in the same program.
• These components are not optimized for every scenario a programmer faces.

At this point you may be asking yourself; “WT%? why am I reading this?  I’ve been programming for years.”

Answer: it doesn’t hurt to look at things from another perspective from time to time.

Please allow me to rant on a bit. 😈

Use, don’t Misuse: Open SQL

Database systems are optimized for set-based operations; meaning, “do what you need to do in as few round trips as possible.”

Open SQL provides techniques to do things in a number of ways.  While there is no ‘one size fits all’ in software development, a bunch of select singles inside a loop can usually be replaced with something more efficient.

One misconception I had when starting out in ABAP, was the difference between ‘SELECT…ENDSELECT’ and ‘SELECT INTO TABLE…’.  Needless to say, I was somewhat concerned about the performance of the first technique. However, I’m happy to say, the database interface uses an Array-Fetch with both techniques. 🙂   It just has to buffer the data longer during a SELECT…ENDSELECT Operation.

While SELECT INTO TABLE… is the prefered method in the text books, it’s up to the developer to choose the appropriate method for each situation.

Where we do need to be especially careful, though, is when looping over a resultset to query further tables.

The following code is relatively straightforward:

LOOP AT itab INTO wa.

     SELECT *

     APPENDING TABLE lt_dbtab

     FROM dbtab

     WHERE field = wa-field.

ENDLOOP.

Depending upon the number of records in itab, you may really need to reconsider here, as this actually does send a separate request to the database for each row.

We have a couple of options in this case.

Range Table:

DATA:     rangetab TYPE RANGE OF dbtab-field,

               wa_rangetab LIKE LINE OF rangetab.

LOOP AT itab INTO wa.

                    wa_rangetab-sign = ‘I’.

                    wa_rangetab-option = ‘EQ’.

                    wa_rangetab-low = wa-field.

     APPEND wa_rangetab TO rangetab.

ENDLOOP.

SELECT *

INTO TABLE lt_dbtab

FROM dbtab

WHERE field IN rangetab.

Keep in mind, there are database restrictions on the allowable size of statements.  With this technique, a bunch of ORs are appended to the WHERE clause and if the resulting statement is too big for your particular database, you’ll end up with a short dump.  The following scn thread could be helpful:

Max limit of Selection-option single values?

SELECT FOR ALL ENTRIES:

SELECT *

               INTO TABLE lt_dbtab

               FROM dbtab

               FOR ALL ENTRIES IN itab

               WHERE field1 = itab-field1

               AND field2 = itab-field2

               AND field3 > ‘100’.

This technique is more practical because you can effectively “JOIN” on more than one field. There are also no implicit size limitations on itab.

One thing to be especially careful about, though, is not selecting for all entries in an empty itab.  This will select the whole database table for you. 🙁

You should also ensure that you have no duplicates in itab.

JOINS and Database VIEWS:

Consider these options as well.  While not always appropriate, or even usable, in many situations they allow you to save on database traffic AND even shorten your code. 🙂

Use, don’t Misuse: SAP Standard

The little LOOP…ENDLOOP example in the last section was pretty easy to optimize.  In the real world, though, things get pretty complicated pretty quick.  Inside a LOOP AT itab ENDLOOP you could be doing a whole lot of things. These whole lot of things are usually packed up nicely in subroutines, which are  themselves packed into various includes.  These subroutines make use of reuse components, either components of your own making or from SAP Standard.

As a program grows over the years, (which is a good sign, cause it’s still being used), it can be easy to lose track of what is being done and where.  In many cases, the program is enhanced by adding a new subroutine (in a new include), which then calls a reuse component.  And here’s where it gets tricky.  The nature of frameworks makes it easy to keep doing the same thing over and over, each time you need the result in a different place.

When using your own components, you can optimize them for your needs.  When using SAP Standard components, you need to be aware of all the other things they are doing, that you don’t need.  As I mentioned earlier, it’s often the wise choice to use SAP Standard, but you need to design your programs to use these components effectively.

In either case, a good rule of thumb is, don’t ask your database for the exact same data more than once in the context of your program.

Take the time to run an SQL Trace in ST05.

If you see something like this in the Value-Identical Statements aggregate, “Have a Problem with it!”.

Now that’s not going to take down your database if you do it for one record.  You can probably even get away with it over a couple thousand records.  But remember, yours isn’t the only program looking for data.  And if it still seems to run fast enough on the development machine, don’t assume it will work just as fine on a database with 10 years worth of production-data.

Guess that’s enough for now.

If I’ve been vague on any of these points, or you have a different view on things, please leave a comment.  I’m always willing to learn. 🙂