SQL statement Optimization Method

Ensure that the number of accesses to the database is minimized based on the implementation of the function. By searching for parameters, the number of access rows to the table is minimized, and the result set is minimized, thus reducing the network burden; operations that can be separated should be processed separately to improve the response speed each time. When using SQL in the data window, try to place the indexes in the selected first column; the algorithm structure should be as follows:

Ensure that the number of accesses to the database is minimized based on the implementation of the function. By searching for parameters, the number of access rows to the table is minimized, and the result set is minimized, thus reducing the network burden; operations that can be separated should be processed separately to improve the response speed each time. When using SQL in the data window, try to place the indexes in the selected first column; the algorithm structure should be as follows:

Ensure that the number of accesses to the database is minimized based on the implementation of the function. By searching for parameters, Hong Kong servers are rented to minimize the number of access rows to the table and result sets, thus reducing the network burden; operations that can be separated should be processed separately to improve the response speed each time. When using SQL in the data window, try to place the indexes in the selected first column; the algorithm structure should be as simple as possible; during query, do not use wildcards such as SELECT * FROM T1. SELECT COL1 and COL2 FROM T1 if you want to use several columns; if possible, try to limit the number of rows in the result set as much as possible, for example, select top 300 COL1, COL2, COL3 FROM T1, because in some cases users do not need that much data.
If no index is created, the database must perform a full table scan to search for a certain data record, traverse all the data, and find the matching record. When the data volume is small, there may be no obvious difference. Hong Kong servers, but when the data volume is large, this situation is extremely bad.
SQL statements are executed in SQL server. They are worried that the SQL statements they write will be misunderstood by SQL SERVER. For example:
Select * from table1 where name = 'hangsan' and tID> 10000
And execution:
Select * from table1 where tID> 10000 and name = 'hangsan'
Some people do not know whether the execution efficiency of the preceding two statements is the same, because the two statements are indeed different from the statement sequence. If tID is an aggregate index, then, the next sentence can only be searched from the 10000 records in the table, while the previous sentence should first look up from the full table to see how many names = 'hangsan, then, the query results are presented based on the condition tID> 10000.
In fact, such a worry is unnecessary. SQL SERVER has a "query analysis optimizer", which can calculate the search conditions in the where clause and determine which index can narrow the search space of the table scan, that is, it can achieve automatic optimization. Although the query optimizer can automatically optimize queries based on the where clause, sometimes the query optimizer does not perform quick queries according to your intention.
In the query and analysis phase, the query optimizer checks each stage of the query and determines whether the data volume to be scanned is useful. If a phase can be used as a scan parameter (SARG), it is called an Optimized phase and the required data can be quickly obtained using the index.
The definition of SARG: it is used to restrict a search operation, because it usually refers to a specific match, a match within the range of a value or the AND connection of two or more conditions. The format is as follows:
Column name Operator <常数 或 变量> Or <常数 或 变量> Operator column name
The column name can appear on one side of the operator, while the constant or variable appears on the other side of the operator. For example:
Name = 'zhang san'
Price> 5000
5000 <价格
Name = 'zhang san' and price> 5000
If an expression cannot meet the form of SARG, it cannot limit the search range, that is, SQL SERVER must determine whether each row meets all the conditions in the WHERE clause. Therefore, an index is useless for expressions that do not meet the SARG format.
Therefore, the most important thing to optimize the query is to make the statements comply with the rules of the query optimizer as much as possible to avoid full table scans and use index queries.

Note:

1. Try to avoid null value determination on the field in the where clause. Otherwise, the engine will discard the index and perform full table scanning, for example:
Select id from t where num is null
You can set the default value 0 on num to make sure that the num column in the table does not have a null value, and then query it like this:
Select id from t where num = 0

2. Try to avoid using it in the where clause! = Or <> operator. Otherwise, the engine will discard the index for full table scanning. The optimizer cannot use indexes to determine the number of rows to be hit. Therefore, you need to search all rows in the table.

3. Try to avoid using or in the where clause to connect to the condition. Otherwise, the engine will discard the index and perform full table scanning, for example:
Select id from t where num = 10 or num = 20
You can query it as follows:
Select id from t where num = 10
Union all
Select id from t where num = 20

4. in and not in should also be used with caution, because IN will make the system unable to use the index, but can only directly search the data in the table. For example:
Select id from t where num in (1, 2, 3)
For continuous values, you can use between instead of in:
Select id from t where num between 1 and 3

5. Try to avoid using non-start letters to search for indexed character data. This also makes the engine unable to use the index.
See the following example:
SELECT * FROM T1 where name like '% L %'
SELECT * FROM T1 where substing (NAME, 2, 1) = 'l'
SELECT * FROM T1 where name like 'l %'
Even if the NAME field has an index, the first two queries still cannot use the index to accelerate the operation. The engine has to perform operations on all data in the table one by one to complete the task. The third query can use indexes to speed up operations.

6. If necessary, force the query optimizer to use an index. If a parameter is used in the where clause, full table scan may occur. Because SQL parses local variables only at runtime, the optimizer cannot postpone the selection of the access plan to runtime; it must be selected at compilation. However, if an access plan is created during compilation, the value of the variable is still unknown and thus cannot be used as an input for index selection. The following statement performs a full table scan:
Select id from t where num = @ num
You can change it to force query to use the index:
Select id from t with (index name) where num = @ num

7. Avoid performing expression operations on fields in the where clause as much as possible, which will cause the engine to discard the use of indexes for full table scanning. For example:
SELECT * FROM T1 WHERE F1/2 = 100
Should be changed:
SELECT * FROM T1 WHERE F1 = 100*2

SELECT * from record where substring (CARD_NO, 5378) = '20140901'
Should be changed:
SELECT * from record where CARD_NO LIKE '201312'

SELECT member_number, first_name, last_name FROM members
Where datediff (yy, datofbirth, GETDATE ()> 21
Should be changed:
SELECT member_number, first_name, last_name FROM members
WHERE dateofbirth <DATEADD (yy,-21, GETDATE ())
That is, any operation on the column will cause the table to scan, including database functions, calculation expressions, etc. During the query, try to move the operation to the right of the equal sign.

8. Avoid performing function operations on fields in the where clause as much as possible. This will cause the engine to stop using the index for full table scanning. For example:
Select id from t where substring (name, 1, 3) = 'abc' -- id whose name starts with abc
Select id from t where datediff (day, createdate, '2017-11-30 ') = 0 -- '2017-11-30' generated id
Should be changed:
Select id from t where name like 'abc %'
Select id from t where createdate> = '2014-11-30 'and createdate <'2014-12-1'

9. Do not perform functions, arithmetic operations, or other expression operations on the left side of "=" in the where clause. Otherwise, the system may not be able to correctly use the index.

10. when using an index field as a condition, if the index is a composite index, you must use the first field in the index as the condition to ensure 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.

11. Using exists is often a good choice:
Elect num from a where num in (select num from B)
Replace the following statement:
Select num from a where exists (select 1 from B where num = a. num)