Basic tutorial for SQL coding Optimization for IBM DB2 database: classic edition!

The following articles mainly introduce the basic tutorial of SQL coding optimization for the IBM DB2 database. The importance of such a specific query is that the more important the query is, you may need to optimize indexes by creating indexes. If you are coding the query that the CIO is running every day, make sure it provides the best performance.

Therefore, it is important to create an index for this specific query. On the contrary, staff queries may not be as heavy as they are, so they may need to use existing indexes to execute queries. Of course, the decision depends on the importance of the application to the business-not just the importance of the user.

Index design involves much more content than I have discussed so far. For example, you may need to consider index overloading to achieve index-only access ). If all the data to be searched for in the SQL query is included in the index, DB2 may only use the index to meet this request. Consider the preceding SQL statement. Given information about EMPNO and DEPTNO, we are looking for LASTNAME and SALARY.

We also start from creating indexes for the EMPNO and DEPTNO columns. If we include LASTNAME and SALARY in the index, we do not need to access the EMP table because all the data we need is already in the index. This technology can greatly improve performance because it reduces the number of I/O requests.

Remember: it is neither careful nor even impossible to make each query an index-only access. You should use this technique with caution to use SQL statements that are particularly tricky or important.

SQL coding principles

When writing SQL statements that access DB2 data, make sure that the following three encoding SQL rules are followed for optimal performance. Of course, SQL Performance is a complex topic, and it may take a lifetime to understand the nuances of SQL Execution Methods. However, these simple rules allow you to get on the right track for developing high-performance DB2 applications.

The first rule is to always provide only the columns to be retrieved in the SELECT list of each SQL SELECT statement. The other statement is "Do not use SELECT *". SELECT * indicates that you want to retrieve all columns in the table being accessed. This applies to quick and dirty queries that are obtained in a "quick but inappropriate way", but it is a bad practice for applications because:

The DB2 table may need to be changed in the future to include additional columns. SELECT * will also retrieve those new columns, and your program may not be able to process additional data without time-consuming changes.

DB2 will consume additional resources for each column returned by the request. If the program does not need data, it will not look for it. Even if the program requires each column, it is best to explicitly search for each column based on the name in the SQL statement to increase clarity and avoid previous errors.

Do not look for things you already know. This seems obvious, but most programmers have violated this rule. For a typical example, consider the following SQL statement errors:

 
 

  
  
SELECT EMPNO, LASTNAME, SALARY  
  
  
FROM EMP  
  
  
WHERE EMPNO = ’000010’; 
 
 

Give up? The problem is that EMPNO is included in the SELECT list. You already know that EMPNO will be equal to the value "000010", because that is what the WHERE clause wants DB2 to do. However, EMPNO is listed in the WHERE clause, and DB2 will perform due diligence to retrieve this column. This causes additional overhead to reduce performance.

Use the WHERE clause in SQL to filter data, instead of using it everywhere in the program. This is also an easy mistake for new users. Before DB2 returns data to a program, it is best to filter data by DB2. This is because DB2 uses additional I/O and CPU resources to obtain each row of data. The less rows passed to the program, the higher the SQL efficiency:

 
 

  
  
SELECT EMPNO, LASTNAME, SALARY  
  
  
FROM EMP  
  
  
WHERE SALARY > 50000.00; 
 
 

This SQL statement is better than reading all data without using the WHERE clause, and then checking whether SALARY is greater than 50000.00 in the program.

Use parameterized query. Parameterized SQL statements contain variables, also known as parameters (or parameter tags ). Typical parameterized queries use these parameters instead of text values, so the WHERE clause condition can be changed at runtime. Generally, the program is designed to provide the parameter values before the end user can run the query. This allows a query to return different results based on different values provided to the parameter.

The major performance benefit of parameterized query is that the optimizer can specify the access path that can be used when statements are repeatedly executed. Compared with a new SQL statement that requires a new value in each WHERE clause, this increases the performance of the program.

However, these rules are not the final and highest goal of SQL Performance Optimization-they are by no means. You may need additional and in-depth tuning. However, following the previous rules will ensure that you do not make the "Newbie" error to reduce application performance.

Skills for developing specific IBM DB2 database applications

Whether you are using Delphi, C ++ Builder, or Kylix, some tips and guidelines will help you ensure good performance when accessing DB2 data. For example, in some cases, using dbExpress TM instead of ODBC/JDBC or ADO can improve query performance. DbExpress is used from Delphi (or Borland Kylix™) Cross-platform interface for Processing dynamic SQL.

Make sure that the COMMIT statement is frequently issued in your application. The COMMIT statement controls the unit of work. When a COMMIT Statement is issued, all the work since the previous COMMIT statement is "forever" recorded in the IBM DB2 database. Before sending a COMMIT, you can use the ROLLBACK statement to roll back the job.

When data (using INSERT, UPDATE, and DELETE) is modified but no COMMIT is issued, DB2 will apply a lock to the data and keep the lock-this lock will cause other applications to time out while waiting to retrieve the locked data. By issuing a COMMIT statement upon completion of the work and ensuring that the data is correct, the data is released for use by other applications.

In addition, you must consider the usage when building an application. For example, when a specific query returns thousands of rows to the end user, you must proceed with caution. For online interactions between programs and end users, a few hundred lines of data are rarely used. You can use the fetch first nROWS ONLY clause in an SQL statement to limit the amount of data returned to the query. For example, consider the following query:

 
 

  
  
SELECT EMPNO, LASTNAME, SALARY  
  
  
FROM EMP  
  
  
WHERE SALARY > 10000.00  
  
  
FETCH FIRST 200 ROWS ONLY; 
 
 

This query returns only 200 rows. It does not matter if more than 200 rows meet the condition. If you try to FETCH more than 200 rows from the query, DB2 uses + 100 SQLCODE to indicate that the data has ended. This method is useful when you want to limit the amount of data returned to the program.

DB2 supports another clause named optimize for nROWS. This clause does not limit the number of rows to be returned to the cursor, but it may be helpful in terms of performance. Use the optimize for nROWS clause to tell DB2 how to process SQL statements. For example:

 
 

  
  
SELECT EMPNO, LASTNAME, SALARY  
  
  
FROM EMP  
  
  
WHERE SALARY > 10000.00  
  
  
OPTIMIZE FOR 20 ROWS; 
 
 

This tells DB2 to try to access the first 20 rows as soon as possible. If your Delphi application displays 20 rows of data retrieved from the IBM DB2 database at a time, this will be very useful.

For read-ONLY cursors, use the for read only clause to ensure that the cursor is unambiguous. Delphi cannot execute location update in the DB2 cursor. Therefore, attaching for read only to each SELECT statement can make the cursor a non-Ambiguous READ-ONLY cursor, which is helpful to DB2. For example:

 
 

  
  
SELECT EMPNO, LASTNAME, SALARY  
  
  
FROM EMP  
  
  
WHERE SALARY > 10000.00  
  
  
FOR READ ONLY; 
 
 

Conclusion

Understanding SQL coding to get the best performance will immediately increase the performance of your Delphi enterprise applications. But I only revealed the tip of the iceberg. You need to learn more complex types of SQL, including connection, sub-selection, and union. You also need to learn how to best write these SQL statements and how to find the access path selected by DB2 to meet your SQL requests. Indeed, there are still many things to learn. However, you have learned some preliminary knowledge about how to use DB2 SQL to the maximum extent and enjoy this knowledge.