Shulou(Shulou.com)06/01 Report--

Some optimization recommendations:

1. Performance of SELECT statements, which can be viewed through explain statements.

2. Database object stored procedures are faster than single statements.

3. Reduce the frequency of select *

4. Indexes can speed up data queries, but for fields or tables that are not looked up often, it is best not to build indexes.

5. The execution efficiency of LIKE keyword is low, which is generally replaced by "FULL TEXT".

6. Try to avoid using or to join conditions in the where clause, otherwise it will cause the engine to give up using indexes and perform full table scans, which is not in line with optimization principles, such as:

Select id from t where num=10 or num=20

You can query it like this:

Select id from t where num=10union allselect id from t where num=20

7. In and not in should also be used with caution, otherwise it will cause full table scanning, such as:

Select id from t where num in (1, 2, 3)

8. For consecutive values, don't use in if you can use between:

Select id from t where num between 1 and 3

9. In many cases, using exists instead of in is a good choice.

Select num from a where num in (select num from b)

Replace it with the following statement:

Select num from a where exists (select 1 from b where num=a.num)

10. Try to use numeric fields, and try not to design character fields that contain only numeric information, which will reduce the performance of queries and connections, and increase storage overhead. This is because the engine compares each character in the string one by one when processing queries and connections, while for numeric types, it only needs to be compared once.

11. Use varchar/nvarchar instead of char/nchar as much as possible, because first of all, the storage space of long fields is small, which can save storage space, and secondly, for queries, searching in a relatively small field is obviously more efficient.

Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources.

13. 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 improve speed; 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.

14. Avoid using cursors as much as possible, because cursors are inefficient, and if you operate on more than 10,000 rows of data, you should consider rewriting them.

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

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.