Shulou(Shulou.com)05/31 Report--

SQL Server database sql optimization considerations, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

Today, we mainly share 25 points for attention when sqlserver database is doing sql optimization.

SQL optimization items

1. Try to avoid judging the null value of a field in the where clause, otherwise it will cause the engine to abandon the use of the index and perform a full table scan, such as:

SELECT ID FROM T WHERE NUM IS NULL

You can set the default value of 0 on NUM to ensure that there is no null value for the NUM column in the table, and then query it like this:

SELECT ID FROM T WHERE NUM=0

two。 The use of the! = or operator in the where clause should be avoided as much as possible, otherwise the engine will abandon the use of indexes and perform a full table scan. The optimizer will not be able to determine the number of rows to hit through the index, so it needs to search all rows of the table

3. Try to avoid using OR to join conditions in the where clause, otherwise it will cause the engine to abandon the use of indexes and perform full table scans, such as:

SELECT ID FROM T WHERE NUM=10 OR NUM=20

You can query it like this:

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 and can only search the data in the table directly. Such as:

SELECT ID FROM T WHERE NUM IN (1, 2, 3)

For consecutive values, do not use IN if you can use BETWEEN, such as:

SELECT ID FROM T WHERE NUM BETWEEN 1 AND 3

5. Try to avoid using non-initials in indexed character data. This also makes it impossible for the engine to take advantage of indexes.

See the following example:

SELECT * FROM T1 WHERE NAME LIKE'% L%'- > No index

SELECT * FROM T1 WHERE SUBSTING (NAME,2,1) ='L'-> No index

SELECT * FROM T1 WHERE NAME LIKE'L%'- > indexed

Even if the NAME field is indexed, the first two queries still cannot use the index to speed up the operation, and the engine has to operate on all the data in the table one by one to complete the task. The third query can use an index to speed up the operation.

6. Forcing the query optimizer to use an index if necessary, such as using parameters in the where clause, can also result in a full table scan. Because SQL parses local variables only at run time, the optimizer cannot defer the choice of an access plan until run time; 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 and cannot be used as an input to the index selection. A full table scan will be performed as follows:

SELECT ID FROM T WHERE NUM=@NUM

You can force the query to use the index instead:

SELECT ID FROM T WITH (INDEX (index name)) WHERE NUM=@NUM

7. Expression manipulation of fields in the where clause should be avoided as far as possible, which will cause the engine to abandon the use of indexes and perform full table scans. Such as:

SELECT * FROM T1 WHERE F1 Compact 2100

It should be changed to:

SELECT * FROM T1 WHERE F1 100 SELECT * FROM RECORD WHERE SUBSTRING (CARD_NO,1,4) = '5378'

It should be changed to:

SELECT * FROM RECORD WHERE CARD_NO LIKE '5378%' SELECT member_number, first_name, last_name FROM members WHERE DATEDIFF (yy,datofbirth,GETDATE ()) > 21

It should be changed to:

SELECT member_number, first_name, last_name FROM members WHERE dateofbirth

< DATEADD(yy,-21,GETDATE()) 即：任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。 8. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如： select id from t where substring(name,1,3)='abc' ---->

Id select id from t where datediff (day,createdate,'2005-11-30') where name begins with abc = 0-> '2005-11-30' generated id

It should be changed to:

Select id from t where name like 'abc%' select id from t where createdate > =' 2005-11-30 'and createdate0)

Replace it with the following statement:

SELECT SUM (T1.C1) FROM T1WHERE EXISTS (SELECT * FROM T2 WHERE T2.C2=T1.C2)

Both produce the same results, but the efficiency of the latter is obviously higher than that of the former. Because the latter does not produce a large number of locked table scans or index scans.

If you want to check whether a record exists in the table, don't use count (*) as inefficient and a waste of server resources. You can use EXISTS instead. Such as:

IF (SELECT COUNT (*) FROM table_name WHERE column_name = 'xxx')

It 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 with a child result set to find out whether there are records in the parent result set but not in the child result set, such as:

SELECT a.hdr_key FROM hdr_tbl a-tbl a means tbl substitutes the alias a for 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 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)

All three writing methods can get the same correct results, but the efficiency decreases in turn.

twelve。 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 creating and deleting temporary tables frequently to reduce the consumption of system table resources.

14. Temporary tables are not unavailable, and using them appropriately can make some routines more efficient, for example, when you need to re-reference a dataset in a large or common table. However, for one-time events, it is best to use an export table.

15. When creating a new temporary table, if you insert a large amount of data at one time, you can use select into instead of create table to avoid causing a lot of log to speed up; if the amount of data is small, in order to ease the resources of the system table, you should first create table, and then insert.

Note: the SELECT INTO statement will cause the table to be locked, preventing other users from accessing the table

16. If temporary tables are used, be sure to explicitly delete all temporary tables at the end of the stored procedure, first truncate table, and then drop table, to avoid prolonged locking of system tables.

17. Set SET NOCOUNT ON at the beginning of all stored procedures and triggers and set SET NOCOUNT OFF at the end. There is no need to send a DONE_IN_PROC message to the client after each statement of the stored procedure and trigger is executed.

18. Try to avoid large transaction operations and improve the concurrency ability of the system.

19. Try to avoid returning a large amount of data to the client. If the amount of data is too large, you should 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. The incompatibility of data types may prevent the optimizer from performing some optimizations that could have been done. For example:

SELECT name FROM employee WHERE salary > 60000

In this statement, if the salary field is of type money, it is difficult for the optimizer to optimize it because 60000 is an integer. We should convert integers to monetary ones when programming, rather than waiting for runtime conversions.

21. Make full use of the join condition, in some cases, there may be more than one join condition between two tables, and then write the join condition completely in the WHERE clause, which may greatly improve the query speed.

Example:

SELECT SUM (A.AMOUNT) FROM ACCOUNT A Magna Card B WHERE A.CARD_NO = B.CARD_NO SELECT SUM (A.AMOUNT) FROM ACCOUNT A Magi Card B WHERE A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO=B.ACCOUNT_NO

The second sentence will be executed much faster than the first sentence.

twenty-two。 Use views to accelerate queries

Sorting a subset of tables and creating views can sometimes speed up queries. It helps avoid multiple sorting operations and 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 this query is to be executed more than once, you can find all unpaid customers in a view and sort by 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 as follows:

SELECT * FROM V_CUST_RCVLBES WHERE postcode > 98000

The number of rows in the view is less than that in the main table, and the physical order is the required order, reducing the disk Ibank O, so the query workload can be greatly reduced.

23. Those who can use DISTINCT do not need GROUP BY.

SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID

Can be changed to:

SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10

24. If you can use UNION ALL, do not use UNION.

UNION ALL does not execute the SELECT DISTINCT function, which reduces a lot of unnecessary resources

25. Try not to use SELECT INTO statements.

The SELECT INOT statement causes the table to be locked, preventing other users from accessing the table.

What we mentioned above are some basic precautions to improve the query speed, but in more cases, we often need to try and compare different statements over and over again to get the best solution. The best way, of course, is to test, to see which SQL statement to achieve the same function takes the least execution time, but if the amount of data in the database is very small, it can not be compared, then you can use to view the execution plan, that is, to achieve the same function of multiple SQL statements to the query analyzer, by CTRL+L to see the index used, the number of table scans (these two have the greatest impact on performance), and the overall query cost percentage.

After reading the above, have you mastered the methods of sql optimization of SQL Server database? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.