In-depth research on optimizing the performance of DB2 database application systems

Bytes. The criteria for evaluating system performance optimization include throughput, response time, and parallel capability.

Design Database

1. Familiar with business systems

Familiarity with the business system has a great impact on the performance of the entire DB2 database system. A designer who is unfamiliar with the business, despite having a wealth of database knowledge, it is also difficult to design a database application system with the best performance.

2. Normalization and non-Standardization

After the database is normalized, data redundancy is reduced, data volume becomes smaller, and data rows become narrower. In this way, each page of DB2 can contain more rows, so there is more data in each partition, which accelerates table scanning and improves the query performance of a single table. However, when querying multiple tables, you need to use a lot of Join Operations to combine the information from each table, resulting in higher CPU and I/O costs.

In most cases, we need to maintain a balance between normalization and non-standardization, use appropriate redundant information to reduce system overhead, and use the space price in exchange for the time cost. There is an order information table OrderDetail, which records the courier information, cashier information, item information, price policy, customer information ..... The information is stored in the courier information table, cashier information table, item information table, price policy table, and customer information table.

According to the standardization requirements, OrderDetail must be connected to so many tables or nested queries. If the data volume in the OrderDetail table is millions, the time required for a query may be several hours. In fact, as long as the logic validity of the data is ensured during the design, a lot of information can be directly redundant in the OrderDetail table. These redundant data can greatly improve the query efficiency, this reduces CPU and I/O operations.

3. Data striping

If the number of records in a table exceeds a certain size, the most basic DB2 database application system performance query operations will also be affected. You need to divide the table by date level, divides recent and most frequently used data into historical and infrequently used data, or by geographic location or department.

There is also a way of partitioning-vertical partitioning, that is, dividing a table with many attribute columns into several small tables, such as placing frequently used attributes in a table, attributes that are not frequently used are placed in another table, which can speed up table scanning and improve efficiency.

4. Select a data type

The data type selected for each attribute depends on the requirements of the table to a large extent. However, selecting an appropriate data type without violating the requirements of the table can improve the system performance. For example, a text column stores the information of a book, BLOB instead of character (1024), BLOB stores pointer or file reference variables, and real text information can be stored outside the database, this reduces the database storage space and increases the running speed. DB2 provides the UDT (User-Defined PES ypes) function. You can define your own data types as needed.

5. Select an index

Index is an important data structure in DB2 databases. Its fundamental goal is to improve query efficiency. Currently, most database products adopt the ISAM index structure first proposed by IBM. You can use indexes to quickly, directly, and orderly access data. Although indexing accelerates queries, it also reduces the data update speed, because new data must be added not only to tables, but also to indexes. In addition, indexing requires additional disk space and maintenance costs. Therefore, you must use the index properly:

Indexes are created on Attribute columns that are frequently connected but not specified as foreign keys.

Indexes are created on columns that are frequently sorted or grouped (that is, group by or order by operations. Sorting or grouping by index can improve the efficiency.

Create a search for columns with different values that are frequently used in conditional expressions. Do not create an index for columns with fewer values.

If there are multiple columns to be sorted, you can create a compound index on these columns, that is, the index is composed of multiple fields.

The above content is an in-depth exploration of the performance optimization of the DB2 database application system.