Troubleshooting Database Performance Issues

The operation of a project, always accompanied by performance problems, system queries too slow, how to quickly query and so on

Here is a brief explanation of how to troubleshoot and solve these problems.

During the development process:

1: Do not absolute three-paradigm, appropriate build redundancy can improve query speed, without multiple table association

2: Do not appear circular SQL if you can associate with a table

Example: In this case, each of the t_t1 data is searched, the data in the T_T2 is looped, and the index associated with the table is invalidated, resulting in a slow response.

-- Error Demonstration SELECT A, B , (SELECTfromWHERE ID=from t_t1-- correct practice  SELECTfromWHERE t_t1.id=t_t2.id

3: Configure LOG4JDBC, easy to find the problem of SQL

4: Create a reasonable index

Indexes are useful for finding small ranges of data in a wide range of databases, and not for creating indexes if the column data is mostly the same, or if the amount of data fetched is significant.

During the maintenance process:

1: Change database parameters: when a database is installed, you should change the parameters to make it more beneficial to your project's use

Modify data Buffer parameters: The data cache is to load the disk information into memory, read the memory faster than the disk speed, should be appropriate to improve the data buffer.

Modify the personal sort area parameter: When the data needs to be sorted, more than the provided memory space, part of the data in the sorting area, and part of the disk area, after multiple memory and disk exchange, to complete the sorting, affecting the sorting speed.

2: Periodic Table analysis, so that the database table some information re-entry into force

3: Rebuilding the index, for a table often added delete, the data change is not small, but the index is constantly expanding, so, periodically rebuild the index, conducive to query.

4: Regular collation of table fragments.

5: View the execution plan for slow SQL: Sybase as an example

SET  SHOWPLAN on    go  SQL statement go  SET  SHOWPLAN  OFF  GO     SET  SHOWPLAN on    go  EXEC  pr_ stored procedure  GO  SET  SHOWPLAN  OFF  GO   

Troubleshooting Database Performance Issues