Optimizing DB2 application Performance with SQL statements

One of the important issues to consider when designing a new or analyzing an existing system is the design of the application. Even if the database is well designed and optimized, application design is not appropriate or a database that can cause performance problems. Practice has shown that if there is a design problem with the application, modifying these issues can improve the performance of the application better than adjusting the database configuration parameters.

For example, SQL is a high-level language with great flexibility that extracting the same data from a database can be implemented in different forms of SELECT statements, but the performance of the application varies according to the form of the SELECT statement, because different forms of The SELECT statement has different processing costs. In this case, we should select those SELECT statements that process the low-cost, so that the application will have better performance.

The DB2 Universal database itself provides a SQL compiler, the compiler creates a compiled SQL statement that, when compiled by the compiler, will rewrite the SQL statements to produce a form that is easier to optimize, a process called query rewrite rewrite.

Then, the SQL compiler produces a number of optional execution scenarios that meet the user's query requirements, evaluates the execution cost of each scenario based on the statistics of tables, indexes, columns, and functions, and, finally, selects the scenario with the lowest execution cost, known as "Query Optimization (optimization)."

It's important to it is important to note that, regardless of the access scheme, the SQL compiler (including the two phases of query rewriting and optimization) must select one from it to produce a result set that satisfies the application query requirements, so we should only query the data we need when writing the query code, Do not query for data that you do not need, and the purpose is to ensure that the SQL compiler is able to choose the best access scenario.

When you write a SELECT statement, you typically follow the following seven guidelines:

1. Just specify the columns you want in the select list

When we write a SELECT statement, although we sometimes don't need to use all the fields in the table, but it is still customary to specify all columns in the table by using the * (representing all the fields in the table), which is really simple and convenient to do programmatically, but the consequence is that the application returns some columns that we don't need, System to do some unnecessary processing, to do some work, the cost of the system's valuable software and hardware resources, especially when there are many fields in the table, this wasteful phenomenon is more obvious; and, this is not good programming habits, we should not advocate.

2, using predicates to limit the number of rows returned

In the SQL programming language, according to how predicates are used in the evaluation process and when predicates are used, we divide the predicates into four categories (these four predicates have different processing costs), arranged by performance from high to low as follows:

Scope definition Predicate

Index parameter predicate

Data parameter predicate

Remaining predicates