After the data in the SQL SERVER table reaches a certain amount (more than one million), the speed of executing queries and updating statements will become quite slow, and I often encounter such problems during this period.
One way to increase speed is to use indexes. Specifically, there are two ways, which are explained as follows:
The first method: Modify the WHERE statement of the query or update statement, and try to include the columns included in the index in the WHERE statement。 In particular, including columns in the aggregated index is helpful for faster execution.
The second method: Establish an appropriate index based on the query or update statement。 This requires using the index optimization wizard provided by SQL SERVER Query Analyzer, which analyzes SQL statements to arrive at the appropriate index.
It should also be said that when solving such problems, it is necessary to consider joint and collateral issues. If a stored procedure is slow to execute, in addition to optimizing the statements in the stored procedure, it is also necessary to consider whether the process triggered by the statement in the stored procedure also needs to be optimized, such as whether the statements in the corresponding triggers triggered by updating or inserting table statements need to be optimized.
The third method: stage the data in the table to the temporary table before processing the data.This approach can greatly improve the speed of execution, but it does not solve the concurrency problem. The statement is as follows:
SELECT * INTO #临时表 FROM the official table
The fourth method: use the fast enter-only cursor.If no modification is involved, you can use the Quick Enter only cursor instead of the normal cursor to increase the speed.
Find the optimization points and optimize:
1. Using Execution Plans: Query Analyzer menu "Query -> Display Estimated Execution Plans"
2. In the storage process, find the statement that needs to be optimized
3. Create an index using the Index Optimization Wizard: Log in to the Query Analyzer with SA and go to the menu "Query -> Index Optimization Wizard"
|
Posted on 1/27/2015 8:16:51 PM
|
|
|
