none
Apply operator and filter predicates in Joins

    Question

  • Hi,

    SELECT
     c.ID , cn.FirstName , cn.Lastname
    FROM    Customers AS c  
            -- CROSS APPLY fn_NameGet(c.ID, NULL, NULL) AS cn
            LEFT JOIN View_CustomerBalance AS cb ON ( cbf.ID = c.ID )
            -- CROSS APPLY fn_NameGet(c.ID, NULL, NULL) AS cn
    WHERE ROUND(cb.Points,0,1) = 100

    When Apply occurs to the left of the join that supports the filter predicate the filter operation occurs to the Right of the Apply operator and takes a long time.

    When Apply occurs to the right of the join  that supports the filter predicate the filter operation occurs to the LEFT of the Apply operator and takes a much shorter time.

    I know what is happening, I don't know why. Nothing I've read in BOL about Apply explains this behavior. I hope someone smarter then me (and that is a very large set) can explain this behavior.

    SQL Server 2008 R2

    Thanks!


    Robert J. Cantor DBA

    Saturday, December 29, 2012 12:40 AM

Answers

  • Hi Erland,

    Per Naomi, I applied sp2 to the dev environment I was doing all my testing in. It appears the processing of the slower query is faster.

    Same syntax pre sp patch:

    SQL Server Execution Times:
       CPU time = 1800110 ms,  elapsed time = 3145197 ms.

    Same syntax after:

     SQL Server Execution Times:

       CPU time = 1565484 ms,  elapsed time = 2075351 ms.

    This is with the tvf to the right of the filter operator in the execution plan. So while sp 2 does appear to be faster (good call) the same anomalous behavior occurs. I wrote a simpler version of the tvf and the same behavior in the both the compile time and run time execution plans occurs. I think this behavior should be included in MS documentation.


    Robert J. Cantor DBA

    • Marked as answer by Robert_Cantor Thursday, January 03, 2013 8:41 PM
    Thursday, January 03, 2013 7:20 PM

