SQL Server query statement optimization)

1. UseProgramTo minimize the number of accesses to the database. By searching for parameters, You can minimize the number of rows accessed to the table and the result set, thus reducing the network burden; separate operations should be processed separately to improve the response speed each time. When using SQL in the data window, try to place the indexes in the first column selected;AlgorithmThe structure should be as simple as possible; During the query, do not use too many wildcards, such as the select * from T1 statement. To use a few columns, select several columns such as select col1, col2 from T1; if possible, try to limit the number of rows in the result set as much as possible, for example, select top 300 col1, col2, col3 from T1, because in some cases users do not need that much data. Do not use database cursors in applications. cursors are very useful tools, but they are more open-ended than general set-oriented SQL statements. data search is extracted in a specific order.

2. 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.

3. Avoid performing function or expression operations on fields in the WHERE clause whenever possible. This will cause the engine to stop using 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.

4. Avoid using it! = Or <>, is null, is not null, in, not in, and other operators, because this will make the system unable to use the index, but can only directly search for data in the table. For example:
Select ID from employee where ID! = 'B %'
The optimizer cannot use indexes to determine the number of rows to be hit. Therefore, you need to search all rows in the table.
 
5. Use numeric fields whenever possible. Some developers and database administrators prefer to include numeric fields.
Designed to be optimized, this reduces query and connection performance and increases storage overhead. This is because the engine compares each character in the query processing and connection back one by one, and only one comparison is required for the number type.

6. Use the exists and not exists clauses reasonably. As follows:
1. Select sum (t1.c1) from T1 where (
(Select count (*) from T2 where t2.c2 = t1.c2> 0)
2. 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:
1. 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)

2. 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

3. 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.

7. 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.

8. Use join conditions separately. In some cases, there may be more than one join condition between two tables. In this case, write the complete join 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.

9. Eliminate sequential access to data in rows of a large table
although indexes exist on all check columns, some forms of where clauses force the optimizer to use sequential access. For example:
select * from orders where (customer_num = 104 and order_num> 1001) or
order_num = 1008
the solution can use the Union to avoid sequential access:
select * from orders where customer_num = 104 and order_num> 1001
Union
select * from orders where order_num = 1008
query.

10. Avoid difficult Regular Expressions
the like keyword supports wildcard matching, technically called regular expressions. However, this matching is especially time-consuming. For example, select * from customer where zipcode like "98 _"
even if an index is created on the zipcode field, sequential scanning is also used in this case. For example,
if you change the statement to select * from customer where zipcode> "98000", indexes are used to query the statement during query
, which obviously increases the speed.
11. Use view to accelerate query
sort a subset of a table and create a view. Sometimes, query is accelerated. It helps avoid multiple sorting
operations and simplifies the optimizer's work in other aspects. 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 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, reducing the disk size.
I/O, so the query workload can be greatly reduced.

12. If you can use between, do not use in.
Select * from T1 where ID in (10, 11, 12, 13, 14)
Changed:
Select * from T1 where ID between 10 and 14
Because in will make the system unable to use the index, but can only directly search the data in the table.

13. Distinct does not require group
Select orderid from details where unitprice> 10 group by orderid
You can change it:
Select distinct orderid from details where unitprice> 10

14. partial use of Indexes
1. Select employeeid, firstname, lastname
From names
Where dept = 'prod' or city = 'Orlando 'or division = 'food'

2. Select employeeid, firstname, lastname from names where dept = 'prod'
Union all
Select employeeid, firstname, lastname from names where city = 'Orlando'
Union all
Select employeeid, firstname, lastname from names where division = 'food'
If the dept column has an index, query 2 can partially use the index, and query 1 cannot.

15. Do not use Union if Union all is used.
Union all does not execute the select distinct function, which reduces unnecessary resources.

16. Do not write any queries that do not do anything.
For example, select col1 from T1 where 1 = 0
Select col1 from T1 where col1 = 1 and col1 = 2
This type of Dead Code does not return any result set, but it consumes system resources.

17. Try not to use the select into statement.
The Select inot statement will lock the table and prevent other users from accessing the table.

18. When necessary, force the query optimizer to use an index
Select * from T1 where nextprocess = 1 and processid in (8, 32, 45)
Changed:
Select * from T1 (Index = ix_processid) Where nextprocess = 1 and processid in (8, 32, 45)
The query optimizer forcibly uses the index ix_processid to execute the query.

19. Although the update and delete statements are basically fixed, some suggestions are given for the update statement:
A) Try not to modify the primary key field.
B) when modifying varchar fields, try to replace them with values of the same length.
C) Minimize the update operations on tables containing update triggers.
D) Avoid columns to be copied to other databases by update.
E) avoid updating columns with many indexes.
F) avoid updating columns in the WHERE clause condition.

The above mentioned are some basic notes for improving the query speed. However, in more cases, we often need to experiment with different statements repeatedly 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.
Simple stored procedures can be automatically generated using the Wizard: Click the run wizard icon in the Enterprise Manager toolbar, click "Database", "Create Stored Procedure wizard ". Debugging of complex stored procedures: the object browser on the left side of the query analyzer (no? Press F8) Select the stored procedure to be debugged, right-click, click debug, and enter the parameter for execution. A floating toolbar is displayed, including one-step execution and breakpoint settings.