Some performance discussions on SQL

1. SQL Server stores executed statements in the cache, so it is theoretically faster to use statements with parameters than statements that use constants (because each statement is the same, reducing the statement parsing and execution plan selection). However, if the parameters are given different values, the number of rows returned varies greatly, and selecting the same execution plan causes some queries to become unusually slow. The following are the execution plans given by F_parent_cateid to different constants, which vary, but if you use the parameter method, the execution plan will only use one of them, depending on which is executed first. Previously tested, if the A execution plan is the only option,f_parent_cateid=111 will need to execute more than 3 minutes (in fact select B execution plan as long as it takes a short time).

2, update , regardless of whether the updated field value changes, the statement will cause the data is actually modified (data on the hard disk migration), so to avoid the need to modify the field written in the Update statement, In particular, those indexed component fields (especially clustered indexes make up fields).

3, in a single query, not one table is indexed, and sometimes two index connections are used to get results. The execution plan can be viewed

4, for two relatively large tables, and the number of table rows is similar, the execution plan may choose nested loops to connect two tables (for unknown reasons), then the query speed will be unusually slow, such as two tables of 1 million levels, nested loop connection is equivalent to 1 trillion times (O (n^2) ) (at least 1 hours for the result). When SQL Server incorrectly chooses the table connection mode, the inner join in the statement should be forced to inner hash join, directly using the hash connection (O (n)), the result is only 10-30 seconds. In summary, if you encounter a slow query, you should take a first look at whether it is due to an execution plan selection error.

5,SQL Server (2008 and previous)Skip-Use indexes are not currently supported (Oraclesupport), for composite indexes, the index must be used in the order in which the fields appear in the index. such as: byAand theBcomposition of the combined index, ifAdid not appear inWHEREStatement (onlyB), even ifAHas only two or three values and cannot be used on the combined index. In this case, it is possible to consider human-assisted conditions (plusAconditions), and then through multiple queries (depending onApossible values) to return the result.

6, If two tables can be combined in a table, try to merge them, the more tables in a query join, the more the index will be found. Despite the wide table, the cost of index lookup is higher (becauseThe number of rows stored in a page (8KB) is less), but it is much less expensive than more index lookups.

7, for statements that use parameters or statements that use constants, you should ensure that their value types are consistent with the field types in the table, or else you might not use indexes or partitioning columns. For integer constants,SQL Server is considered to be a four-bit integer by default ( even if the SQL Server as well . ).

Some performance discussions on SQL