Five methods to improve the execution efficiency of SQL statement books

Some of the information collected earlier is for reference only. It is not necessarily correct.

1. Operator Symbol: Not in Operator
This operation is not recommended for strong columns because it cannot apply table indexes. Recommended Solution: Replace "is null", "<> ","! = ","!> ","! <"," Not "," not exists "," not in "," not like "," like '% 100' ", because they do not leave the index and are all table scans. Not in scans the table multiple times and replaces it with exists, not exists, in, and left Outer Join, especially the left join. exists is faster than in, and the slowest operation is not. if the column value is null, its index does not work in the past. Now the 2000 optimizer can process it. The same is null, "not", "not exists", "not in" can optimize her, but "<>" cannot be optimized, and no index is used.

2. Pay attention to the difference between Union and Union all. Union performs a step of distinct operation than Union all. If Union all can be used, try not to use Union.

3. Do not return unwanted rows or columns when querying. In addition, for multi-Table connection queries, try to change to connection queries, and use less subqueries ..

4. Use a view as little as possible, which is less efficient. Operations on a view are slower than operations on a table. You can replace it with a stored procedure. In particular, do not use nested views. nested views increase the difficulty of searching for original data.
Let's look at the essence of the View: it is an optimized SQL statement stored on the server that has produced a query plan. When retrieving data from a single table, do not use a view pointing to multiple tables,
Read data directly from a table or view that only contains the table. Otherwise, unnecessary overhead is added and query is disturbed. To speed up View query, MSSQL adds the View index function.

5. Create a proper index. Exercise caution when using indexes for tables with frequent insertion or modification. If an index exists in the table, the full table scan is triggered during insertion and modification.
Indexes are generally used on fields that are often used as conditions after the WHERE clause.

6. When defining fields, stored procedures, and functions in a table, set the parameter size to an appropriate value. Do not set it to too large. This is costly.

7. Between is faster in some cases than in, and between can locate the range based on the index faster. Use the query optimizer to see the difference.
Select * From chineseresume where title in ('male', 'female ')
Select * From chineseresume where between 'male' and 'female 'are the same. Because in may be more than once, it may be slower sometimes.

8. If it is necessary to create an index for a global or local temporary table, it may increase the speed, but not necessarily because the index also consumes a lot of resources. Its creation is the same as that of the actual table.

9. Effect of conditional order after where
The conditional order after the WHERE clause directly affects the query of the big data table, as shown in figure
Select * From zl_yhjbqk where dy_dj = '1k' and xh_bz = 1
Select * From zl_yhjbqk where xh_bz = 1 and dy_dj = '1kv below'
In the preceding two SQL statements, the dy_dj and xh_bz fields are not indexed. Therefore, full table scan is performed, if the ratio of dy_dj = '1k' to below the record set is 99%, and the ratio of xh_bz = 1 is only 0.5%, when the first SQL statement is executed, 99% records are compared with dy_dj and xh_bz. When the second SQL statement is executed, 0.5% records are compared with dy_dj and xh_bz, the CPU usage of the second SQL statement is obviously lower than that of the first SQL statement. So try to put the conditions with a small range in front ..

10. The or clause can be divided into multiple queries and connected to multiple queries through Union. Their speed is only related to whether an index is used. If a query requires a Union Index, Union all is more efficient. no index is used for multiple or statements, and it is rewritten to the form of union to try to match the index. Whether or not indexes are used in a key issue.

11. Do not use distinct or order by unless necessary. These actions can be executed on the client. They increase additional overhead. This is the same as Union and Union all.

12. When using in, put the most frequent value in the list of post-in denominations at the beginning and the least value at the end to reduce the number of judgments.

13. When select into is used, it locks the system table (sysobjects, sysindexes, etc.) and blocks access from other connections. When creating a temporary table, use the show statement. In another connection, select * From sysobjects can see that select into locks the system table, create Table also locks the system table (whether it is a temporary table or a system table ). So never use it in things !!! In this case, use real tables or temporary table variables for temporary tables that are frequently used.

14. Generally, redundant rows can be removed before group by and having clauses, so try not to use them for row removal. Their execution sequence should be optimal as follows: Select WHERE clause Selects all appropriate rows, group by is used to group statistical rows, and having clause is used to remove redundant groups. In this way, the consumption of group by and having is small, and queries are fast. Grouping and having large data rows consumes a lot of resources. If the purpose of group by is not to include computing, but to group, it is faster to use distinct.

15. Updating multiple records at a time is faster than updating multiple records at a time, that is, batch processing is good.

16. Use temporary tables with caution. Temporary tables are stored in the tempdb database. Operations on temporary tables may result in cross-database operations. Replace the result set and table variables whenever possible.

17. Try to put data processing on the server to reduce network overhead, such as using stored procedures. The stored procedure is compiled, optimized, organized into an execution plan, and stored in the database SQL statements. It is a collection of control flow languages, and the speed is certainly fast.

18. Do not use the same function or query statement multiple times in a SQL statement or stored procedure. This is a waste of resources. We recommend that you put the result in a variable before calling it. This is faster.

19. Access your table in a certain order. If you lock table A and table B first, lock them in this order in all stored procedures. If you first lock table B in a stored procedure and then lock Table A, this may lead to a deadlock. If the lock sequence is not designed in detail in advance, it is difficult to find deadlocks.