Tuning SQL Procedures

Do you want to learn some tips for tuning SQL PL? If this is the case, this article will describe some common examples of tuning ibm®db2®universal database™ (UDB) V8.2 for linux,unix®, and Windows®, and will pay special attention to porting from other database management systems Come over the process. Motivation

SQL process Language (SQL PL) is a popular tool in many DB2 developer Toolbox. This is especially true when migrating applications from Oracle, Sybase, or Microsoft®sql servers to DB2. However, the instant availability of SQL PL also poses some problems for it:

First, in the minds of many developers, the development process logic is much easier than writing SQL. Therefore, although DB2 has been shown to be a relational engine with the most cutting-edge optimizer, it has not been fully exploited.

Second, automated tools such as migration Tool kit (MTK) generate SQL PL simulations of the logic created for T-SQL or Pl/sql. As we all know, pure analog performance is always inferior to its prototype.

In the previous article, I described the use of SQL PL Profiler and SQL process tracking to discover performance and logic issues, respectively. In this article, I'll use SQL pl Profiler to illustrate a common example of a set of slow SQL pl, and explain how DB2 V8.2 optimize them alone, or how you can tune SQL pl yourself.

Normal SQL procedures

If you have background knowledge of Microsoft SQL Server or Sybase, you will have a good understanding of these processes. In addition to a call statement, no SQL exists in the application for this example. There are two benefits to be learned from this paradigm:

The first is encapsulation. For example, if the query is very complex, you should not repeat the query multiple times, but should store it in a certain place.

The second is caching. The code in the stored procedure is precompiled. The code in the application is usually dynamic code.

To solve the first problem, DB2 supports the concept of inline SQL PL. DB2 allows simple logic or queries to be encapsulated in SQL functions. When the SQL function is executed from the caller, its body is the macro that extends to the caller.

To solve the second problem, DB2 uses the package cache. The cache remembers not only the recently executed procedure, but also the previously executed statements. Therefore, after the first compilation of the SQL statement, subsequent calls continue to execute the same execution plan. Let's use an example to illustrate:

1 CREATE PROCEDURE get_dept_emps (dept_id dept_id_t)
2 BEGIN
3 DECLARE cur CURSOR with return for
4 SELECT E Mp_name, emp_id from EMP
5 WHERE emp_dept_id = dept_id;
6 OPEN cur;
7 End

Note that you can download all the examples with full DDL from the download section. You can download the SQL PL Profiler used in the full text from DeveloperWorks. The following screenshot shows 100 executions of the normal SQL process described above.

So where is the time consuming? DB2 need to process this call statement. Assuming that the process has previously been cached, DB2 will need to initialize the SQL process to execute it. DB2 then opens the cursor returned from the procedure and processes the locator assigned to the result set. Finally, DB2 can actually get the rows returned. So much work is done just to open a cursor!

Now, let's use inline SQL PL to get the same effect:

1 CREATE FUNCTION get_dept_emps_f (dept_id 

dept_id_t)
2 RETURNS TABLE (Emp_name name_t,
3 emp_id emp_id _t)
4 return SELECT emp_name, emp_id
5 from EMP
6 WHERE emp_dept_id = emp_dept_id;

We now replace call and all locator code in the driver procedure with the DECLARE cursor and its respective OPEN statements, and test the results:

It was impressive! Only by using inline SQL PL can the monitored code be run three times times faster. Even simple SQL procedures that do not return a result set can be replaced with SQL functions. For procedures that return more than one parameter, you can use the SQL table function, which returns a table containing one row-each output parameter is a column. For a procedure that contains one output, you can use only one scalar SQL function. Note that DB2 V8.2 also supports the inclusion of UPDATE, DELETE, INSERT, and MERGE in SQL table functions. This means that you can even use inline SQL PL to encapsulate database changes.