SQL Server Mass Data Query code optimization and recommendations, SQL data query

SQL Server Mass Data Query code optimization and recommendations, SQL data query
1. Try to avoid null value judgment on the field in the where clause. Otherwise, the engine will discard the index and
Full table row 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 and scan the entire table.
Description. 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 conditions. Otherwise, the engine will discard the index.
Full table scan, such:
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 be accelerated using the index, and the engine has
All data in the table is operated 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, the optimizer cannot postpone the selection of the access plan to run.
It must be selected during compilation. However, if an access plan is created during compilation, the variable value is still unknown,
As a result, it cannot be used as the index selection input. 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.
Full table scan. 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 column operation will cause table scanning, including database functions and computing expressions.
You can 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 discard the index and perform full table operations.
Scan. 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 will
Indexes cannot be correctly used.

10. When using an index field as a condition, if the index is a composite index, you must use the first
The system can use this index only when fields are used as conditions. Otherwise, this index will not be used and
The field order is consistent with the index order.


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 scanning.
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 whether it exists in the parent result set
There are records that are not in the subresult 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
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 repeat
Use a data set in a large table or common table. 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 causing a large number of logs to increase the speed. If the data volume is small, in order to ease the system table resources, first
Create table, 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
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 on at the end
Nocount off. You do not need to send DONE_IN_PROC to the client after executing each statement of the stored procedure and trigger.
Message.


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 not
Compatible. 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.
Number. 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.
Write the complete join conditions in the 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, while
In addition, the optimizer can be simplified 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,
Sort 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 master table, and the physical order is the required order, which reduces disk I/O.
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 are some basic notes for improving the query speed. However, in more cases
Test and compare different statements to obtain the best solution. The best way is to test the SQL statement that implements the same function.
Which of the following statements has the least execution time? However, if the database contains a small amount of data, it cannot be compared.
Row plan: obtain multiple SQL statements that implement the same function to the query analyzer, and press CTRL + L to view the used query records.
Reference: the number of table scans (the two have the greatest impact on performance). The total query cost percentage is enough.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.