SQL Universal optimization Scheme (where optimization, index optimization, paging optimization, transaction optimization, temporal table optimization)

SQL General optimization scheme:
1. Using parameterized queries: Prevent SQL injection and precompile SQL commands for increased efficiency
2. Remove unnecessary queries and search fields: In fact, in the actual application of the project, many of the query conditions are optional, can be avoided from the source of redundant functions as far as possible, this is the most simple and rough solution.
3. Select the most efficient table name order: The database parser processes the table name from the FROM clause in a right-to-left order, and the last table in the FROM clause is processed first, and in the case of multiple tables in the FROM clause, you must select the table with the fewest number of record bars to put in the last. If you have more than 3 tables connected to the query, you need to select the table that is referenced by the other table to be last.
4. Do not use SELECT *: Do not use SELECT * To improve query efficiency, reduce the amount of data output, increase transmission speed (the database or "*" in the parsing process into all column names, which means more time spent)
5. Try to avoid the return of large data to the client, if the amount of data is too large, should consider whether the corresponding demand is reasonable.
6. Reduce access to the database: through stored procedures and so on, put multiple statements in a stored procedure to execute, reduce the number of database access
7. Consolidate simple, unrelated database access: If you have a few simple database query statements, you can integrate them into a single query (even if they don't have a relationship))
8. Delete duplicate records: Delete duplicate records, reduce database file size
9. Use the alias of the table: when you concatenate multiple tables in an SQL statement, use the alias of the table and prefix the alias to each column. This reduces the time to parse and reduces the syntax errors caused by column ambiguity.
10. Use the alias of a column: When the name of the column is long, the alias of the short column can be used to make the query clearer and more concise.
11. Replace in with exists with not exists instead of not in: In either case, not in is the least effective (because it performs a full table traversal of the table in the subquery). To avoid using not, we can change it to an outer join (Outer Joins) or not EXISTS.
12. Statistics-related queries, which affect result sets are often huge, should avoid performing statistics-related queries during peak business hours, or only perform statistical queries from the library. It is also recommended to store the data in memory, cache (such as Redis), and then write to the database in a certain policy.
Select COUNT (*) from table, so that count without any conditions causes a full table scan, and without any business meaning, it must be eliminated and can be replaced by other methods.
14. Use truncate instead of Delete to empty the entire data table: When you delete a record in a table, in general, the rollback segment (rollback segments) is used to hold information that can be recovered. (You can revert to the state before the delete command was executed) and when Truncate is applied, the rollback segment no longer holds any recoverable information. When the command is run, the data cannot be restored. So very few resources are invoked and the execution time is very short.

Field type optimization:
1. Use numeric fields as much as possible, if the field containing only numeric information should not be designed as a character type, which will reduce the performance of queries and connections and increase storage overhead. This is because the engine compares each character in a string one at a time while processing queries and joins, and it is sufficient for a numeric type to be compared only once.
2. It is best not to leave the database null, and to populate the database with not NULL as much as possible. (Remarks, descriptions, comments, and so on can be set to NULL)

Where conditional statement optimization:
1. Connection order in the WHERE clause: the database parses the WHERE clause from right to left, and according to this principle, the connections between tables must be written before other where conditions (left), and those conditions that can filter out the maximum number of records must be written at the end of the WHERE clause (right).
2. Avoid field-type conversions in the Where statement (implicit type conversions occur when the type of the field and the passed parameter type are inconsistent)
3. Do not perform functions, arithmetic operations, or other expression operations on the left side of the "=" in the Where Condition statement, you can use redundant fields instead of function operations, or the system will not work correctly with the index
4. The null value of the field in the Where clause, including the not,! =, <> operators, or like keyword before plus% (like '% keyword '), can not use the index, thus triggering a full table scan.
5. Use like for fuzzy query should be aware that unless necessary, otherwise do not add% before the keyword, otherwise it will inevitably lead to full table query

Index optimization:
1. The index is not the more the better, although the index can improve the efficiency of the corresponding select, but also reduce the efficiency of insert and UPDATE, because the INSERT or update when the index may be rebuilt, so how to build the index needs careful consideration, depending on the situation. The number of indexes on a table should not be more than 6, if too many you should consider whether some of the indexes that are not commonly used are necessary.
2. Improve efficiency with indexes: proper use of indexes and composite indexes can also improve efficiency. But using indexes is a cost, the index needs space to store it, and it needs to be maintained regularly, and the index itself is modified whenever there are records in the table or when the index column is modified. This means that each record's insert, DELETE, and update will pay more than 4, 5 disk I/O. Because indexes require additional storage space and processing, those unnecessary indexes can slow query response time. It is necessary to periodically refactor the index.
3. When using an indexed field as a condition, if the index is a federated 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.
4. Be aware of index maintenance, periodically rebuild the index, and recompile the stored procedure.

Paging Statement optimization:
1. Optimization of paged query. If the number of pages is more, if the result set affected by limit 10000,10 is 10010 rows, the query will be slow. The recommended solution is to first query only the primary key select ID from the table where: ORDER BY: Limit 10000,10 (search criteria and sort index), then the primary key to get the data.
2. For multiple large data volume (here Hundreds of is even larger) table join, to first paged and then join, otherwise the logical reading will be very high, poor performance.

Optimization of

transactions:
1. Use commit whenever possible: use commit in the program as much as you can, so that the performance of the program is improved and the requirements are reduced by the resources freed by the commit, which is different for each database engine. SQL Server is a single transaction by default.
2. Try to avoid large transaction operation and improve the system concurrency ability.
3. Using the BEGIN Tran:begin Tran only when necessary ensures that the data is consistent, that either a few tables are modified or unsuccessful, but that the BEGIN Tran pays the price that all SQL statements cannot be freed until they are committed. Until the commit is dropped. The principle of the BEGIN Tran is that the less SQL statements that begin TRAN nested, the better it is to ensure data consistency! In some cases, triggers can be used to synchronize data, not necessarily with begin TRAN.

Temporary table optimizations:
1. Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources.
2. Staging intermediate results with temporary tables: temporary tables are temporarily present, and subsequent queries are in tempdb, which avoids multiple scans of the main table in the program, and greatly reduces the "shared lock" blocking "update lock" in program execution, reducing blocking and improving concurrency performance. It can also simplify the complexity of SQL statements.
3. When creating 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 table and insert to mitigate the resources of the system tables.
4. If a temporary table is used, be sure to explicitly delete all temporary tables at the end of the stored procedure, TRUNCATE table first, and then drop table, which avoids longer locking of the system tables.
5. If the temporal table has a large amount of data and needs to be indexed, then the process of creating the staging table and indexing should be placed in a single sub-stored procedure to ensure that the system is well-used to the index of the temporary table.
6. Avoid using distinct, order BY, group BY, have, join, Cumpute, as these statements will aggravate the burden on tempdb.
7. Carefully use large temporary tables and other large table connection query and modification, reduce the system table burden, because this operation will be in one statement multiple times the system table of tempdb.

Cursor Optimization:
1. Avoid using cursors as much as possible, because cursors are inefficient and should be considered for overwriting if the cursor is manipulating more than 10,000 rows of data.
2. Before using a cursor-based method or temporal table method, you should first look for a set-based solution to solve the problem, and the set-based approach is generally more efficient.
3. As with temporary tables, cursors are not unusable. Using Fast_forward cursors on small datasets is often preferable to other progressive processing methods, especially if you must reference several tables to obtain the required data.

Reference article:
Http://www.cnblogs.com/hellohell/p/6208466.html
Http://www.cnblogs.com/520sophia/archive/2011/08/23/2150990.html
Http://www.cnblogs.com/ATree/archive/2011/02/13/sql_optimize_1.html
Http://database.51cto.com/art/201407/445934.htm
Http://www.cnblogs.com/petitprince/archive/2010/11/23/1885994.html

Copyright NOTICE: This document is licensed under the attribution-Non-commercial use-sharing (CC BY-NC-SA 3.0 CN) International License Agreement, please specify the author and source. Title: SQL General optimization Scheme (where optimization, index optimization, paging optimization, transaction optimization, temporal table optimization) This article link: http://www.cnblogs.com/sochishun/p/7003513.html This article Sochishun (e-mail: 14507247#qq.com | blog: http://www.cnblogs.com/sochishun/) Published: June 13, 2017

SQL Universal optimization Scheme (where optimization, index optimization, paging optimization, transaction optimization, temporal table optimization)