How to optimize physical queries in SQL statements

Several friends have such questions:

select * from tab1, tab2 where tab1.id = tab2.id and tab1.col1 = 123 and tab2.col1 = 'abc'

According to the execution order you described, tab1 and tab2 are first given the Cartesian product, and then filtered by tab1.col1 = 123 and tab2.col1 = 'abc. In this case, the efficiency is not very low. Is the database so stupid?
I think many people will have this question, including the question I raised when I first studied. In this article, I will discuss the physical Query Processing of SQL Server. First, we must understand the differences between logical processing and physical processing. Logical Processing refers to the results produced by executing a query, then, each stage of the logical query is the logic execution sequence of the query. The correct results can be obtained according to the sequence, just as we do the four mixed operations, after division, add or subtract to get the correct result.

Therefore, logical queries only care about generating a desired and correct result, and do not care about the resource consumption required to produce this result. The physical processing is how to get this result, so the performance issue will be considered at this time. Next we will discuss how to execute this physical processing.

When a query arrives at the database engine, the database engine needs to execute the query plan for this query. There are two situations at this time, one possibility is that the query plan for this query is already in the cache. In this case, the query plan is directly executed. The query plan for this query cannot be found in the cache. What should I do? Generate one! How to generate?

The execution plan is generated in the compilation phase. Three steps are required for compilation: Analysis and algebra. algebrization) and query optimization, without the query optimization process, we can solve the problem of low performance caused by the filtering of the first Cartesian set proposed by the above friend. Next I will introduce these three steps.

Step 1: analysis is the process of checking the syntax and converting SQL batch processing into an analysis tree., Such as select * t1 where id in (, 7) after the analysis, select * t1 where id = 1 or id = 2 or id = 3 or id = 4 or id = 5 or id = 6 or id = 7, in addition, you can check whether the syntax is correct.

Step 2: the next process is the algebraic algebrization ),In this phase, the new component algebrizer of SQL Server 2005 is used. The main function of the algebrizer component is binding. Therefore, the algebraic process is usually called binding. In this phase, the analysis tree in the first step is used as the input to generate the output called the query processor tree for query optimization. In fact, this stage mainly involves several things,

I. The operator is flat. In short, binary operators are combined into N-element operators, here we must provide an example to explain this binary conversion to N elements. As shown in step 1, The in Operation expands into a series of or operators, and the analyzer considers these or operations to be binary, that is to say, it thinks that the first or left child is id = 1, the right child is the expression id = 2 or id = 3 or id = 4 or id = 5 or id = 6 or id = 7), and the right child is considered binary, therefore, a recursive process is required. In the flat operation process, this binary operation is combined into an n-element operator to avoid recursion.

2. name resolution. This process is to check whether the table or table column in the query exists in the database. And whether it is visible during the Query Process. Iii. Type derivation is a bit abstract. For example, we can understand it. For example, for union queries, the data types at the corresponding locations of the query results on both sides of union should be consistent. 4. Aggregation binding and component binding. After performing this step, the query processor tree is generated.

Step 3: Query OptimizationThis process is completed by the query optimizer component. In the query, the order in which the table should be accessed, the method used, the index used, and the join algorithm are determined by the query optimizer component, however, this decision is not random. The prerequisite must be that the final result set must be correct, that is to say, the result set must be consistent with the result set obtained by each stage of logical processing. The optimizer will try many variants of the query to find the plan with the lowest cost.

If the optimizer analyzes the metadata of the query and learns that there is only one executable plan, it will no longer try to find a better plan. This step is called fine plan optimization. If no minor optimization plan is found, SQL Server will perform some simplification and simplification means to convert its own syntax, such as calculating the where filter of the table before joining, as described in the previous article, in a logical query, where filtering is always calculated after the join, but the correct result obtained after the where filter is first calculated during the join operation is often higher, therefore, in physical processing, where is often executed before join. The problem mentioned in the beginning is that the reader does not understand the difference between logical processing and physical processing.

So far, the physical processing steps have also been briefly described. In summary, both stored procedures and ad hoc queries are copies of a query plan executed, if this query plan does not exist, it must be compiled to generate an execution plan. During the compilation stage, it must be analyzed and bound to the algebra.) query optimization is performed to obtain the search results. I will introduce in detail how the Query Optimization Component optimizes the query processor tree in the future.

1. How to delete duplicate rows in an SQL Server table
2. Accelerate SQL Server running with parallel queries
3. SQL Server 2005 FAQs