SQL Process performance: tips and Tricks

Brief introduction

There are a number of sources available for how to tune your database systems and applications. DB2 tuning techniques such as OLTP applications (previously published in the ibm®db2® Developer's Garden) some suggestions from table space and index design to buffer pool memory allocation are given through the use of transaction and data parallelism and analysis of query scenarios. These aspects are the basics of performance tuning.

However, there are few specific recommendations on how to organize the logic in the stored procedure itself and focus on its performance. This article provides such a suggestion. Although this article focuses on the SQL process, most of the information provided here also applies to SQL logic embedded in applications or stored procedures written in other languages.

Background knowledge and terminology

Before delving into detailed questions, let's review some of the basic terminology and concepts in DB2 about procedural SQL. Procedural SQL constructs, such as scalar quantities, IF statements, and while loops, are introduced DB2 in the DB2 Universal database™ (UDB) V7 release. Previous DB2 distributions supported the languages of C and Java™ as stored procedures. V7 introduces SQL stored procedures and many other features that can facilitate the development of OLTP applications such as temporary tables, application savepoint, and identity columns.

When you create the SQL procedure, DB2 distinguishes the SQL queries in the procedure body from the process logic. For optimal performance, the SQL query is statically compiled into a section in the package. (for statically compiled queries, the section is primarily composed of the DB2 Optimizer's chosen access scheme for the query.) A package is a collection of sections. For more information about packages and sections, see the DB2 SQL Reference Encyclopedia, Volume 1th. On the other hand, process logic is compiled into DLLs (dynamic link libraries).

During the execution of a procedure, there is a "context switch" between the DLL and the DB2 engine whenever control flows from the process logic to the SQL statement. (In DB2 V8, the SQL process runs under "unprotected mode", which is in the same addressing space as the DB2 engine.) So the context switch we are talking about here is not a complete context switch at the operating system level, but rather a replacement of the DB2 middle class. Reduces the number of context switches in frequently invoked processes, such as those in an OLTP application, or processes that handle a large number of rows, such as the process of performing data cleanup, and has a significant impact on their performance. Several techniques in this article are designed to reduce these context switches.

At the beginning (DB2 common database V7 GA), only SQL procedure language (commonly known as SQL PL) is allowed in SQL procedures. Later (in the DB2 UDB V7.2), a subset of the language is started to be supported in SQL functions and trigger bodies. This subset of SQL Pl is called inline (inline) SQL Pl. The term "inline" highlights the important difference between it and the full language. The SQL PL process is implemented by statically compiling a separate SQL query into a section in the package, and the inline SQL PL function is displayed as its name shows by inline the function body into the query that uses it. We'll look at some examples of inline SQL PL and its usage later.

Now, let's look at some specific tasks that can be used to improve performance when using the SQL process language.

Avoid using multiple statements when you are using only one statement

Let's start with a simple coding technique. A single INSERT row sequence as shown below:

INSERT INTO tab_comp VALUES (item1, price1, qty1);
INSERT INTO tab_comp VALUES (item2, price2, qty2);
INSERT INTO tab_comp VALUES (item3, price3, qty3);

Can be rewritten as:

INSERT INTO tab_comp VALUES　　(item1, price1, qty1),
(item2, price2, qty2),
(item3, price3, qty3);

The time required to execute this multiline INSERT statement is approximately one-third of the original three statements. In isolation, this improvement may seem minimal, but if the code fragment is repeated (for example, the Code field in the loop body or the trigger body), the improvement is significant.

Similarly, the SET statement sequence as shown below:

SET A = expr1;
SET B = expr2;
SET C = expr3;

Can be written as a VALUES statement:

VALUES expr1, expr2, expr3 INTO A, B, C;

If there is no correlation between any two statements, the conversion retains the semantics of the original sequence. To illustrate this point, please consider:

SET A = monthly_avg * 12;
SET B = (A / 2) * correction_factor;

Convert the above two statements to:

VALUES (monthly_avg * 12, (A / 2) * correction_factor) INTO A, B;

The original semantics are not preserved because the expression before the INTO keyword is evaluated in "parallel" mode. This means that the value assigned to B is not based on the value assigned to a, which is the semantics that the original statement expects.

From multiple SQL statements to an SQL expression

As with other programming languages, the SQL language provides two types of conditional constructs: procedural (IF and case) and function (case expressions). In most environments, you can use any of the constructs to express calculations, which is just a matter of preference. However, the logic written using case expressions is more compact and efficient than the logic written with case or IF statements.

Consider the following SQL PL code fragment:

IF (Price <= MaxPrice) THEN
INSERT INTO tab_comp(Id, Val) VALUES(Oid, Price);
ELSE
INSERT INTO tab_comp(Id, Val) VALUES(Oid, MaxPrice);
END IF;

The condition in the IF clause is used only to decide what value to insert into the tab_comp. The Val column. To avoid context switching between the process layer and the data flow layer, the case expression is used to represent the same logic as an INSERT statement:

INSERT INTO tab_comp(Id, Val)
VALUES(Oid,
CASE
WHEN (Price <= MaxPrice) THEN Price
ELSE MaxPrice
END);

It is worth noting that case expressions can be used in any context where you want to have a scalar value. In particular, you can use them on the right side of the assignment symbol. For example:

IF (Name IS NOT NULL) THEN
SET ProdName = Name;
ELSEIF (NameStr IS NOT NULL) THEN
SET ProdName = NameStr;
ELSE
SET ProdName = DefaultName;
END IF;

Can be rewritten as:

SET ProdName = (CASE
WHEN (Name IS NOT NULL) THEN Name
WHEN (NameStr IS NOT NULL) THEN NameStr
ELSE　DefaultName
END);

In fact, this particular example has a better solution:

SET ProdName = COALESCE(Name, NameStr, DefaultName);