SQL Server mass data query code optimization recommendations

1. Try to avoid null values for the field in the WHERE clause

A null value judgement on a field in the Where clause causes the engine to abandon using the index for a full table scan. Such as:

SELECT ID from T WHERE num is NULL

You can set the default value of 0 in the Num field to make sure that the NUM field in the table does not have a null value, and then query:

SELECT ID from T WHERE num=0

2. Try to avoid using the! = or <> operator in the WHERE clause

Using the! = or <> operator in the WHERE clause will cause the engine to discard the full table scan using the index. The optimizer will not be able to determine the number of rows to be fatal by the index, so it needs to search all rows of that table.

3. Try to avoid using or in the WHERE clause to join the condition

Using or join conditions in the WHERE clause will cause the engine to discard the use of the index for a full table scan, such as:

SELECT ID from t WHERE num=10 OR num=20

Can be changed to:

SELECT ID from t where num=10union allselect ID from t where num=20

4. Be cautious with in and not

Because in causes the system to not use the index, it can only search the data in the table directly. Such as:

SELECT ID from T WHERE num in

For consecutive values, you can use between

SELECT ID from T WHERE num between 1 and 3

5. Try to avoid searching in indexed character data by using '% '.

In this scenario, the engine cannot take advantage of the index, such as:

SELECT * from T WHERE name like '%jhon% '

It is recommended to use full-text search.

6. Force the query optimizer to use an index if necessary

Using parameters in the WHERE clause can also cause a full table scan. Because SQL resolves local variables only at run time, the optimizer cannot say that the Access plan's selection is deferred until run time, and it must be selected at compile time. However, if the CV accesses the plan at compile time, the value of the variable is still unknown and therefore cannot be selected as an input for the index. The following statement will perform a full table scan:

SELECT ID from t WHERE num = @num

You can force the query to use the index instead:

[Email protected]

7. Try to avoid expression operations on fields in the WHERE clause

This causes the engine to discard the full table scan using the index. Such as:

SELECT * from T WHERE num/2=100

should be:

SELECT * from T WHERE num=100*2

select* from T WHERE SUBSTRING (num,1,4) = ' 5678 '

should be:

SELECT * from T WHERE num like ' 5,678% '

SELECT * from T WHERE DATEDIFF (Yy,num,getdate ()) >21

should be:

SELECT * from T WHERE Num<dateadd (Yy,-21,getdate ())

That is, any action on a column will result in a table scan, including database functions, calculation expressions, etc., to move the operation to the right as possible when querying.

SQL Server mass data query code optimization recommendations