Summarizes the methods for optimizing SQL Execution progress and SQL Execution progress.

Summarizes the methods for optimizing SQL Execution progress and SQL Execution progress.

In this article, I will discuss with you how to optimize the SQL Execution progress.

Clustered index Scan

SELECT * FROM C_SY_Ownedstorm 

Clustered index scan is faster than table Scan

Clustered index scan: occurs in a clustered table, which is also equivalent to a full table scan operation. However, it is more efficient when performing operations such as conditions on Clustered columns.

Table Scan

SELECT *  FROM #temp 

Table scan: If a heap table has no available indexes, a table scan is performed, indicating that the entire table is scanned once.

Test SQL

CREATE TABLE t1(c1 INT, c2 VARCHAR (8000));   GO   DECLARE @a INT;   SELECT @a = 1;   WHILE (@a <= 5000)   BEGIN           INSERT INTO t1 VALUES (@a, replicate('a', 5000))     SELECT @a = @a + 1   END   GO SELECT count(1) FROM t1  group by c1 

Hash matching:

Hash matching is used to create a hash table based on the hash algorithm for the two tables on the right, then there is another table with a large number of data rows to match the previously formed hash table to find data. This is basically the process. However, the emergence of hash matching must be vigilant. When one of the two tables on the Right of hash matching is significantly less than the other, hash matching is more efficient, otherwise, efficiency is affected. Hash matching may occur in the following situations:

There are missing or incorrect Indexes

The where clause is missing.

The where clause contains column type conversion or data operations, so that indexes cannot be used.

Although hash matching is more efficient in some cases, it does not mean that it is not better to improve the query efficiency, for example, add an appropriate index or use the where statement to reduce the data volume. In other words, when there is a hash matching operation, we should pay attention to whether there are other methods to improve the query efficiency. If not, hash matching may be the best choice.

Clustered index search:

CREATE UNIQUE CLUSTERED INDEX _Id  ON t1(c1)  select * from t1  where c1=3 

Sort:

Sorting consumes performance. In SQL server, sorting is performed after data is found.

select * from t1  order by desc 

Loop nesting

Nested loops are the best strategy for small data tables that use simple inner join. It is best suited when the number of records in two tables is very different and there is an index on the connected columns. Nested loop connections require the least I/O and comparison.

Nested loops loop one record at a time in the External table (usually a table with a small amount of data), and then find and output matched records in the internal table. There are many names about nested loop policies. For example, querying the entire table or index is called a nested loop connection of Naive (ignorant. When normal or temporary indexes are used, they are called index nested loop connections or temporary index nested loop connections.

Merge connections

The merge connection is used to compare the two input rows of storage while reading the data. In each step, compare the next line of each input. If the two rows are the same, output a connected row and continue. If the rows are different, discard the less of the two input rows and continue. Because the input is stored, any row discarded by the connection must be smaller than any other row in the two inputs, so the connection can never be connected. To merge connections, you do not need to scan each row of the two inputs. As long as the end of one of the two inputs is reached, the merged connection stops scanning.

The total consumption of nested loop connections is proportional to the product of the rows in the input table. Unlike nested loop connections, the tables that are joined are read at most once. The total consumption is proportional to the total number of input rows, therefore, connection is a good choice for a large number of input.

The above is a summary of the Optimization Methods for SQL Execution progress. I hope you will have some gains.