Basic principles
- Avoid full table scan
- Build an index
- Try to avoid returning large amounts of data to the client, and if the amount of data is too large, you should consider whether the demand is reasonable
- Try to avoid large transaction operations and improve system concurrency
- Before you can use a cursor-based method or a temporary table method, you should look for a set-based solution to solve the problem, and the set-based approach is generally more efficient. Avoid using cursors as much as possible, because cursors are inefficient.
The condition after where
- Try to avoid using the! = or <> operator in the WHERE clause, or discard the engine for a full table scan using the index.
- You should try to avoid using or in the WHERE clause to join the condition, consider using union instead of
- In and not in also to use caution, for continuous values, you can use between do not use in,exists instead of in
- Try to avoid expression and function manipulation of fields in the WHERE clause
Data type
- Use numeric fields as much as possible, and if fields with numeric information are not designed as character types, this can degrade query and connection performance and increase storage overhead.
- Use Varchar/nvarchar instead of Char/nchar as much as possible, because variable-length fields have small storage space, and for queries, the search efficiency in a relatively small field is clearly higher.
- It is best not to leave the database null, and to populate the database with not NULL as much as possible. Comments, descriptions, comments and the like can be set to NULL, others, preferably do not use NULL.
- Do not use anywhere
select * from t , replace "*" with a specific field list, and do not return any fields that are not available.
Temp table
- Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources. For one-time events, it is best to use an export table.
- When you create a temporary table, if you insert a large amount of data at one time, you can use SELECT INTO instead of CREATE table to avoid causing a large number of logs to increase speed, and if the amount of data is small, create a table and insert it in order to mitigate the resources of the system tables.
- If a temporary table is used, TRUNCATE TABLE first and then drop table when all temporary tables are finally explicitly deleted, which avoids longer locking of the system tables.
Index
- You should first consider establishing an index on the columns involved in where and order by.
- When using an indexed field as a condition, if the index is a composite index, you must use the first field in the index as a condition to guarantee that the system uses the index, otherwise the index will not be used, and the field order should be consistent with the index order as much as possible.
- Indexes are not as good as they are, and while indexes can improve the efficiency of the corresponding select, they also reduce the efficiency of insert and update, as it is possible to rebuild the index at INSERT or update, depending on the situation. The number of indexes on a table should not be more than 7, if too many you should consider whether some of the indexes that are not commonly used are necessary.
SQL statement tuning where Condition data type temp Table index
