1. Operation 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 ...... 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 the view that only contains the table. Otherwise, unnecessary overhead is added, the query is disturbed. To speed up View query, MsSQL adds the View index function. 4. 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. 5. 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. 6. 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. Male, female and female are the same. Because in may be more than once, it may be slower sometimes. 7. 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. 8. The conditional order following the WHERE clause directly affects the query of the big data table. For example, dy_dj (voltage level) in the following two SQL statements) the xh_bz and xh_bz fields are not indexed. Therefore, full table scan is performed. If dy_dj = '1k' or lower, the rate in the record set is 99%, the ratio of xh_bz = 1 is only 0.5%. When the first SQL statement is executed, all the 99% records are compared by dy_dj and xh_bz, the CPU usage of the second SQL statement is significantly lower than that of the first SQL statement by comparing dy_dj and xh_bz on the first 0.5% records. So try to put the conditions with a small scope before ...... 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. 9. 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. 10. When using in, put the most frequent value IN the list of post-in denominations at the beginning, and put the least value at the end, this reduces the number of queries. When select into is used, it locks the system table (sysobjects, sysindexes, and so on) and blocks access to 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. 11. 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 the query speed is fast. Grouping and Having large data rows consumes a lot of resources. If the purpose of Group BY is not to include computation, but only to Group, it is faster to use Distinct, multiple records are updated at a time, and each record is updated at a time. That is to say, batch processing is good, and temporary tables are used with caution, temporary tables are stored in the tempdb database. Cross-database operations may occur when you operate on temporary tables. Replace the result set and table variables whenever possible. 12. 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. 13. 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. 14. 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.
I personally think that when writing SQL statements, the efficiency of using operators is higher than that of expressions. When you query 40 thousand pieces of data within a certain period of time, the efficiency of using the operators ">=" AND" <= "is about 16% higher than that of using between and.
