tag:blogger.com,1999:blog-3000721125799986885.post7257908219204623389..comments2024-03-29T04:14:18.488-07:00Comments on SELECT Blog FROM Brad.Schulz CROSS APPLY SQL.Server(): Query Optimizer: Genius or Bonehead?Brad Schulzhttp://www.blogger.com/profile/01852762873611487368noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-3000721125799986885.post-32382978661649025512023-04-28T08:59:23.610-07:002023-04-28T08:59:23.610-07:00IT companies can optimize the advantages of outsou...IT companies can optimize the advantages of outsourcing services by following certain best practices. The first step is to select a service provider with the appropriate skills and experience. It is also crucial to establish clear expectations and goals for the project, including timelines, deliverables, and quality standards. Successful outsourcing requires effective communication and collaboration, so regular check-ins and status updates with the service provider should be established. To learn more more <a href="https://mobilunity-bpo.com/technical-support-services/" rel="nofollow">helpful hints</a> visit the website by clicking on the provided link. <br />StevenGreenhttps://mobilunity-bpo.com/technical-support-services/noreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-50308633506630945242009-10-27T09:47:51.698-07:002009-10-27T09:47:51.698-07:00@Michael:
Nyuk-nyuk-nyuk.@Michael:<br /><br />Nyuk-nyuk-nyuk.Brad Schulzhttps://www.blogger.com/profile/01852762873611487368noreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-67030810793589737812009-10-27T09:47:22.787-07:002009-10-27T09:47:22.787-07:00@Adam:
Thanks for the link on foldable expression...@Adam:<br /><br />Thanks for the link on foldable expressions. I didn't realize there was documentation on it (or a term for it)... that's good to know. My "SELECT * vs SELECT 1" blog entry and "Down For the COUNT(*)" entry, as well as this one, had those foldable expressions in there (I especially like the SIN(PI()/2)=1 predicate).<br /><br />I'm glad you tested the queries out on your system as well. I'll bet that the reason Query #2 and #3 generated the same plan on your system (and not on mine) was because you did it on SQL2008 (which I don't currently have) and I did mine on SQL2005.<br /><br />I'll bet 2008 made an improvement there. I kind of suspect (though I searched the web and couldn't find confirmation on this) that adding a predicate of Table1.ColumnName = Table1.ColumnName to a query was an "old-fashioned" way of giving a hint to the optimizer that you wanted to "force" the selectivity of Table1 to be lower (i.e. 10%) in order to trick the optimizer into using Table1 as the driving table of the query. I wonder if this roundabout hinting approach was deprecated in SQL2008.<br /><br />I remember reading a book (I won't mention the title... but it was NOT one from Microsoft Press... in fact, I don't remember the exact title... I must have Wrox in my head) that mentioned "helping the query" by adding such a predicate to the WHERE clause, and I remember thinking that was nuts. Because it didn't help the query at all, but made it worse, as I demonstrated in this article.<br /><br />Thanks, as always, for the input. Much appreciated.Brad Schulzhttps://www.blogger.com/profile/01852762873611487368noreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-45106174610467845352009-10-27T04:45:46.899-07:002009-10-27T04:45:46.899-07:00Ha! Four Curly's was the bit for me!Ha! Four Curly's was the bit for me!Michael J. Swarthttps://www.blogger.com/profile/05408240220683534698noreply@blogger.comtag:blogger.com,1999:blog-3000721125799986885.post-71606029206409561282009-10-26T21:05:18.709-07:002009-10-26T21:05:18.709-07:00Great post Brad! I have a few comments, as usual ...Great post Brad! I have a few comments, as usual 8^). <br /><br />The optimizer is basing cardinality decisions off what it believes to be true, which will vary from system to system depending on the distribution of data and the validity of the stastics. On my box query 3 gave the same query plan as query 2. If you notice the optimizer is making bad decisions, it is usually a good indicator that you are missing indexes or statistics. <br /><br />One other key thing of note is that the optimizer typically evaluates the where clause in one of the last phases of query processing, which means the optimizer has to go through all the other stages to create the query plan. Typically, you cannot "short circuit" a query even if you validate/invalidate the where clause by setting all filters to true/false.... Now that is the typical scenario, but there is an exception. The exception is called a "foldable" expression, http://technet.microsoft.com/en-us/library/ms175933.aspx. Essentially the optimizer is able to validate constant deterministic expressions early in query plan generation to give a better query plan. A foldable expression can be used to short circuit a query to avoid work, as shown in the example below.<br /><br />select distinct<br /> CustomerID,<br /> ProductID<br />from<br /> AdventureWorks.Sales.SalesOrderDetail sod<br />join <br /> AdventureWorks.Sales.SalesOrderHeader soh<br /> on sod.SalesOrderID = soh.SalesOrderID<br />WHERE<br /> 1=2<br /> and SalesOrderDetailID = SalesOrderDetailID<br /> and ProductID = ProductID<br /> and SpecialOfferID = SpecialOfferID<br /> and OrderQty = OrderQty<br /> and UnitPrice = UnitPrice <br /><br />Again great post. Keep'em coming.Adam Haineshttps://www.blogger.com/profile/16288608920551626835noreply@blogger.com