Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345

Statement evaluation precedence Expand / Collapse
Author
Message
Posted Tuesday, May 11, 2010 10:31 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 5:26 AM
Points: 1,120, Visits: 4,909
Thanks for the Q - I must admit I hadn't considxered this
Post #919844
Posted Thursday, June 3, 2010 9:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, January 27, 2014 10:14 AM
Points: 1,322, Visits: 1,091
calvo (5/7/2010)
So, now knowing how a query is processed internally, how can we use this knowledge to improve query performance or perhaps troubleshooting unexpected results?


I think there are two standout things to learn from this:
1. Since JOIN happens before WHERE a more complete ON clause may be more efficient as there will be fewer rows to filter out in the WHERE clause.

2. Since GROUP BY happens before SELECT and DISTINCT happens after SELECT. These:
a) SELECT DISTINCT A,B,C FROM TABLE
b) SELECT A,B,C FROM TABLE GROUP BY A,B,C

Are not necessarily going to have the same efficiency. This surprises me a little as my naive assumption would be that the optimizer would convert the DISTINCT into a GROUP BY.

--
JimFive
Post #932241
Posted Thursday, June 3, 2010 9:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:54 PM
Points: 6,130, Visits: 8,394
James Goodwin (6/3/2010)
calvo (5/7/2010)
So, now knowing how a query is processed internally, how can we use this knowledge to improve query performance or perhaps troubleshooting unexpected results?


I think there are two standout things to learn from this:
1. Since JOIN happens before WHERE a more complete ON clause may be more efficient as there will be fewer rows to filter out in the WHERE clause.


This is not true for an INNER JOIN. The placement of the predicate has no influence on the generated query plan and the efficiency. (Exception - for very complicated many-table joins with little rows, the optimizer might stop searching for a better plan if it estimates that the cost of finding a better plan exceeds the cost saved by that plan; in that case the placement might affect performance, and that might happen in both ways. This is rare, though).

2. Since GROUP BY happens before SELECT and DISTINCT happens after SELECT. These:
a) SELECT DISTINCT A,B,C FROM TABLE
b) SELECT A,B,C FROM TABLE GROUP BY A,B,C

Are not necessarily going to have the same efficiency. This surprises me a little as my naive assumption would be that the optimizer would convert the DISTINCT into a GROUP BY.

Again, incorrect. These will have the same plan, as that conversion is exactly what the optimizer does.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #932251
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse