SomewhereSomehow's SQL Server notes - A main
Request: use opt; go select * from t1 where t1.c in (select t2.b from t2 where t2.c = 10 and t2.b = t1.c); - 1. select * from t1 where t1.c in (select t2.b from t2 where t2.c = 10 and t1.c = t2.b); - 2.
At first glance, not even immediately clear what the difference between requests and between plans. Pay attention to the predicates: the first query is t2.b = t1.c, Second t1.c = t2.b. Ie we just swapped column in comparison.
Usually, even in non-declarative programming languages, it does not matter which side of the comparison operations are operands in a declarative language, it should not matter even more. However, ironically, it is the root cause of the declarative language differences in the plans. Creating a query, pam00423 we specify that we want to get, but how do we get it - the optimizer decides. Based on the query it builds the logical tree operators, simplifies it and generates a plan. It appears simplifying pam00423 step difference, which causes the difference in the planes.
For the first query tree is constructed logical operators, then begins the stage of simplification, which, among other actions, pam00423 there is a search and elimination of redundant conditions. If, for example, write where a = 1 and a = 1, the optimizer pam00423 will eliminate excessive one condition, it does not matter whether it is written where a = 1 and a = 1 or where a = 1 and 1 = a. However, the disclosure in subquery do not, apparently, did not foresee.
The same thing happens with the query: select * from t1 where t1.c = some (select t2.b from t2 where t2.c = 10 and t2.b = t1.c) select * from t1 where t1.c = some (select t2.b from t2 where t2.c = 10 and t1.c = t2.b)
In this simple example, there is no impact on performance or it is minimal, pam00423 but if you take a slightly different example, pam00423 which involved the exclusion of joins, the difference will be visible to the naked eye. DB used AdventureWorks2008. use AdventureWorks2008; go set showplan_xml on go select * from Sales.SalesOrderHeader soh where soh.CustomerID in (select sc.CustomerID from Sales.Customer sc where sc.CustomerID = soh.CustomerID) go select * from Sales.SalesOrderHeader soh where soh. CustomerID in (select sc.CustomerID from Sales.Customer sc where soh.CustomerID = sc.CustomerID) go set showplan_xml off go
Typically, optimizer pam00423 almost always the same building plan for both structures, and equally performs these queries. However, in our case, the plan will be different, because exists in the case of using the optimizer receives as input another tree operators to simplify, with no problems as described above.
Also, there is such a thing as a habit, accustomed to write in, very easy to write not in, when you need to verify that the item is not included in the set. In this formulation, the question not in and not exists begin to differ, if in a subquery can be null.
Archives December 2013 October 2013 July 2013 April 2013 March 2013 February 2013 December 2012 November 2012 October 2012 July 2012 May 2012 March 2012 February 2012 January 2012 October 2011 June 2011 March 2011 February 2011 January 2011 December 2010
8671 8780 8788 catch clustered index Code First columnstore copy csindex dbcc dbcc csindex Entity Framework estimates foreign key fragmentation geek good enough plan heap index merge monitoring pam00423 nested pam00423 loops optimizer order by parallel performance queryruleoff search0 search1 simplification skip trivial plan sort sp_trace sqlsaturday SqlServer SQL Server SSMS statistics timeout trigger trivial plan try undocumented view window functions
Request: use opt; go select * from t1 where t1.c in (select t2.b from t2 where t2.c = 10 and t2.b = t1.c); - 1. select * from t1 where t1.c in (select t2.b from t2 where t2.c = 10 and t1.c = t2.b); - 2.
At first glance, not even immediately clear what the difference between requests and between plans. Pay attention to the predicates: the first query is t2.b = t1.c, Second t1.c = t2.b. Ie we just swapped column in comparison.
Usually, even in non-declarative programming languages, it does not matter which side of the comparison operations are operands in a declarative language, it should not matter even more. However, ironically, it is the root cause of the declarative language differences in the plans. Creating a query, pam00423 we specify that we want to get, but how do we get it - the optimizer decides. Based on the query it builds the logical tree operators, simplifies it and generates a plan. It appears simplifying pam00423 step difference, which causes the difference in the planes.
For the first query tree is constructed logical operators, then begins the stage of simplification, which, among other actions, pam00423 there is a search and elimination of redundant conditions. If, for example, write where a = 1 and a = 1, the optimizer pam00423 will eliminate excessive one condition, it does not matter whether it is written where a = 1 and a = 1 or where a = 1 and 1 = a. However, the disclosure in subquery do not, apparently, did not foresee.
The same thing happens with the query: select * from t1 where t1.c = some (select t2.b from t2 where t2.c = 10 and t2.b = t1.c) select * from t1 where t1.c = some (select t2.b from t2 where t2.c = 10 and t1.c = t2.b)
In this simple example, there is no impact on performance or it is minimal, pam00423 but if you take a slightly different example, pam00423 which involved the exclusion of joins, the difference will be visible to the naked eye. DB used AdventureWorks2008. use AdventureWorks2008; go set showplan_xml on go select * from Sales.SalesOrderHeader soh where soh.CustomerID in (select sc.CustomerID from Sales.Customer sc where sc.CustomerID = soh.CustomerID) go select * from Sales.SalesOrderHeader soh where soh. CustomerID in (select sc.CustomerID from Sales.Customer sc where soh.CustomerID = sc.CustomerID) go set showplan_xml off go
Typically, optimizer pam00423 almost always the same building plan for both structures, and equally performs these queries. However, in our case, the plan will be different, because exists in the case of using the optimizer receives as input another tree operators to simplify, with no problems as described above.
Also, there is such a thing as a habit, accustomed to write in, very easy to write not in, when you need to verify that the item is not included in the set. In this formulation, the question not in and not exists begin to differ, if in a subquery can be null.
Archives December 2013 October 2013 July 2013 April 2013 March 2013 February 2013 December 2012 November 2012 October 2012 July 2012 May 2012 March 2012 February 2012 January 2012 October 2011 June 2011 March 2011 February 2011 January 2011 December 2010
8671 8780 8788 catch clustered index Code First columnstore copy csindex dbcc dbcc csindex Entity Framework estimates foreign key fragmentation geek good enough plan heap index merge monitoring pam00423 nested pam00423 loops optimizer order by parallel performance queryruleoff search0 search1 simplification skip trivial plan sort sp_trace sqlsaturday SqlServer SQL Server SSMS statistics timeout trigger trivial plan try undocumented view window functions
No comments:
Post a Comment