1. To optimize the query, avoid full table scan as far as possible, and first consider creating indexes on the columns involved in WHERE and ORDER by.
2. Avoid using it in where clauses! = or <> otherwise the engine will abandon the index for a full table scan.
3. Avoid null values in the WHERE clause. Otherwise, the engine will abandon the use of index and perform full table scan.
select id from t where num is null
Select * from num where num is null; select * from num where num is null;
select id from t where num=0
4. Avoid the use of OR in the WHERE clause to join conditions, otherwise the engine will abandon the use of index and perform full table scan, such as:
select id from t where num=10 or num=20
It can be queried like this:
select id from t where num=10
union all
select id from t where num=20
5. The following query will also cause a full table scan:
select id from t where name like ‘%abc%’
For efficiency, consider full-text retrieval.
6. In and not in should also be used with caution, otherwise it will cause a full table scan.
Select id from t where num in(1,2,3)
For continuous numbers, use between instead of in:
select id from t where num between 1 and 3
7. If you use parameters in the WHERE clause, it will also cause a full table scan. Because SQL resolves 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 unknown and therefore cannot be used as an input for index selection. The following statement will perform a full table scan:
select id from t where num=@num
You can force the query to use indexes instead:
Select id from t with(index) where num=@num
8. Expression operations on fields in the WHERE clause should be avoided as much as possible. This will cause the engine to abandon the use of indexes and perform a full table scan. Such as:
select id from t where num/2=100
Should be changed to:
select id from t where num=100*2
9. Avoid functional manipulation of fields in the WHERE clause, which will cause the engine to abandon indexes and perform a full table scan. Such as:
Select id from t where substring(name,1,3)=’ ABC ‘–name id starting with ABC
Select id from t where datediff(day,createdate,’2005-11-30′)=0–‘2005-11-30
Should be changed to:
select id from t where name like ‘abc%’
select id from t where createdate>=’2005-11-30′ and createdate<‘2005-12-1’
10. Do not perform functions, arithmetic operations, or other expression operations to the left of the “=” in the WHERE clause, or the system may not use the index properly.
11. When using an index field as a condition, if the index is a compound index, the first field in the index must be used as a condition to ensure that the system can use the index. Otherwise, the index will not be used, and the field order should be as consistent as possible with the index order.
12. Do not write meaningless queries, such as generating an empty table structure:
select col1,col2 into #t from t where 1=0
This type of code does not return any result set, but consumes system resources.
create table #t(…)
13. It is often a good choice to use exists instead of in:
select num from a where num in(select num from b)
Replace with the following statement:
select num from a where exists(select 1 from b where num=a.num)
14. Not all indexes are valid for query. SQL queries are optimized based on the data in the table.
15. More indexes are not always better. While indexes can improve the efficiency of select operations, they can also reduce the efficiency of insert and update operations. The number of indexes in a table should not exceed 6. If there are too many, you should consider whether it is necessary to build indexes on infrequently used columns.
16. Avoid updating clustered index columns as much as possible because the order of clustered index columns is the physical storage order of table records. Changes in clustered index columns will result in the adjustment of the order of table records, which will consume considerable resources. If your application system needs to update clustered index data columns frequently, you need to consider whether to build the clustered index as a clustered index.
17. Use numeric fields as much as possible. If only numeric fields contain numeric information, do not design them as characters, which reduces query and join performance and increases storage overhead. This is because the engine compares each character in the string one by one while processing queries and joins, whereas for numeric types it only needs to compare once.
18. Use vARCHar /nvarchar instead of char/nchar whenever possible, because the storage space of a longer field is small, and the search efficiency of a relatively small field is obviously higher.
19. Do not use select * from t anywhere, replace “*” with a list of specific fields, and do not return any fields that are not needed.
20. Use table variables instead of temporary tables whenever possible. If the table variables contain a lot of data, be aware that the indexes are very limited (only primary key indexes).
21. Avoid frequent creation and deletion of temporary tables to reduce system table resource consumption.
22. Temporary tables are not unusable, and their proper use can make some routines more efficient, for example, when a large table or a data set in a commonly used table needs to be referenced repeatedly. However, for one-off events, it is best to use exported tables.
23. When creating a temporary table, if a large amount of data is inserted at a time, you can use select INTO instead of create table to avoid creating a large number of logs and improve the speed. If the amount of data is small, to reduce the resources of the system table, create table first, then insert.
24. If temporary tables are used, you must explicitly delete all temporary tables at the end of the stored procedure, truncate TABLE first, and then DROP TABLE. In this way, system tables cannot be locked for a long time.
25. Avoid using cursors as they are inefficient, and you should consider rewriting them if they operate on more than 10,000 rows.
26. Before using a cursor based approach or a temporary table approach, look for a set based solution to solve the problem, which is usually more efficient.
27. Like temporary tables, cursors are not unusable. Using the FAST_FORWARD cursor for small data sets is generally preferable to other line-by-line processing methods, especially if you have to reference several tables to get the data you want. Routines that include “totals” in the result set are generally faster to execute than those that use cursors. If development time allows, both the vernior-based approach and the set-based approach can be tried to see which works better.
28. SET SET NOCOUNT ON at the start and SET NOCOUNT OFF at the end of all stored procedures and triggers. 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.
29. Try to avoid returning a large amount of data to the client. If the amount of data is too large, consider whether the corresponding demand is reasonable.
30. Avoid large transaction operations and improve system concurrency.