All replies

  • Can you post  fn_NameGet & DDL?  Thanks.

    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Saturday, December 29, 2012 12:47 AM
    Moderator
  • Hi Kalman,

    I don't think I can, fn_NameGet is proprietary. I can mock up some pseudo code.

    But, fn_NameGet does create a #temp table of Customer name attributes based on the c.ID.


    Robert J. Cantor DBA

    Saturday, December 29, 2012 1:06 AM
  • Hi Robert,

    We would need something we can work with. I am sure you are not looking for philosophical opinions?  Can you upload the XML execution plans?


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Saturday, December 29, 2012 1:10 AM
    Moderator
  • point well taken! I will do so on Monday! Also, I am very appreciative of well thought out philosophical opinions!

    Robert J. Cantor DBA

    Saturday, December 29, 2012 3:51 AM
  • This is an aside, and no help to your question, but your WHERE clause is effectively making your LEFT JOIN an INNER JOIN.  To see why, consider that a LEFT JOIN has all the rows that an INNER JOIN would plus for every row in Customers that has no matching row in CustomerBalance, that Customers row is kept with all NULLs in the CustomerBalance columns.  So now we have the rows that we would have from an INNER JOIN plus these extra rows.  Then, after the LEFT JOIN, you are doing WHERE ROUND(cb.Points,0,1) = 100.  But for all of those extra rows cb.Points is NULL, so the WHERE condition is not true for any of those extra rows, so they are removed from the result set.  Thus you are left with only rows that would have been returned by an INNER JOIN.

    If you really want all customers that have one or more rows in CustomerBalance with ROUND(cb.Points,0,1) = 100 plus the rows in Customers that don't have any matching rows in CustomerBalance with ROUND(cb.Points,0,1) = 100, then you code should be

    SELECT
     c.ID , cn.FirstName , cn.Lastname
    FROM    Customers AS c  
            -- CROSS APPLY fn_NameGet(c.ID, NULL, NULL) AS cn
            LEFT JOIN View_CustomerBalance AS cb ON ( cbf.ID = c.ID AND ROUND(cb.Points,0,1) = 100)
            -- CROSS APPLY fn_NameGet(c.ID, NULL, NULL) AS cn
    

    Tom

    Saturday, December 29, 2012 6:00 AM
  • If I understand your question, you get different execution plans depending on how you write the query, but the result is the same.

    What Books Online describes is mainly the logical execution order, and the physical execution order may be different. As far as I can see, the two queries are equivalent, but maybe the optimizer fails to do a transformation why you get different plans depending on how you write the query.

    But as Tom points out, the query is not well written. Either you should have an inner join, or what's in the WHERE condition should be in the ON clause.

    By the way, I notice that there is an alias cbf in the ON clause that is not defined in the query. This makes me suspect that is not the actual query, but a mockup. And in such case, all bets are off...


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, December 29, 2012 11:05 AM
  • Hi

    "When Apply occurs to the left of the join that supports the filter predicate the filter operation occurs to the Right of the Apply operator and takes a long time"

    "When Apply occurs to theright of the join  that supports the filter predicate the filter operation occurs to the LEFT of the Apply operator and takes a much shorter time."

    Because:

    1. Tables which are involved in the left join have a one to many relation that means it increases my row count.

    2. Select statement in which you use (with a PK) inner Join a table inner join a View_CustomerBalance view inner join a table left join a table left join a table.

    See details using execution plans.

    Simulate INNER JOIN using LEFT JOIN statement – Performance Analysis

    http://blog.sqlauthority.com/2008/10/25/sql-server-simulate-inner-join-using-left-join-statement-performance-analysis/


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Saturday, December 29, 2012 12:08 PM
  • Thanks Tom. This is pseudo code. While a valid observation, as you note, does not inform why are different query plans produced based on the relative position of the Apply operator to the join referenced by the filter predicate.

    Robert J. Cantor DBA

    Sunday, December 30, 2012 1:01 AM
  • 'get different execution plans depending on how you write the query, but the result is the same. ' Correct.

    'the optimizer fails to do a transformation [which is] why you get different plans depending on how you write the query.' Correct. The question is why? Is the answer 'because'? As in because the optimizer is not 'smart' enough to figure it out? If so the documentation for the Apply operator should be updated to reflect this behavior.


    Robert J. Cantor DBA

    Sunday, December 30, 2012 1:08 AM
  • What is your exact SQL Server version and are you able to post execution plans? In your case the table function is involved which may confuse the optimizer. I guess if you move this function down, the JOIN filters rows earlier and therefore there is less rows to execute the function against.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Sunday, December 30, 2012 1:11 AM
    Moderator
  • 1) If you want helpful answers about why a query behaves certain way, you need to post the actual query text, not pseudo-code. When composing the pseudo-code you may inadvertenly left out critical clues that would answer the question. Note that just because the queries produces the same result with the data you have at hand, the queries may not be logically equivalent.

    2) As I pointed out, Books Online documents the logical behaviour of the operators. It makes no committments on what shape you can expect of the query plans, nor does it discuss what transformations the engine performs and which it does not. Nor is this material that belongs in Books Online in my opinion - that would be going into a level of detail that goes beyond what most users are able to grasp. If you want details of the inner workings, I recommend that you read the blogs from people like Craig Friedman, Conor Cunningham and Paul White - there is a wealth of material there.

    To the first point I should add that if you want to protect intellectual property, it is OK if you replace table and columns names, as long as you do it consistently. Keep in mind that in the next step we may ask for table and index definitions as well as the actual query plans, so may have to run that find/replace multiple times.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, December 30, 2012 10:49 AM
  • Problem adding predicates to outer join http://stackoverflow.com/questions/4404140/problem-adding-predicates-to-outer-join
    Sunday, December 30, 2012 5:40 PM
  • <?xml version="1.0" encoding="utf-16"?>
    <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.1617.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
      <BatchSequence>
        <Batch>
          <Statements>
            <StmtSimple StatementCompId="1" StatementEstRows="7514.53" StatementId="2060531" StatementOptmLevel="FULL" StatementSubTreeCost="6.04315" StatementText="SELECT &#xD;&#xA;        &quot;ID&quot; = p.PlayerID ,&#xD;&#xA;        'First Name' = pn.FirstName ,&#xD;&#xA;        'Last Name' = pn.LastName &#xD;&#xA;FROM    Player AS p &#xD;&#xA;        CROSS APPLY fn_PlayerNameGet(p.PlayerID, NULL, NULL) AS pn&#xD;&#xA;        LEFT JOIN View_PlayerBalance AS pbf WITH ( NOLOCK ) ON ( pbf.PlayerID = p.PlayerID ) &#xD;&#xA;        --CROSS APPLY fn_PlayerNameGet(p.PlayerID, NULL, NULL) AS pn&#xD;&#xA;WHERE 1=1&#xD;&#xA;      and ROUND(pbf.SlotPoints,0,1) = 100&#xD;&#xA;" StatementType="SELECT" QueryHash="0x5FCDD8285B65CBF6" QueryPlanHash="0xDF73D8EBE323E876">
              <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
              <QueryPlan DegreeOfParallelism="0" MemoryGrant="28040" CachedPlanSize="48" CompileTime="17" CompileCPU="7" CompileMemory="376">
                <RelOp AvgRowSize="97" EstimateCPU="0.114806" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="7514.53" LogicalOp="Filter" NodeId="0" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="6.04315">
                  <OutputList>
                    <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[Player]" Alias="[p]" Column="PlayerID" />
                    <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[fn_PlayerNameGet]" Alias="[pn]" Column="FirstName" />
                    <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[fn_PlayerNameGet]" Alias="[pn]" Column="LastName" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="43" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <Filter StartupExpression="false">
                    <RelOp AvgRowSize="101" EstimateCPU="2.51509" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="147187" LogicalOp="Right Outer Join" NodeId="1" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="5.92834">
                      <OutputList>
                        <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[Player]" Alias="[p]" Column="PlayerID" />
                        <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[fn_PlayerNameGet]" Alias="[pn]" Column="FirstName" />
                        <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[fn_PlayerNameGet]" Alias="[pn]" Column="LastName" />
                        <ColumnReference Column="Expr1006" />
                      </OutputList>
                      <MemoryFractions Input="1" Output="1" />
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="147187" ActualEndOfScans="1" ActualExecutions="1" />
                      </RunTimeInformation>
                      <Hash>
                        <DefinedValues />
                        <HashKeysBuild>
                          <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[PlayerBalance]" Column="PlayerID" />
                        </HashKeysBuild>
                        <HashKeysProbe>
                          <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[Player]" Alias="[p]" Column="PlayerID" />
                        </HashKeysProbe>
                        <RelOp AvgRowSize="15" EstimateCPU="0.168172" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="147138" LogicalOp="Aggregate" NodeId="2" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="2.33378">
                          <OutputList>
                            <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[PlayerBalance]" Column="PlayerID" />
                            <ColumnReference Column="Expr1006" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="147138" ActualEndOfScans="1" ActualExecutions="1" />
                          </RunTimeInformation>
                          <StreamAggregate>
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Column="Expr1006" />
                                <ScalarOperator ScalarString="SUM([PlayerManagement].[dbo].[PlayerBalance].[SlotPoints])">
                                  <Aggregate AggType="SUM" Distinct="false">
                                    <ScalarOperator>
                                      <Identifier>
                                        <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[PlayerBalance]" Column="SlotPoints" />
                                      </Identifier>
                                    </ScalarOperator>
                                  </Aggregate>
                                </ScalarOperator>
                              </DefinedValue>
                            </DefinedValues>
                            <GroupBy>
                              <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[PlayerBalance]" Column="PlayerID" />
                            </GroupBy>
                            <RelOp AvgRowSize="15" EstimateCPU="0.173596" EstimateIO="1.99201" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="157672" LogicalOp="Clustered Index Scan" NodeId="3" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="2.16561" TableCardinality="157672">
                              <OutputList>
                                <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[PlayerBalance]" Column="PlayerID" />
                                <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[PlayerBalance]" Column="SlotPoints" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="0" ActualRows="157672" ActualEndOfScans="1" ActualExecutions="1" />
                              </RunTimeInformation>
                              <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[PlayerBalance]" Column="PlayerID" />
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[PlayerBalance]" Column="SlotPoints" />
                                  </DefinedValue>
                                </DefinedValues>
                                <Object Database="[PlayerManagement]" Schema="[dbo]" Table="[PlayerBalance]" Index="[PK_PlayerBalance]" IndexKind="Clustered" />
                              </IndexScan>
                            </RelOp>
                          </StreamAggregate>
                        </RelOp>
                        <RelOp AvgRowSize="97" EstimateCPU="0.615242" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="147187" LogicalOp="Inner Join" NodeId="7" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.07947">
                          <OutputList>
                            <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[Player]" Alias="[p]" Column="PlayerID" />
                            <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[fn_PlayerNameGet]" Alias="[pn]" Column="FirstName" />
                            <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[fn_PlayerNameGet]" Alias="[pn]" Column="LastName" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="147187" ActualEndOfScans="1" ActualExecutions="1" />
                          </RunTimeInformation>
                          <NestedLoops Optimized="false">
                            <OuterReferences>
                              <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[Player]" Alias="[p]" Column="PlayerID" />
                            </OuterReferences>
                            <RelOp AvgRowSize="11" EstimateCPU="0.162063" EstimateIO="0.154977" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="147187" LogicalOp="Index Scan" NodeId="8" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.31704" TableCardinality="147187">
                              <OutputList>
                                <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[Player]" Alias="[p]" Column="PlayerID" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="0" ActualRows="147187" ActualEndOfScans="1" ActualExecutions="1" />
                              </RunTimeInformation>
                              <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[Player]" Alias="[p]" Column="PlayerID" />
                                  </DefinedValue>
                                </DefinedValues>
                                <Object Database="[PlayerManagement]" Schema="[dbo]" Table="[Player]" Index="[idx_Player_SSN]" Alias="[p]" IndexKind="NonClustered" />
                              </IndexScan>
                            </RelOp>
                            <RelOp AvgRowSize="93" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="147186" EstimateRewinds="0" EstimateRows="1" LogicalOp="Table-valued function" NodeId="9" Parallel="false" PhysicalOp="Table-valued function" EstimatedTotalSubtreeCost="0.147187">
                              <OutputList>
                                <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[fn_PlayerNameGet]" Alias="[pn]" Column="FirstName" />
                                <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[fn_PlayerNameGet]" Alias="[pn]" Column="LastName" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="0" ActualRebinds="147187" ActualRewinds="0" ActualRows="147187" ActualEndOfScans="147187" ActualExecutions="147187" />
                              </RunTimeInformation>
                              <TableValuedFunction>
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[fn_PlayerNameGet]" Alias="[pn]" Column="FirstName" />
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[fn_PlayerNameGet]" Alias="[pn]" Column="LastName" />
                                  </DefinedValue>
                                </DefinedValues>
                                <Object Database="[PlayerManagement]" Schema="[dbo]" Table="[fn_PlayerNameGet]" Alias="[pn]" />
                                <ParameterList>
                                  <ScalarOperator ScalarString="[PlayerManagement].[dbo].[Player].[PlayerID] as [p].[PlayerID]">
                                    <Identifier>
                                      <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[Player]" Alias="[p]" Column="PlayerID" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="NULL">
                                    <Const ConstValue="NULL" />
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="NULL">
                                    <Const ConstValue="NULL" />
                                  </ScalarOperator>
                                </ParameterList>
                              </TableValuedFunction>
                            </RelOp>
                          </NestedLoops>
                        </RelOp>
                      </Hash>
                    </RelOp>
                    <Predicate>
                      <ScalarOperator ScalarString="round([Expr1006],(0),(1))=(100)">
                        <Compare CompareOp="EQ">
                          <ScalarOperator>
                            <Intrinsic FunctionName="round">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Column="Expr1006" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Const ConstValue="(0)" />
                              </ScalarOperator>
                              <ScalarOperator>
                                <Const ConstValue="(1)" />
                              </ScalarOperator>
                            </Intrinsic>
                          </ScalarOperator>
                          <ScalarOperator>
                            <Const ConstValue="(100)" />
                          </ScalarOperator>
                        </Compare>
                      </ScalarOperator>
                    </Predicate>
                  </Filter>
                </RelOp>
              </QueryPlan>
            </StmtSimple>
          </Statements>
        </Batch>
      </BatchSequence>
    </ShowPlanXML>


    Robert J. Cantor DBA

    Monday, December 31, 2012 10:52 PM
  • <?xml version="1.0" encoding="utf-16"?>
    <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.1617.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
      <BatchSequence>
        <Batch>
          <Statements>
            <StmtSimple StatementCompId="1" StatementEstRows="7514.53" StatementId="603" StatementOptmLevel="FULL" StatementSubTreeCost="5.04472" StatementText="SELECT &#xD;&#xA;        &quot;ID&quot; = p.PlayerID ,&#xD;&#xA;        'First Name' = pn.FirstName ,&#xD;&#xA;        'Last Name' = pn.LastName &#xD;&#xA;FROM    Player AS p &#xD;&#xA;        --CROSS APPLY fn_PlayerNameGet(p.PlayerID, NULL, NULL) AS pn&#xD;&#xA;        LEFT JOIN View_PlayerBalance AS pbf WITH ( NOLOCK ) ON ( pbf.PlayerID = p.PlayerID ) &#xD;&#xA;        CROSS APPLY fn_PlayerNameGet(p.PlayerID, NULL, NULL) AS pn&#xD;&#xA;WHERE 1=1&#xD;&#xA;      and ROUND(pbf.SlotPoints,0,1) = 100&#xD;&#xA;" StatementType="SELECT" QueryHash="0x78B6C0179BE1A58A" QueryPlanHash="0x5C58156BE604DE15">
              <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
              <QueryPlan DegreeOfParallelism="0" MemoryGrant="26784" CachedPlanSize="48" CompileTime="5" CompileCPU="5" CompileMemory="368">
                <RelOp AvgRowSize="97" EstimateCPU="0.0314108" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="7514.53" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="5.04472">
                  <OutputList>
                    <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[Player]" Alias="[p]" Column="PlayerID" />
                    <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[fn_PlayerNameGet]" Alias="[pn]" Column="FirstName" />
                    <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[fn_PlayerNameGet]" Alias="[pn]" Column="LastName" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="43" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <NestedLoops Optimized="false">
                    <OuterReferences>
                      <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[Player]" Alias="[p]" Column="PlayerID" />
                    </OuterReferences>
                    <RelOp AvgRowSize="11" EstimateCPU="0.114806" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="7514.53" LogicalOp="Filter" NodeId="1" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="5.0058">
                      <OutputList>
                        <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[Player]" Alias="[p]" Column="PlayerID" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="43" ActualEndOfScans="1" ActualExecutions="1" />
                      </RunTimeInformation>
                      <Filter StartupExpression="false">
                        <RelOp AvgRowSize="15" EstimateCPU="2.24017" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="147187" LogicalOp="Left Outer Join" NodeId="2" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="4.89099">
                          <OutputList>
                            <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[Player]" Alias="[p]" Column="PlayerID" />
                            <ColumnReference Column="Expr1005" />
                          </OutputList>
                          <MemoryFractions Input="1" Output="1" />
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="147187" ActualEndOfScans="1" ActualExecutions="1" />
                          </RunTimeInformation>
                          <Hash>
                            <DefinedValues />
                            <HashKeysBuild>
                              <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[Player]" Alias="[p]" Column="PlayerID" />
                            </HashKeysBuild>
                            <HashKeysProbe>
                              <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[PlayerBalance]" Column="PlayerID" />
                            </HashKeysProbe>
                            <RelOp AvgRowSize="11" EstimateCPU="0.162063" EstimateIO="0.154977" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="147187" LogicalOp="Index Scan" NodeId="3" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.31704" TableCardinality="147187">
                              <OutputList>
                                <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[Player]" Alias="[p]" Column="PlayerID" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="0" ActualRows="147187" ActualEndOfScans="1" ActualExecutions="1" />
                              </RunTimeInformation>
                              <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[Player]" Alias="[p]" Column="PlayerID" />
                                  </DefinedValue>
                                </DefinedValues>
                                <Object Database="[PlayerManagement]" Schema="[dbo]" Table="[Player]" Index="[idx_Player_SSN]" Alias="[p]" IndexKind="NonClustered" />
                              </IndexScan>
                            </RelOp>
                            <RelOp AvgRowSize="15" EstimateCPU="0.168172" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="147138" LogicalOp="Aggregate" NodeId="4" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="2.33378">
                              <OutputList>
                                <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[PlayerBalance]" Column="PlayerID" />
                                <ColumnReference Column="Expr1005" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="0" ActualRows="147138" ActualEndOfScans="1" ActualExecutions="1" />
                              </RunTimeInformation>
                              <StreamAggregate>
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Column="Expr1005" />
                                    <ScalarOperator ScalarString="SUM([PlayerManagement].[dbo].[PlayerBalance].[SlotPoints])">
                                      <Aggregate AggType="SUM" Distinct="false">
                                        <ScalarOperator>
                                          <Identifier>
                                            <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[PlayerBalance]" Column="SlotPoints" />
                                          </Identifier>
                                        </ScalarOperator>
                                      </Aggregate>
                                    </ScalarOperator>
                                  </DefinedValue>
                                </DefinedValues>
                                <GroupBy>
                                  <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[PlayerBalance]" Column="PlayerID" />
                                </GroupBy>
                                <RelOp AvgRowSize="15" EstimateCPU="0.173596" EstimateIO="1.99201" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="157672" LogicalOp="Clustered Index Scan" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="2.16561" TableCardinality="157672">
                                  <OutputList>
                                    <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[PlayerBalance]" Column="PlayerID" />
                                    <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[PlayerBalance]" Column="SlotPoints" />
                                  </OutputList>
                                  <RunTimeInformation>
                                    <RunTimeCountersPerThread Thread="0" ActualRows="157672" ActualEndOfScans="1" ActualExecutions="1" />
                                  </RunTimeInformation>
                                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[PlayerBalance]" Column="PlayerID" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[PlayerBalance]" Column="SlotPoints" />
                                      </DefinedValue>
                                    </DefinedValues>
                                    <Object Database="[PlayerManagement]" Schema="[dbo]" Table="[PlayerBalance]" Index="[PK_PlayerBalance]" IndexKind="Clustered" />
                                  </IndexScan>
                                </RelOp>
                              </StreamAggregate>
                            </RelOp>
                          </Hash>
                        </RelOp>
                        <Predicate>
                          <ScalarOperator ScalarString="round([Expr1005],(0),(1))=(100)">
                            <Compare CompareOp="EQ">
                              <ScalarOperator>
                                <Intrinsic FunctionName="round">
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Column="Expr1005" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Const ConstValue="(0)" />
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Const ConstValue="(1)" />
                                  </ScalarOperator>
                                </Intrinsic>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Const ConstValue="(100)" />
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                        </Predicate>
                      </Filter>
                    </RelOp>
                    <RelOp AvgRowSize="93" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="7513.53" EstimateRewinds="0" EstimateRows="1" LogicalOp="Table-valued function" NodeId="11" Parallel="false" PhysicalOp="Table-valued function" EstimatedTotalSubtreeCost="0.00751469">
                      <OutputList>
                        <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[fn_PlayerNameGet]" Alias="[pn]" Column="FirstName" />
                        <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[fn_PlayerNameGet]" Alias="[pn]" Column="LastName" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRebinds="43" ActualRewinds="0" ActualRows="43" ActualEndOfScans="43" ActualExecutions="43" />
                      </RunTimeInformation>
                      <TableValuedFunction>
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[fn_PlayerNameGet]" Alias="[pn]" Column="FirstName" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[fn_PlayerNameGet]" Alias="[pn]" Column="LastName" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[PlayerManagement]" Schema="[dbo]" Table="[fn_PlayerNameGet]" Alias="[pn]" />
                        <ParameterList>
                          <ScalarOperator ScalarString="[PlayerManagement].[dbo].[Player].[PlayerID] as [p].[PlayerID]">
                            <Identifier>
                              <ColumnReference Database="[PlayerManagement]" Schema="[dbo]" Table="[Player]" Alias="[p]" Column="PlayerID" />
                            </Identifier>
                          </ScalarOperator>
                          <ScalarOperator ScalarString="NULL">
                            <Const ConstValue="NULL" />
                          </ScalarOperator>
                          <ScalarOperator ScalarString="NULL">
                            <Const ConstValue="NULL" />
                          </ScalarOperator>
                        </ParameterList>
                      </TableValuedFunction>
                    </RelOp>
                  </NestedLoops>
                </RelOp>
              </QueryPlan>
            </StmtSimple>
          </Statements>
        </Batch>
      </BatchSequence>
    </ShowPlanXML>


    Robert J. Cantor DBA

    Monday, December 31, 2012 10:53 PM
  • ProductVersion  10.50.1617.0   
    ProductLevel  RTM   
    Edition Enterprise Edition (64-bit)   
    EngineEdition  3 

    When the TVF occurs to the right side of the filter predicate:
     SQL Server Execution Times:
       CPU time = 1800110 ms,  elapsed time = 3145197 ms.

    When the TVF occurs to the left side of the filter predicate:
       SQL Server Execution Times:
       CPU time = 78 ms,  elapsed time = 118 ms.


    Robert J. Cantor DBA

    Monday, December 31, 2012 10:57 PM
  • Hi Kalman,
    I posted the 2 execution plans - slow and fast and server info and cpu time for both the slow and fast plan.

    Again, what determines the 'slow' or 'fast' plan is the position of the TVF in the From clause.

    Usual disclaimer: I did write this code, I inherited it.


    Robert J. Cantor DBA

    Monday, December 31, 2012 11:00 PM
  • Hi Naomi,
    I posted the 2 execution plans - slow and fast and server info and cpu time for both the slow and fast plan.

    Again, what determines the 'slow' or 'fast' plan is the position of the TVF in the From clause.

    Usual disclaimer: I did write this code, I inherited it.


    Robert J. Cantor DBA

    Monday, December 31, 2012 11:01 PM
  • Hi Erland,
    I posted the 2 execution plans - slow and fast and server info and cpu time for both the slow and fast plan.

    Again, what determines the 'slow' or 'fast' plan is the position of the TVF in the From clause.

    Usual disclaimer: I did write this code, I inherited it.


    Robert J. Cantor DBA

    Monday, December 31, 2012 11:01 PM
  • You should not use RTM version. You need to install the latest SP for your SQL Server and then re-test. With SQL Server you always need to be on the latest SP.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, December 31, 2012 11:09 PM
    Moderator
  • so you think this behavior is a bug fixed by a SP?

    Robert J. Cantor DBA

    Monday, December 31, 2012 11:12 PM
  • It may be. I suggest to first install the latest SP, re-test and let us know if the problem is still present.

    BTW, in your 'disclaimer' you said that you wrote the code and inherited it. I suspect you meant that you didn't write that code.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Monday, December 31, 2012 11:29 PM
    Moderator
  • so you think this behavior is a bug fixed by a SP?

    My gut feeling is that this is not a bug, but a limitation(*). However, applying the latest service pack is always a good idea. If this would happen to be something that is fixed, you would also need to enable trace flag 4199, as Microsoft only enables optimizer fixes for bugs that can yield incorrect result. Fixes of poor plan choices are only enabled with the trace flag to prevent plan regressions.

    As Tom pointed out, the query is better written as an inner join, and I can see in the plans that the optimizer is not making this shortcut - it still handles the join to PlayerBalance as an outer join. I would expect that if you make it an inner join, the optimizer would push the filter to right after the Stream Aggregate. This may or may not help. The limitation may also be due to the appearance of the UDF. Which, judging from the name, could be replaced with a single join or so.

    Also, the query could benefit from a non-clustered index on PlayerBalance (PlayerID) INCLUDE (SlotPoints).


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, December 31, 2012 11:56 PM
  • Hi Naomi, I applied sp2 to the dev environment I was doing all my testing in. It appears the processing of the slower query is faster.

    Same syntax pre sp patch:

    SQL Server Execution Times:
       CPU time = 1800110 ms,  elapsed time = 3145197 ms.

    Same syntax after:

     SQL Server Execution Times:

       CPU time = 1565484 ms,  elapsed time = 2075351 ms.

    This is with the tvf to the right of the filter operator in the execution plan. So while sp 2 does appear to be more efficient (good call) the same anomalous behavior occurs. I wrote a simpler version of the tvf and the same behavior in the both the compile time and run time execution plans occurs. I think this behavior should be included in MS documentation.

    btw, thanks for proofing my work.


    Robert J. Cantor DBA

    Thursday, January 03, 2013 7:19 PM
  • Hi Erland,

    Per Naomi, I applied sp2 to the dev environment I was doing all my testing in. It appears the processing of the slower query is faster.

    Same syntax pre sp patch:

    SQL Server Execution Times:
       CPU time = 1800110 ms,  elapsed time = 3145197 ms.

    Same syntax after:

     SQL Server Execution Times:

       CPU time = 1565484 ms,  elapsed time = 2075351 ms.

    This is with the tvf to the right of the filter operator in the execution plan. So while sp 2 does appear to be faster (good call) the same anomalous behavior occurs. I wrote a simpler version of the tvf and the same behavior in the both the compile time and run time execution plans occurs. I think this behavior should be included in MS documentation.


    Robert J. Cantor DBA

    • Marked as answer by Robert_Cantor Thursday, January 03, 2013 8:41 PM
    Thursday, January 03, 2013 7:20 PM
  • If you have the same problem after applying the latest SP and don't want to share TVP information in the forum, I think you may want to open a case with MS as it may turn to be a bug.

    I hope Erland will also be able to review the plans and make suggestions based on that.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, January 03, 2013 7:26 PM
    Moderator
  • Thanks Naomi,

    This is not a production issue since there is a work around. I will try to come up with a generic example that illustrates the issue. The behavior is undocumented and reproducible so opening a case with MS may be a good idea.

    Thanks again!


    Robert J. Cantor DBA

    Thursday, January 03, 2013 9:34 PM
  • Per Naomi, I applied sp2 to the dev environment I was doing all my testing in. It appears the processing of the slower query is faster.

    Did you enable trace flag 4199 when you tested?

    Also, did you try the variations of the query I suggested?

    This is with the tvf to the right of the filter operator in the execution plan. So while sp 2 does appear to be faster (good call) the same anomalous behavior occurs. I wrote a simpler version of the tvf and the same behavior in the both the compile time and run time execution plans occurs. I think this behavior should be included in MS documentation.

    As I said before, I don't think that this fits into the documentation. BOL is overall not very detailed from what you can expect from the optimizer. And it is my understanding that this is by intention, as Microsoft reserves the right to constantly enhance the optimizer.

    I again like to stress that the query you have is not particularly well-written. If you get get the good plan with an inner join no matter where you have the CROSS APPLY, I would consider it case closed.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, January 03, 2013 11:10 PM
  • Hi Erland,

    The same anomalous behavior occurs with this query:

     SELECT 
            "ID" = c.ID ,
            'First Name' = pn.FirstName ,
            'Last Name' = pn.LastName 

    FROM    PM.Customer AS c WITH ( NOLOCK )  
            --CROSS APPLY pm.[fn_PlayerNameHash](c.ID) AS pn
            LEFT JOIN  PM.View_CustomerBalance AS cbf WITH ( NOLOCK ) ON ( cbf.ID =c.ID ) 
            LEFT JOIN PM.CustomerYN cyn on cyn.id=c.ID
            CROSS APPLY pm.[fn_PlayerNameHash](c.ID) AS pn

    WHERE ROUND(cbf.Points,0,1) > 100
    or cyn.yn=0
    go

    A different query plan is produced depending on the relative position of the Apply operator. A limitation or a bug, it should be acknowledged.

    if you'd like I'll send you all the code to reproduce if you can provide me with an e-mail or an ftp sight. I can post all the scripts but then you would need to populate the customer's table with names. The other values can be created on the fly.


                    

    Robert J. Cantor DBA

    Saturday, January 05, 2013 12:12 AM
  • I was able to reproduce the behaviour. Turns out that it is a combination of factors, where the most important is the use of round and the incorrect use of LEFT JOIN.

    If you take out round of the query, you will find that you get the same plan in both cases. You will also see that the join operators reads Inner Join. Now they read Left/Right Join. For some reason unknown to me, the use of round() prevents the optimizer from rewriting the left join to an inner join.

    One could argue that even in this case, the optimizer should do it right, but it doesn't. And it could be because that such a transformation is not considered safe.

    Be that as it may. round() obviously fills a function in these queries, but the use of LEFT JOIN instead of INNER JOIN does not. Tom pointed out this in his first post, and I have kept repeatedly suggested that you should try with an inner join, but you have flatly ignored that.

    Here is my repro (it requires the Northwind database):

    CREATE FUNCTION dbo.funkis (@custid nchar(5))
    RETURNS @k TABLE (custname nvarchar(40) NOT NULL) AS
    BEGIN
       INSERT @k (custname)
          SELECT CompanyName FROM Customers WHERE CustomerID = @custid
       RETURN
    END
    go
    CREATE VIEW custfreight AS
       SELECT SUM(Freight) AS Freight, CustomerID
                 FROM   Orders
                 GROUP  BY CustomerID
    go
    SELECT C.CustomerID, f.custname
    FROM   Customers C
    CROSS  APPLY dbo.funkis(C.CustomerID) f
    LEFT   JOIN custfreight AS O  ON C.CustomerID = O.CustomerID
    WHERE  round(O.Freight, 0) = 12000
    go
    SELECT C.CustomerID, f.custname
    FROM   Customers C
    LEFT   JOIN custfreight AS O ON C.CustomerID = O.CustomerID
    CROSS  APPLY dbo.funkis(C.CustomerID) f
    WHERE  round(O.Freight, 0) = 12000
    go
    DROP FUNCTION funkis
    DROP VIEW custfreight


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, January 05, 2013 9:24 PM