Common Methods to Improve SQL Server database efficiency

In the current Internet application development process, it is often found that the query or operation data speed is slow. There are many causes, which are common:
1. No index or no index is used (this is a defect in Database Design)
2. Low I/O throughput, resulting in a bottleneck effect.
3. Insufficient memory
4. slow network speed
5. The queried data volume is too large (multiple queries can be used)
6. Lock or deadlock (this is also a defect in program design)
7. Unnecessary rows and columns are returned.
8. The query statement is not good and is not optimized.
To operate databases efficiently, we must first ensure the correctness of the database design, and avoid violating the three paradigm principles unless otherwise required. Then we should consider optimizing the database performance and efficiency. In our daily work, we often use the following optimization methods:
1. Place data, logs, and indexes on different I/O devices to increase the reading speed. The larger the data size (size), the more important it is to increase I/O.
2. Upgrade the hardware.
3. Create an index based on the query conditions, optimize the index, optimize the access mode, and limit the data volume of the result set. The index should be as small as possible. Use a column with a small number of bytes to create an index.

Create a single index for fields with limited values.
4. the query time is proportional to the total length of the field value. Therefore, the variable length field in the database design time cannot be of the char type, but is of the varchar type. There are quite a few developers who like variable-length strings using char and then fill in spaces.
5. Rebuild the index DBCC reindex, DBCC indexdefrag, and contract the data and log DBCC shrinkdb and DBCC shrinkfile. Set automatic log shrinking. For large databases, do not set Automatic database growth, which will reduce the server performance.
6. Use the WHERE clause in the SELECT statement to limit the number of returned rows to avoid table scanning. If unnecessary data is returned, the server's I/O resources are wasted, increasing the network burden and reducing performance. If the table is large, the table is locked during the table scan and other connections are prohibited from accessing the table. The consequence is serious.
7. Try not to use the cursor. It occupies a large amount of resources. If you need row-by-row execution, try to use non-cursor technology, such as loop on the client, using temporary tables, table variables, subqueries, and case statements.
8. Use profiler to track the query, obtain the time required for the query, locate the SQL problem, and use the index optimizer to optimize the index.
9. Pay attention to the difference between Union and Union all. Use Union all whenever possible.
10. Use distinct unless necessary. Similar to union, it slows down the query.
11. Do not return unwanted rows or columns during Query
12. Use select Top 100/10 percent to limit the number of rows returned by the user or set rowcount to limit the rows to be operated.
13. Use the query analyzer to check whether the SQL statement query plan and evaluation analysis are the optimized SQL statements. Generally, 20% of the Code occupies 80% of the resources, and our optimization focuses on these slow points.
14. If an in or query is not indexed, use the display statement to specify the index: Select * From tablename (Index = ix_title) where sex in ('male ', female ')
15. There is a principle in the database that the code is closer to the data, the better. Therefore, the default rule, trigger, and constraint are selected first (constraints such as the external key checkunique ......, The maximum length of the data type, etc. are constraints), stored procedures. In this way, not only the maintenance work is small, the programming quality is high, and the execution speed is fast.
16. If you want to insert a large binary value to the image column, use the stored procedure. Do not insert it using an embedded insert statement. Because the application first converts the binary value to a string (twice the size of the string), the server receives the character and converts it to a binary value. Stored

Without these actions. Method: Create procedure p_insert as insert into table (fimage) values (@ image). Call this stored procedure on the foreground to pass in binary parameters, which significantly improves the processing speed.
17. Between is faster in some cases than in, and between can locate a range faster Based on the index. Use the query optimizer to see the difference. Select * From chineseresume where title in ('male', 'femal') and select * From chineseresume where between 'male' and 'female 'are the same functions. Because in may be more than once, it may be slower sometimes.
18. Do not use useless transactions in the program.
19. The or clause can be divided into multiple queries and connected to multiple queries through Union. Their speed is only related to whether an index is used. If a query requires a Union Index, Union all is more efficient. No index is used for multiple or statements, and it is rewritten to the form of union to try to match the index. Whether or not indexes are used in a key issue.
20. Using a view as few as possible is inefficient. Operations on a view are slower than operations on a table. You can replace it with stored procedure. In particular, do not use nested views. nested views increase the difficulty of searching for original data.
21. Do not use distinct or order by when necessary. These actions can be changed
. They increase additional overhead. This is the same as Union and Union all.
22. In the post-in nominal value list, place the most frequent values at the beginning and the least value at the end to reduce the number of judgments.
23. Updating multiple records at a time is faster than updating multiple records at a time, that is, batch processing is good.
24. Try to process data on the server to reduce network overhead, such as using stored procedures. Stored procedures are compiled, optimized, organized into an execution plan, and stored in the database as SQL statements. They are a collection of control flow languages and are fast.
25. Monitor the load of the corresponding hardware through SQL server performance monitor memory: Page faults/sec counters. If this value increases occasionally, it indicates that there were threads competing for memory. If it continues high, memory may be the bottleneck.

# Database Technology