SQL database operation optimization

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 scan, 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 scan, 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 only parses local variables at run time, but optimizesProgramThe access plan cannot be postponed to runtime; it must be selected during 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:
Select 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)
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)
The two produce the same results, but the latter is obviously more efficient than the former. Because the latter will not generate a large number of locked table scans or index scans.
If you want to check whether a record exists in the table, do not use count (*) as inefficient and waste server resources. It can be replaced by exists. For example:
If (select count (*) from table_name where column_name = 'xxx ')
Can be written:
If exists (select * From table_name where column_name = 'xxx ')
You often need to write a t_ SQL statement to compare a parent result set and a child result set, so as to find the records that exist in the parent result set but not in the Child result set, such:
Select a. hdr_key from hdr_tbl a ---- tbl a indicates that TBL is replaced by alias.
Where not exists (select * From dtl_tbl B where a. hdr_key = B. hdr_key)
Select a. hdr_key from hdr_tbl
Left join dtl_tbl B on 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)
The three writing methods 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, note that the index is very limited (only the 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 unavailable. Using them appropriately can make some routines more effective. For example, when you need to reference large tables or a data set in common tables repeatedly. However, it is best to use the export table for one-time events.
15. when creating a temporary table, if a large amount of data is inserted at one time, you can use select into instead of create table to avoid creating a large number of logs to increase the speed. If the data volume is small, to ease system table resources, create table first and then insert.
16. if a temporary table is used, you must explicitly delete all temporary tables at the end of the stored procedure. First truncate the table and then drop the table, so that the system table can be locked for a long time.
17. Set nocount on at the beginning of all stored procedures and triggers, and set nocount off at the end. You do not need to send the done_in_proc message to the client after executing each statement of the stored procedure and trigger.
18. Avoid large transaction operations as much as possible to improve the system concurrency capability.
19. Avoid returning a large amount of data to the client as much as possible. If the data volume is too large, consider whether the corresponding requirements are reasonable.
20. Avoid using incompatible data types. For example, float and INT, char and varchar, binary, and varbinary are incompatible. Data Type incompatibility may make the optimizer unable to perform some optimization operations that can be performed originally. For example:
Select name from employee where salary> 60000
In this statement, if the salary field is of the money type, it is difficult for the optimizer to optimize it because 60000 is an integer. We should convert the integer type into a coin type during programming, instead of waiting for the conversion at runtime.
21. Make full use of the connection conditions. In some cases, there may be more than one connection condition between two tables. In this case, write the complete connection conditions in the WHERE clause, which may greatly improve the query speed.
Example:
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 statement is much faster than the first statement.
22. Use view to accelerate query
Sort a subset of a table and create a view, which sometimes accelerates query. It helps avoid multiple sorting operations and simplifies the optimizer's work in other aspects. 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 this query is executed multiple times but more than once, you can find all the unpaid customers in a view and sort them by customer 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 number of rows in the view is smaller than that in the primary table, and the physical order is the required order, which reduces disk I/O, so the query workload can be greatly reduced.
23. If you can use distinct, you do not need group.
Select orderid from details where unitprice> 10 group by orderid
You can change it:
Select distinct orderid from details where unitprice> 10
24. Do not use Union if Union all is used.
Union all does not execute the select distinct function, which reduces unnecessary resources.

25. Try not to use the select into statement.
the select inot statement will lock the table and prevent other users from accessing the table.
the above mentioned notes are some basic notes for improving the query speed. However, in more cases, we often need to experiment with different statements to obtain the best solution. The best way is to test the SQL statement that implements the same function, which has the least execution time. However, if the data volume in the database is small, it cannot be compared. In this case, you can view the execution plan, that is, you can obtain multiple SQL statements that implement the same function to the query analyzer, press Ctrl + L to view the indexes used for query, and the number of table scans (the two have the greatest impact on performance ), in general, check the cost percentage.