SQL statement performance test

As the complexity of the software system increases, there may be many bottlenecks in the performance of the software system, which may be code, application server, front-end interface layer, or middleware, also, it may be Database. There are two bottlenecks in the database: one is the database architecture and design problems, and the other is submitted for processing. SQLThe statement is incorrect.

According to statistics, in the database application system, most of the performance problems are caused by inefficient SQL statements, accounting for more than 80% of the performance problems. This reminds us that the SQL statements in the Software SystemTestAnd Performance Tuning cannot be ignored. We need to optimize SQL statements to avoid many database performance problems.

　　SQL test and optimization process

A standard SQL test and optimization process requires four iteration steps:

(1) identify problematic SQL statements

The first step is to find the SQL statements that cause performance problems. These SQL statements may be embedded in front-end program code and may be stored in backend database objects, such as stored procedures and views.

For those dynamically constructed SQL statements, that is, they are combined to create executable SQL statements only when the program is executed, we can use some tools to capture them, such as for sqlserver, you can use the included event probe.

(2) Optimize SQL statements

Step 2: prepare appropriate data in the database, execute SQL statements, and view performance. Data PreparationWorkYou can write an insert statement or use some existing tools (such as DataFactory.

........................

See full text click to download: http://www.51testing.com/html/82/n-141082.html

Here, SQL optimizer automatically analyzes the database execution plan of this SQL statement. The auto tune function of SQL optimizer intelligently analyzes a given SQL statement to find the semantic consistency of replaceable statements, that is, to find the same results that can be queried, however, different SQL statements are written.

For example, the following SQL statement can use the in Keyword:

However, to get the same query result, we can also use the exists keyword, except that the statement is written differently:

Reprint: http://www.51testing.com /? Action-viewnews-itemid-142060