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

This article mainly introduces "what is the reason for slow SQL query". In daily operation, I believe many people have doubts about the reason why SQL query is slow. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubt of "what is the reason why SQL query is slow?" Next, please follow the editor to study!

There are many reasons for slow query speed, and the common ones are as follows:

1. There is no index or no index is used (this is the most common problem of slow query and a flaw in programming)

2. The throughput of IPUP O is small, which forms the bottleneck effect.

3. The query is not optimized because the computed column is not created.

4. Insufficient memory

5. Slow network speed

6. The amount of data queried is too large (multiple queries can be used, and other methods can reduce the amount of data)

7. Lock or deadlock (this is also the most common problem of slow query and is a flaw in programming)

8. Sp_lock,sp_who, viewed by active users, due to read and write competition for resources.

9. Unnecessary rows and columns are returned

10. The query statement is not good, and ● can optimize the query in the following ways:

(1), put the data, logs and indexes on different I Tempdb O devices to increase the reading speed. In the past, you can put Tempdb on the RAID0, but SQL2000 no longer supports it. The larger the amount of data (size) is, the more important it is to improve the I _ map O.

(2) split the table vertically and horizontally, reduce the size of the table (sp_spaceuse)

(3) upgrade hardware

(4) according to the query conditions, establish the index, optimize the index, optimize the access mode, and limit the amount of data in the result set. Note that the fill factor is appropriate (it is best to use the default value of 0). The index should be as small as possible. It is better to use columns with a small number of bytes to build an index (reference index creation). Do not build a single index on fields with limited values, such as gender fields.

(5) improve the speed of the network

(6) expand the memory of the server, Windows 2000 and SQL server 2000 can support 4-8 GB of memory. Configure virtual memory: the size of virtual memory should be configured based on services running concurrently on the computer. When running Microsoft SQL Server 2000, consider setting the virtual memory size to 1.5 times the physical memory installed on your computer. If you also have the full-text search feature installed and you plan to run the Microsoft search service to perform full-text indexing and queries, consider configuring virtual memory to be at least three times the physical memory installed on your computer. Configure the SQL Server max server memory server configuration option to 1.5 times the physical memory (half the virtual memory size setting).

(7) increase the number of server CPU; but it must be understood that parallel processing serial processing needs more resources such as memory. The use of parallel or serial travel is selected by MsSQL automatic evaluation. A single task is broken down into multiple tasks and can be run on the processor. For example, delayed query sorting, join, scanning and GROUP BY words are executed at the same time, SQL SERVER determines the optimal parallel level according to the load of the system, and complex queries that consume a lot of CPU are most suitable for parallel processing. But the update operation UPDATE,INSERT,DELETE cannot be processed in parallel.

(8) if you are using like for query, it is not possible to simply use index, but full-text indexing consumes space. When like a% uses the index like% a without referencing the like% a% query, the query time is proportional to the total length of the field value, so you can't use the CHAR type, but VARCHAR. Create a full-text index for long values of the field.

(9), DB Server and APPLication Server separation; OLTP and OLAP separation

(10) distributed partitioned views can be used to implement server consortia. A consortium is a group of separately managed servers, but they cooperate with each other to share the processing load of the system. This mechanism of forming a consortium of database servers through partitioned data can expand a set of servers to support the processing needs of large, multi-tier Web sites. For more information, see designing a federated database server. (refer to SQL help File Partition View)

At this point, the study on "what is the reason why SQL query is slow" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.