Some small places that are easy to overlook in SQL Development (iii)

Original: Some small places that are easy to overlook in SQL Development (iii)

Purpose: This article I would like to say about my work in the use of in and union all.

index definition : Microsoft SQL Server provides two types of indexes: Clustered indexes (clustered index, also called clustered indexes, clustered indexes), and nonclustered indexes ( Nonclustered index, also known as non-clustered indexes, nonclustered indexes.

SARG definition : used to limit the search to an operation, because it usually refers to a specific match, a worthy range of matching or more than two conditions and connection. The form is as follows: Column name operators < constants or variables > or < constants or variables > operation column names can appear on one side of the operator, while constants or variables appear on the other side of the operator.

SARG Meaning: If a stage can be used as a scanning parameter (SARG), then it is called an optimization, and the index can be used to quickly obtain the required data.

discuss the question : Now some views directly say in does not conform to the SARG standard, therefore in the query all produces the full table scan.

my point of view : This is probably the case in the early database, at least SQL2005 enough to prove that the above statement is wrong.

Case: There is a membership table (member), which contains proxy information, where the agent number Proxyid is created on the index. The quantity is more than million.

Requirements:Queries the proxy information for the specified agent.

Query sql:
Method 1:select related fields from member where Proxyid in (' ID1 ', ' ID2 ',.....)
Method 2:select related fields from member where proxyid= ' ID1 '
UNION ALL
Select related fields from member where proxyid= ' ID1 '
UNION ALL
...
How to compare:
First:p the number of Roxyid more, I entered the test 30 Proxyid
The following are the execution plan graphs for the two methods:

1:union All Execution plan diagram: Because the graphs are longer, all are divided into two parts.

2:in diagram of the execution plan:

Conclusion:
1: Regardless of the method, the index will be used.
2: The execution plan for both are different: when the number of Proxyid is relatively long, the in will directly look up the index, and there is a filtered operation. Union All is a connection of n nested queries.

3: The agent number is relatively long, the Union all efficiency is significantly higher than in

Second :p the number of Roxyid is smaller, now input two, 6, 15, the execution plan diagram can be seen, when the number of Proxyid is 15 o'clock, the direct lookup index, and 2 and 6 are selected nested query to complete. Because the Union The execution plan diagram for all is always the same, all the execution plans that are pasted under different Proxyid with in query:

1: Two agent execution plan diagram:

2: Six agent execution plan diagram:

3:15 Agent execution plan diagram:

Conclusion:
1: Regardless of the method, the index will be used.

The 2:proxyid data volume is smaller when the execution time and the union all gap is small.

3:in the number of data in the same time, the execution plan will be the same, the data amount of hours will be nested query, and vice versa directly query index and other related auxiliary operations.

Conclusion: the current database engine will be based on the query cost analysis to select the optimal query algorithm to execute, can not take the previous point of view to now say. The difference between union and all is not always the same and depends on what the situation is. Like in and or, for or, There is an argument that the index cannot be applied, and in fact, as in, the upper version will be indexed.

Note:

This article refers to: MSDN, network-related Materials

Some small places that are easy to overlook in SQL Development (iii)