DB2 database SQL coding optimization and Performance description

This article mainly describes the actual operation steps to maximize the use of DB2 SQL encoding for optimal performance. In practice, when you need to ensure that the use of IBM DB2®Universal Database™(DB2 UDB) and Borland®Tools (such as Delphi™, C ++ Builder™Or Kylix™When an enterprise application is built with the optimum performance.

Programmers can use the capabilities of the DB2 optimizer to process even "difficult" SQL statements and provide effective access paths. However, poor-coded SQL and application code may cause performance problems. By learning several basic principles, you can easily avoid these problems. '

I will show you how the DB2 optimizer works and provide guidelines for writing SQL statements that maximize the efficiency of the optimizer. However, even with the optimization capability of DB2, writing effective SQL statements may be complicated. This is especially tricky if programmers and developers are not familiar with the DB2 database environment. Therefore, before we look into the details of coding SQL to get the best performance, we should take some time to review the basic knowledge of SQL.

Basic knowledge

Because SQL is different from procedural languages, it provides a higher level of abstraction, So it allows programmers to focus on what data they need, rather than how to retrieve data. You do not need to use embedded data navigation commands to encode SQL statements. DB2 analyzes the SQL statements and "behind the scenes" develops data navigation commands. These data navigation commands are called access paths.

Determining the optimal data access path by DBMS removes the burden on programmers. In addition, the DB2 database can better understand the state of the data it stores, so as to generate a more effective and dynamic data access path. The result is that the appropriate SQL can be used for faster application development.

Another SQL feature is that it is not just a query language. You can also use it to define data structures, control data access, and insert, modify, and delete data. By providing a public language, SQL simplifies communication between DBAs, system programmers, application programmers, system analysts, and end users. When all participants in the project use the same language, the collaboration established between them can reduce the overall system development time.

History shows that the most important feature to ensure SQL success is its ability to easily retrieve data using similar English syntax. Understanding this language is much easier than understanding the structure of data pages and program source code:

 
 

  
  
SELECT LASTNAME  
  
  
FROM EMP  
  
  
WHERE EMPNO = '000010';  
 
 

Think about it: when accessing data in a file, programmers must encode commands to open a file, start a loop, read records, check whether the EMPNO field is equal to the appropriate value, check the end of the file, and return to the beginning of the loop.

SQL is very flexible. It uses a free-format structure that allows users to develop SQL statements to suit their needs. Before Execution, DBMS analyzes each SQL request to check whether the syntax is correct and optimize the request. SQL statements do not need to start from any given column. You can enclose them in a row or split them into several rows. For example, the SQL statement of the following single row is equivalent to the preceding three rows:

 
 

  
  
SELECT LASTNAME FROM EMP WHERE EMPNO = '000010'; 
 
 

Another flexible feature of SQL is that you can use many methods with different forms but equivalent functions to create a request. For example, SQL can connect to tables or nested queries. You can always convert nested queries into equivalent connections. You can see other examples of this flexibility in a large number of functions and predicates. Examples of features with equivalent functions include:

 
 

  
  
BETWEEN vs <= / >=  
 
 

IN vs a series of predicates with OR

Inner join vs FROM clause tables that are strings and separated by commas

Outer join vs simple SELECT with UNION and related subqueries

CASE expression vs complex union all statements

This flexibility of SQL display is not always satisfactory, because the SQL formulas with different forms but equivalent functions can provide very different performance. I will discuss the results of this flexibility later in this article and provide the guidelines for developing effective SQL statements.

As I said, SQL specifies the data to be retrieved or operated, but does not specify how the DB2 database completes these tasks. This makes the SQL itself very simple. If you can remember the characteristics of a relational database processing set-at-a-time at a time, you will begin to understand the nature and nature of SQL. An SQL statement can act on multiple rows. Act on a group of data without the need to establish the ability to retrieve and operate data. Define SQL as a non-procedural language.

Because SQL is a non-procedural language, a statement can replace a series of processes. Similarly, this is possible because SQL uses collection-level processing and DB2 optimized queries to determine the data navigation logic. Sometimes, if you do not use SQL statements, a complete procedural Program is required to complete a task that can be completed by one or two SQL statements.