SQL Server mass data query code optimization recommendations

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.