SomewhereSomehow's SQL Server notes - A main
Recently, looking query plan, I noticed that in one branch plan tables are joined using Nested Loops Join (NL), although it would be logical to see there Merge Join (SM). I decided to find out why this is happening and came across an interesting watch opening tool feature of the optimizer. watch opening tool
use master; go if db_id ('opt') is not null drop database opt; go create database opt; go use opt; go create table t1 (a int primary key, b int not null, c int check (c between 1 and 50)); create table t2 (b int primary key, c int, d char (10)); create table t3 (c int primary key); go insert into t1 (a, b, c) select number, number% 100 +1, number% 50 +1 from master .. spt_values where type = 'p' and number between 1 and 1000; insert into t2 (b, c) select number, number% 100 +1 from master .. spt_values where type = 'p' watch opening tool and number between 1 and 1000; insert into t3 (c) select number from master .. spt_values where type = 'p' and number watch opening tool between 1 and 1000; go alter table t1 add constraint fk_t2_b foreign key (b) references watch opening tool t2 (b); create index ix_b on t1 (b asc) include (c); go
Selecting the type of connection, do the two queries and see their plans. select * from t1 join t2 on t1.b = t2.b where t1.b <= 36 order by t1.b, t2.b select * from t1 join t2 on t1.b = t2.b where t1.b <= 37 order by t1.b, t2.b go
Choosing the type of connection, and indeed, choosing which operator to use the optimizer based on the cost. Value is measured in some arbitrary units, which themselves do not have much meaning, but are used when comparing two options - Select the option whose value is less than, ie, cheaper option. One of the most important variables affecting the value of the number of rows. In this case, starting from a certain count of rows, it becomes more advantageous to use Nested Loops (NL) instead watch opening tool of Merge Join (SM). It is not surprising that the query below which selects only 10 lines, also used compound NL. select * from t1 join t2 on t1.b = t2.b where t1.b = 37 order by t1.b, t2.b
Now let's see how much more will the plan if using hints force the optimizer to use the Merge Join forcibly. select * from t1 join t2 on t1.b = t2.b where t1.b = 37 order by t1.b, t2.b option (merge join) go
Surprise. It turns out that in such a situation, the optimizer can not build a plan with the Merge Join. The same result if we add Hash Join. select * from t1 join t2 on t1.b = t2.b where t1.b = 37 order by t1.b, t2.b option (merge join, hash join) go
The situation becomes more interesting if you try to write the query differently. 1. / * 1. * / - ERROR select * from t1 join t2 on t1.b = t2.b where t1.b = 1 option (recompile, merge join, hash join); go - OK select * from t1 join t2 on t1.b = t2.b where t1.b> 0 and t1.b <2 option (recompile, merge join, hash join); go
The same result would be if rewrite the query in another way. / * 2. * / - ERROR select * from t1 join t2 on t1.b = t2.b where t1.b = 1 option (recompile, merge join, hash join); go - OK select * from t1 join t2 on t1.b +1 = t2.b +1 where t1.b = 1 option (recompile, merge join, hash join);
Another option. / * 3. * / - ERROR select * from t1 join t2 on t1.b = t2.b where t1.b in (1) option (recompile, merge join, hash join); go - OK select * from t1 join t2 on t1 . b = t2.b where t1.b in (1,2) option (recompile, merge join, hash join); go 4.
And another. / * 4. * / - ERROR select * from t1 join t2 on t1.a = t2.b and t1.b = t2.c where t1.b = 1 and t1.a = 100 option (recompile, merge join, hash join); go - OK select * from t1 join t2 on t1.a = t2.b and t1.b = t2.c where t1.b = 1 option (recompile, merge join, hash join); go 5.
The same problem in the query variable and recompile option. / * 5. * / - ERROR declare @ b int = 1; select * from t1 join t2 on t1.a = t2.b where t1.a = @ b or @ b is null option (recompile, merge join); go - OK declare @ b int = null; select * from t1 join t2 on t1.a = t2.b where t1.a = @ b or @ b is null option (recompile, merge join); go
You can think of a few more examples, but I think these are already enough. More interesting will understand what is happening, because all these examples hides the same manipulation optimizer, leading to similar results. Reason
Optimizer starts with the construction plan for constructing a tree of logical operators, which represents a request. After that, even before watch opening tool the start search options optimizer tries every way to simplify this tree. One of these simplifications is an attempt to filter out rows before the join operation. This is a logical move, because the more lines will be filtered to make the connection, the less will have to connect the lines and that will be faster.
You may notice that in the condition where the predicate appears with column t1.b. Verified t1.b = 1. The same column also appears in the limit
Recently, looking query plan, I noticed that in one branch plan tables are joined using Nested Loops Join (NL), although it would be logical to see there Merge Join (SM). I decided to find out why this is happening and came across an interesting watch opening tool feature of the optimizer. watch opening tool
use master; go if db_id ('opt') is not null drop database opt; go create database opt; go use opt; go create table t1 (a int primary key, b int not null, c int check (c between 1 and 50)); create table t2 (b int primary key, c int, d char (10)); create table t3 (c int primary key); go insert into t1 (a, b, c) select number, number% 100 +1, number% 50 +1 from master .. spt_values where type = 'p' and number between 1 and 1000; insert into t2 (b, c) select number, number% 100 +1 from master .. spt_values where type = 'p' watch opening tool and number between 1 and 1000; insert into t3 (c) select number from master .. spt_values where type = 'p' and number watch opening tool between 1 and 1000; go alter table t1 add constraint fk_t2_b foreign key (b) references watch opening tool t2 (b); create index ix_b on t1 (b asc) include (c); go
Selecting the type of connection, do the two queries and see their plans. select * from t1 join t2 on t1.b = t2.b where t1.b <= 36 order by t1.b, t2.b select * from t1 join t2 on t1.b = t2.b where t1.b <= 37 order by t1.b, t2.b go
Choosing the type of connection, and indeed, choosing which operator to use the optimizer based on the cost. Value is measured in some arbitrary units, which themselves do not have much meaning, but are used when comparing two options - Select the option whose value is less than, ie, cheaper option. One of the most important variables affecting the value of the number of rows. In this case, starting from a certain count of rows, it becomes more advantageous to use Nested Loops (NL) instead watch opening tool of Merge Join (SM). It is not surprising that the query below which selects only 10 lines, also used compound NL. select * from t1 join t2 on t1.b = t2.b where t1.b = 37 order by t1.b, t2.b
Now let's see how much more will the plan if using hints force the optimizer to use the Merge Join forcibly. select * from t1 join t2 on t1.b = t2.b where t1.b = 37 order by t1.b, t2.b option (merge join) go
Surprise. It turns out that in such a situation, the optimizer can not build a plan with the Merge Join. The same result if we add Hash Join. select * from t1 join t2 on t1.b = t2.b where t1.b = 37 order by t1.b, t2.b option (merge join, hash join) go
The situation becomes more interesting if you try to write the query differently. 1. / * 1. * / - ERROR select * from t1 join t2 on t1.b = t2.b where t1.b = 1 option (recompile, merge join, hash join); go - OK select * from t1 join t2 on t1.b = t2.b where t1.b> 0 and t1.b <2 option (recompile, merge join, hash join); go
The same result would be if rewrite the query in another way. / * 2. * / - ERROR select * from t1 join t2 on t1.b = t2.b where t1.b = 1 option (recompile, merge join, hash join); go - OK select * from t1 join t2 on t1.b +1 = t2.b +1 where t1.b = 1 option (recompile, merge join, hash join);
Another option. / * 3. * / - ERROR select * from t1 join t2 on t1.b = t2.b where t1.b in (1) option (recompile, merge join, hash join); go - OK select * from t1 join t2 on t1 . b = t2.b where t1.b in (1,2) option (recompile, merge join, hash join); go 4.
And another. / * 4. * / - ERROR select * from t1 join t2 on t1.a = t2.b and t1.b = t2.c where t1.b = 1 and t1.a = 100 option (recompile, merge join, hash join); go - OK select * from t1 join t2 on t1.a = t2.b and t1.b = t2.c where t1.b = 1 option (recompile, merge join, hash join); go 5.
The same problem in the query variable and recompile option. / * 5. * / - ERROR declare @ b int = 1; select * from t1 join t2 on t1.a = t2.b where t1.a = @ b or @ b is null option (recompile, merge join); go - OK declare @ b int = null; select * from t1 join t2 on t1.a = t2.b where t1.a = @ b or @ b is null option (recompile, merge join); go
You can think of a few more examples, but I think these are already enough. More interesting will understand what is happening, because all these examples hides the same manipulation optimizer, leading to similar results. Reason
Optimizer starts with the construction plan for constructing a tree of logical operators, which represents a request. After that, even before watch opening tool the start search options optimizer tries every way to simplify this tree. One of these simplifications is an attempt to filter out rows before the join operation. This is a logical move, because the more lines will be filtered to make the connection, the less will have to connect the lines and that will be faster.
You may notice that in the condition where the predicate appears with column t1.b. Verified t1.b = 1. The same column also appears in the limit
No comments:
Post a Comment