Performance optimization of database application system based on DB2

Combined with the experience of DB2, this paper discusses some principles and methods of database application system performance optimization from four aspects of database design, query optimization, concurrency control and client/server model.

Keyword DB2 performance optimization database design query optimization concurrency control C/S mode

Introduction

DB2 is a high-performance large-scale relational database management system, which is widely used in client/server architecture. The criteria for evaluating system performance optimization are: throughput, response time, parallel capability, etc. This paper discusses the optimization system performance from four angles of database design, query optimization, concurrency control and client/server model.

Design Database

1. Familiar with business system

The familiarity of the business system has a great impact on the performance of the whole database system, a designer who is not familiar with the business, despite the rich knowledge of the database, it is difficult to design the best performance database application system.

2. Normalization and non-normalization

After the database is normalized, data redundancy is reduced, data quantity is small, and data row narrows. So that each page of the DB2 can include more rows, so the amount of data in each area is more, which accelerates the scanning of the table and improves the query performance of a single table. However, when a query involves multiple tables, it takes a lot of connection operations to combine information from each table, resulting in higher CPU and I/O costs. There are many times when there is a need to balance normalization and normalization, with appropriate redundancy information to reduce overhead, and to exchange space costs for time costs. There are order Information table OrderDetail, it records the postmen information, payee information, goods information, price strategy, customer information ... the information is stored in postmen information table, Cashier information table, item Information table, Price Strategy table and customer information table. If you follow normalized requirements, OrderDetail queries must be connected to so many tables or nested queries. If the amount of data in the OrderDetail table is millions, then the time required for a single query can be up to several hours. In fact, as long as the logical validity of the data is guaranteed at design time, a lot of information can be directly redundant in the OrderDetail table, these redundant data can greatly improve the efficiency of the query, thereby reducing CPU and I/O operations.

3. Data stripe

If the number of records in a table exceeds a certain size, then the most basic query operation is also affected, the table needs to be divided according to the date level, the most recent, most frequently used data and historical, not commonly used data, or according to geographical location, department, and so on division. There is also a way to divide-vertical division, that is, a number of columns of a property to split into several small tables, such as the often used attributes in a table, not often used in the next table of attributes, so that you can speed up the scan, improve efficiency.

4. Select data type

The choice of data types for each attribute is largely based on the requirements of the table, but without violating the requirements of the table, selecting the appropriate data type can improve the performance of the system. For example, there is a text column to store a book information, with a blob rather than character (1024), the blob is stored pointers or file reference variables, the real text information can be placed outside the database, thereby reducing the database storage space, so that the speed of the program running. DB2 provides a UDT (user Defined datatypes) feature that allows users to define their own data types according to their needs.