1. You should try to avoid null values in the WHERE clause, otherwise it will cause the engine to discard the index
Full table scan, such as:
Select ID from t where num is null
You can set the default value of 0 on NUM, make sure that the NUM column 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, or discard the engine for full table sweep using the index
Stroke 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. You should try to avoid using or in the WHERE clause to join the condition, otherwise it will cause the engine to abandon the use of the index
Full table scan, such as:
Select ID from t where num=10 or num=20
You can query this:
Select ID from t where num=10
UNION ALL
Select ID from t where num=20
4.in and not in are also used sparingly, because in makes the system unusable with indexes and 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 instead of in:
Select ID from t where num between 1 and 3
5. Try to avoid searching in indexed character data using non-heading letters. This also makes the engine unusable with indexes.
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 is indexed, the first two queries are still unable to take advantage of the index completion to speed up the operation, the engine has to
Table all data-by-operation to complete the task. The third query can use an index to speed up operations.
6. Forcing the query optimizer to use an index if necessary, such as 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 defer the selection of access plans to run
, it must be selected at compile time. However, if an access plan is established at compile time, the value of the variable is still unknown,
Therefore, the input cannot be selected as an index. The following statement will perform a full table scan:
Select ID from t where [email protected]
You can force the query to use the index instead:
Select ID from T with (index name) where [email protected]
7. You should try to avoid expression operations on the field in the Where clause, which will cause the engine to discard the use of the index
Full table scan. Such as:
SELECT * from T1 WHERE f1/2=100
should read:
SELECT * from T1 WHERE f1=100*2
SELECT * from RECORD WHERE SUBSTRING (card_no,1,4) = ' 5378 '
should read:
SELECT * from RECORD WHERE card_no like ' 5,378% '
SELECT Member_number, first_name, last_name from members
WHERE DATEDIFF (Yy,datofbirth,getdate ()) > 21
should read:
SELECT Member_number, first_name, last_name from members
WHERE dateOfBirth < DATEADD (Yy,-21,getdate ())
That is, any action on a column will result in a table scan, which includes database functions, calculation expressions, and so on, to be
To move the operation to the right of the equals sign.
8. You should try to avoid function operations on the field in the Where clause, which will cause the engine to discard the use of the index for the full table
Scanning. Such as:
Select ID from t where substring (name,1,3) = ' abc '--name ID starting with ABC
Select ID from t where DATEDIFF (day,createdate, ' 2005-11-30 ') =0--' 2005-11-30 ' generated ID
should read:
Select ID from t where name like ' abc% '
Select ID from t where createdate>= ' 2005-11-30 ' and createdate< ' 2005-12-1 '
9. Do not perform functions, arithmetic operations, or other expression operations on the left side of the "=" in the WHERE clause, or the system will be
Indexes can not be used correctly.
10. When using an indexed field as a condition, if the index is a composite index, you must use the first in the index
field as a condition to ensure that the system uses the index, the index will not be used and should be made as
The field order coincides with the index order.
11. Many times it is a good choice to use exists:
Select num from a where num in (select num from B)
Replace with the following statement:
Select num from a where exists (select 1 from b where num=a.num)
SELECT SUM (T1. C1) from T1 WHERE (
(SELECT COUNT (*) from T2 WHERE t2.c2=t1.c2>0)
SELECT SUM (T1. C1) from T1where EXISTS (
SELECT * from T2 WHERE T2. C2=t1. C2)
Both produce the same result, but the latter is obviously more efficient than the former. Because the latter does not produce a large number of locked table sweeps
or index Scan.
If you want to verify that there is a record in the table, do not use COUNT (*) as inefficient and waste server resources.
Can be replaced with exists. Such as:
IF (SELECT COUNT (*) from table_name WHERE column_name = ' xxx ')
Can be written as:
IF EXISTS (SELECT * FROM table_name WHERE column_name = ' xxx ')
It is often necessary to write a t_sql statement to compare a parent result set and a child result set to find out if there are any in the parent result sets
There are records that are not in the child result set, such as:
SELECT A.hdr_key from Hdr_tbl a----tbl a means that TBL uses alias a instead
Where not EXISTS (SELECT * from dtl_tbl b WHERE a.hdr_key = B.hdr_key)
SELECT A.hdr_key from Hdr_tbl a
Left JOIN dtl_tbl B-a.hdr_key = B.hdr_key WHERE B.hdr_key is NULL
SELECT Hdr_key from Hdr_tbl
WHERE Hdr_key not in (SELECT Hdr_key from DTL_TBL)
Three kinds of writing can get the same correct results, but the efficiency is reduced in turn.
12. Try to use table variables instead of temporary tables. If the table variable contains a large amount of data, be aware that the index is very limited (only
Primary key index).
13. Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources.
14. Temporary tables are not unusable, and they can be used appropriately to make certain routines more efficient, for example, when a repeated citation is required
When using a large table or a dataset in a common table. However, for one-time events, it is best to use an export table.
15. 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, avoid causing a lot of log to increase speed, if the amount of data is not large, in order to mitigate the system table resources, you should first
Create TABLE, and then insert.
16. If a temporary table is used, be sure to explicitly delete all temporary tables at the end of the stored procedure, first truncate
Table, and then drop table, which avoids long-time locking of the system tables.
17. Set NOCOUNT on at the beginning of all stored procedures and triggers, set at the end
NOCOUNT OFF. You do not have to send Done_in_proc to the client after each statement that executes the stored procedure and trigger
News.
18. Try to avoid large transaction operation and improve the system concurrency ability.
19. 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.
20. Avoid using incompatible data types. For example, float and int, char and varchar, binary, and varbinary are not
Compatible with. Incompatible data types may make the optimizer unable to perform some optimizations that could otherwise have been performed. For example:
SELECT name from employee WHERE salary > 60000
In this statement, such as the salary field is a money type, it is difficult for the optimizer to optimize it because 60000 is an integral type
Number. We should convert an integer into a coin type when programming, rather than wait for a run-time conversion.
21. Make full use of the connection condition, in which case there may be more than one connection condition between the two tables, where
clause, it is possible to greatly improve the query speed by writing the connection conditions in full.
Cases:
SELECT SUM (A.amount) from account a,card B WHERE a.card_no = b.card_no
SELECT SUM (A.amount) from account a,card B WHERE a.card_no = B.card_no and
A.account_no=b.account_no
The second sentence will be much faster than the first sentence.
22. Use the view to speed up the query
Sorting a subset of tables and creating views can sometimes speed up queries. It helps to avoid multiple sorting operations, and
It also simplifies the work of the optimizer in other ways. For example:
SELECT cust.name,rcvbles.balance,?? Other columns
From Cust,rcvbles
WHERE cust.customer_id = rcvlbes.customer_id
and rcvblls.balance>0
and cust.postcode> "98000"
ORDER by Cust.name
If the query is to be executed more than once, all unpaid customers can be found in a single view,
And sort by the customer's name:
CREATE VIEW DBO. V_cust_rcvlbes
As
SELECT cust.name,rcvbles.balance,?? Other columns
From Cust,rcvbles
WHERE cust.customer_id = rcvlbes.customer_id
and rcvblls.balance>0
ORDER by Cust.name
Then query in the view in the following way:
SELECT * from V_cust_rcvlbes
WHERE postcode> "98000"
The rows in the view are less than the rows in the primary table, and the physical order is the required order, reducing the disk I/O, so check
The workload can be significantly reduced.
23, you can use distinct without GROUP by
SELECT OrderID from Details WHERE UnitPrice > Ten GROUP by OrderID
Can be changed to:
SELECT DISTINCT OrderID from Details WHERE UnitPrice > 10
24. Use UNION ALL to not use Union
UNION all does not execute the Select DISTINCT function, which reduces a lot of unnecessary resources
25. Try not to use the SELECT INTO statement.
The SELECT inot statement causes the table to lock and prevent other users from accessing the table.
What we mentioned above are some basic considerations for improving query speed, but in more cases it is often necessary to repeatedly
Experiment to compare different statements to get the best solution. The best way of course is to test the SQL language that implements the same functionality
The minimum execution time, but in the database if the amount of data is relatively small, it can be used to view the
Line plan, namely: the implementation of the same function of multiple SQL statements to the Query Analyzer, according to Ctrl+l see the use of the cable
, the number of table scans (both of which have the greatest impact on performance), and the overall cost percentage to be consulted.
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
SQL Server mass data query code optimization and recommendations
