What are the reasons that affect your query speed?
- Speed is not power, unstable.
- There is not enough memory on the server, or there is not enough memory allocated for SQL.
- SQL statement Design is unreasonable
- There is no corresponding index, the index is unreasonable
- Table data too large no effective partition design
- Database design too 2, there is a lot of data redundancy
So how do we find the reason for the slowness?
- First you need to know whether it is related to SQL statements, to ensure that the machine is not turned on, the server hardware configuration is poor, no net you say P AH
- SQL Server Profiler, analysis of SQL slow related statements, is the execution time is too long, the use of system resources, too many CPU tools
- And then here's what this article is about, SQL optimization methods and techniques, avoiding some unreasonable SQL statements, taking the advantage of SQL
- Then judge whether to use, reasonable statistics.
- A reasonable index is used in the confirmation table.
- Too many data tables, to partition, to narrow the search scope
Parse the time of the SQL statement execution
1. Log the query time and CPU consumption time
Set Statistics on Select * from Detitalindex Set Statistics off
2. Record I/O dominance
Set Statistics on Select * from dbo. Productsetstatisticsoff
Scan count: Number of indexes or table scans
Logical reads: number of pages read in the data cache
Physical reads: Number of pages read from disk
Read-ahead: Number of pages that are cached from disk during query
LOB logical reads: The number of pages of image,text,ntext or large data read from the data cache
LOB physical reads: The number of pages that are read from disk, Image,text,ntext, or large data
LOB pre-read: The number of pages of image,text,ntext or large data that is placed from the disk during a query
If the number of physical and pre-read times is high, the index can be used for optimization.
If you do not want to use SQL commands to view the content, you can right-click to find the query option in the new SQL Server query
Ching SET STATISTICS time and io OK.
3. View the Execution plan
The mouse over this icon will appear the detailed steps of the query, and see which SQL cost you how much, if which table is too large, to prove that the table index design is incorrect, or performance needs to be improved.
Select is a steel knife, grinding good seconds to kill the Army generals! Grinding is not good is suicide.
1. Make sure not to appear *, use the column instead of *
2. Try to avoid unnecessary columns when using the Where query
3. Use top or distinct to reduce redundant or repetitive
(as the saying goes, what is boring is a double-edged sword)
1.distinct
Distinct is used in the case of querying a field or a few fields, which avoids duplication of data and results in optimization of the query.
However, a large number of query fields are used, which can greatly reduce the query efficiency.
This test result represents the use of Distint, where the database uses CPU to filter the data. So use less distinct.
2. Determine if the table has data
Select Count (*fromselectTop(1 from Indentdetails
It's clear that the following outright
3. Connection Query optimization
Select * from (Select*from leftjoinson= Grade.gradeid Select*from leftjoins on S.gradeid= G.gradeid)
Reducing table joins can improve query performance, but this gap is widened by the size of the data.
Modify Delete SQL Performance optimizations
If the operations are clustered, the CPU utilization is too high to affect other users ' access to the database.
And if the individual iterations are working, then the efficiency is too low.
So we use a compromise approach. Block operations
Delete orderwhere ID<Deletewhere ID>= + and ID<Delete orderwhere ID>=2000 and ID< .....