In a project,SQL tuning is crucial to project performance. It is essential to master common SQL tuning methods. The following describes several common SQL tuning methods for reference.

Create an index

Select * from user where (username); select * from user where (username); select * from user where (username); If you are frequently searching by employee department and employee job level, you should create indexes on the employee department and employee job level fields. (2) The performance improvement brought by index creation is often huge, so when it is found that the retrieval speed is too slow, the first thing to think about is to create an index. (3) The number of indexes in a table should not exceed 6, if too many should consider whether it is necessary to build indexes on some infrequently used columns. More indexes are not always better. While indexes can improve the efficiency of select operations, they can also reduce the efficiency of insert and update operations.

Avoid using calculations on indexes

In the WHERE clause, the DBMS optimizer will not use the index if the index column is part of a calculation or function. The function is a type of calculation. Both exists and in are generally used because in does not move the index.

Select * from user where salary*22>11000Copy the code

High efficiency:

Select * from user where salary>11000/22Copy the code

Use precompiled queries

In the program is usually according to the user’s input to dynamically execute SQL, then should try to use parameterized SQL, so that not only can avoid SQL injection vulnerability attack, the most important database will precompile these parameterized SQL, so that the first execution of the DBMS for this SQL statement query optimization and precompile. This allows you to use the precompiled results directly when executing the SQL later, which can greatly speed up execution.

4. Adjust the join order in the Where sentence

A DBMS typically parses WHERE terms in a bottom-up order. According to this principle, table joins are best written before other WHERE conditions that filter out the maximum number of records.

Five. Try to compress multiple SQL statements into one SQL

Each time you execute SQL, you need to establish network connections, verify permissions, optimize SQL queries, and send execution results. This process is very time-consuming. Therefore, you should try to avoid excessive SQL statements, and do not use multiple SQL statements that can be compressed to one SQL execution.

Replace HAVING with where

Avoid HAVING, which filters the result set only after all records have been retrieved, and where, which scrounges records before aggregation, reduces overhead if you limit the number of records by using the WHERE clause. Conditions in HAVING are generally used for filtering aggregate functions, but other than that, you should write conditions in where clauses.

Use aliases for tables

When joining multiple tables in an SQL statement, use the alias of the table and prefix the alias to each column name. This reduces parsing time and reduces syntax errors caused by ambiguous friend column names.

B. Union all c. union all d. union all

When the SQL statement requires two query result sets of union, even though there will be no duplicate records in the retrieval results, the two result sets will also try to merge if union is used, and then sort before output final results. Therefore, if it can be judged that there will be no duplicate records in the retrieval results, union all should be used. So efficiency will be improved.

Consider using “temporary tables” to temporarily store intermediate results

The important way to simplify the SQL statement is to use temporary table temporary intermediate results, however, the benefits of a temporary table is far more than that, the provisional results spooled in the temporary table, the back of the query in tempdb for, this can avoid program scans the main table for many times, also greatly reduce the program execution of “Shared lock” blocking “update lock”, reduced the blocking, Improved concurrency performance. However, you also need to avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources.

Use transaction Begin Translation only when necessary

An SQL statement in SQL Server is a transaction by default, and is committed by default after the statement is executed. This is a minimal form of BEGIN TRAN, such that each statement begins with a BEGIN TRAN and ends with a COMMIT. In some cases, you need to explicitly declare BEGIN TRAN. For example, to perform insert, Delete, and modify operations, you need to modify several tables at the same time. Begin TRAN can do this by grouping several SQL statements together and then committing them together. The benefit is that the data is consistent, but nothing is perfect. The cost of Begin TRAN is that all resources locked by SQL statements cannot be released until commit. As you can see, if Begin Tran traps too many SQL statements, the performance of the database will be poor. Other statements must be blocked before the large transaction commits, resulting in many blocks. The principle used by Begin TRAN is to limit the number of SQL statements trapped by Begin TRAN while ensuring data consistency. In some cases, triggers can be used to synchronize data, not begin TRAN.

11. Avoid using cursors whenever possible

Do not return a large amount of data to the client. If the amount of data is too large, consider whether the corresponding requirements are reasonable. Because cursors are inefficient, you should consider rewriting them if they operate on more than 10,000 rows of data.

Use varchar/nvarchar instead of char/nchar

Use vARCHar /nvarchar instead of char/nchar whenever possible, because first of all, the storage space of a longer field is small, and second of all, it is obviously more efficient to search within a relatively small field for queries. If a vARCHar is a variable length field, NULL takes up no space. If a vARCHar is a variable length field, NULL takes up no space. If a vARCHar is a variable length field, NULL takes up no space.

Query SELECT statement optimization

1. Do not use select * from t anywhere, replace “*” with a list of specific fields, and do not return any fields 2 that are not needed. Avoid null values for fields in the WHERE clause, which will cause the engine to abandon the index and perform a full table scan, as in:

	 select id from t where num is null           
Copy the code

Select * from num where num is null; select * from num where num is null;

      select id from t where num=0
      select id from t where num=10 or num=20
Copy the code

It can be queried like this:

      select id from t where num=10
       union all
      select id from t where num=20
Copy the code

4. You can’t get 100 points

Select id from t where name like '% ABC %'Copy the code

For efficiency, consider full-text retrieval.

Select id from t where num in(1,2,3)Copy the code

For continuous numbers, use between instead of in:

    select id from t where num between 1 and 3 
Copy the code

6. If two tables are of the same size, there is little difference between in and exists. In: for example: table A (small table), table B (large table)

Select * from A where cc in (select cc from B); Select * from A where exists(select cc from B where cc=A.c)Copy the code

On the contrary,

Select * from B where cc in (select cc from A); Select * from B where exists(select cc from A where cc=B.c)Copy the code

Update statement optimization ##

1. If only one or two fields are changed, do not Update all fields. Otherwise, frequent calls will cause significant performance consumption and a large number of logs

Delete Delete statement ##

1. Examples of the most efficient way to remove duplicate records (because of ROWID) :

DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
Copy the code

Insert Insert statement optimization

1. When creating a temporary table, if a large amount of data is inserted at a time, you can use Select into instead of create table to avoid creating a large number of logs and improve the speed. If the amount of data is small, to reduce the resources of the system table, create table first, then insert.