SQL Server Performance 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 require more overhead than general set-oriented SQL statements. extract data searches 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*2Select*From record where substring (card_no,1,4)='5378'
 

Should be changed:

  Select*From record where card_no like'5378%'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----Tbl a indicates that TBL replaces where not exists (select*From dtl_tbl B where a. hdr_key=B. hdr_key)2. Select a. hdr_key from hdr_tbl a left join dtl_tbl B on A. hdr_key=B. hdr_key where B. hdr_key is null3. 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. Avoid using non-headers in 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. Eliminates sequential access to data in large table rows

Although all check columns are indexed, some forms of where clauses force the optimizer to use sequential access. For example:

  Select*From orders where (customer_num=104And order_num>1001) Or order_num=1008
 

The solution can be to use the Union to avoid sequential access:

  Select * from orders where customer_num=104And order_num>1001Union select * from orders where order_num=1008 
 

In this way, you can use the index path to process queries.

10. Avoid difficult Regular Expressions

The like keyword supports wildcard matching, technically called a regular expression. However, this matching is especially time-consuming. Example: Select * from customer where zipcode like "98 ___"

Even if an index is created on the zipcode field, sequential scanning is used in this case. If you change the statement to select * from customer where zipcode> "98000", the query will be executed using the index, which will obviously increase the speed.

11. 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>0And 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>0Order 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.

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 ID10And14
 

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> 10Group 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, such:

 
  Select col1 from T1 where1=0Select col1 from T1 where col1=1And 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= 1And processid in (8,32,45)
 

Changed:

  Select*From T1 (Index=Ix_processid) Where nextprocess= 1And 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.

You can use the Wizard to automatically generate a simple stored procedure: Click the run wizard icon on the Enterprise Manager toolbar, and click "Database" and "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.