Tuning query help

• Kwisatz78

  SSChampion

  Points: 12478

  More actions

  February 28, 2012 at 5:38 am

  #253703

  Hi all

  I have identified a query which is consuming a relatively large amount of CPU resource and is executed often, so I was wanting to perhaps tune it. The query is as follows:

  Select'All' AS 'EmplID',

  'All' AS 'Name',

  0 AS 'SortOrder'

  Union

  Selectpe.EMPLID,

  pe.Name,

  1 AS 'SortOrder'

  From DynamicsV5Realtime.dbo.PROJTABLE proj

  Join udf_R000_EmployeeList() pe On proj.RESPONSIBLEFINANCIAL = pe.EMPLID

  Where proj.DATAAREAID = 'ajb'

  Union

  Select pe.EMPLID, pe.Name, 1 AS 'SortOrder'

  From DynamicsV5Realtime.dbo.SalesTable shed

  Join udf_R000_EmployeeList() pe On shed.MARProjectEngineer = pe.EMPLID

  Where shed.DATAAREAID = 'ajb'

  Union

  Select'ZZ99' AS 'EmplID',

  'Unknown' AS 'Name',

  2 AS 'SortOrder'

  ORDER BY 'SortOrder', 'Name'

  Each of the queries looks to be using index seeks, and when run individually run in less than a second, however when part of the union the query takes over 2mins. Obviously there must be something in the union that is causing he problem.

  I have looked at the estimated CPU costs and individual items in the execution plan do not seem to be costing too much. Could anyone give me any pointers as to how to tune this?

  I have included the XML for the plan below also.

  Many thanks

  <?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.0" Build="9.00.3042.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">

  <BatchSequence>

  <Batch>

  <Statements>

  <StmtSimple StatementCompId="2" StatementEstRows="1002.73" StatementId="3" StatementOptmLevel="FULL" StatementSubTreeCost="4.82017" StatementText="Select 'All' AS 'EmplID', 'All' AS 'Name', 0 AS 'SortOrder' Union Select pe.EMPLID, pe.Name, 1 AS 'SortOrder' From DynamicsV5Realtime.dbo.PROJTABLE proj Join udf_R000_EmployeeList() pe On proj.RESPONSIBLEFINANCIAL = pe.EMPLID Where proj.DATAAREAID = 'ajb' Union Select pe.EMPLID, pe.Name, 1 AS 'SortOrder' From DynamicsV5Realtime.dbo.SalesTable shed Join udf_R000_EmployeeList() pe On shed.MARProjectEngineer = pe.EMPLID Where shed.DATAAREAID = 'ajb' Union Select 'ZZ99' AS 'EmplID', 'Unknown' AS 'Name', 2 AS 'SortOrder' ORDER BY 'SortOrder', 'Name' " StatementType="SELECT">

  <StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />

  <QueryPlan DegreeOfParallelism="1" MemoryGrant="136" CachedPlanSize="73" CompileTime="86" CompileCPU="29" CompileMemory="1096">

  <MissingIndexes>

  <MissingIndexGroup Impact="86.7031">

  <MissingIndex Database="[DynamicsV5Realtime]" Schema="[dbo]" Table="[SALESTABLE]">

  <ColumnGroup Usage="EQUALITY">

  <Column Name="[DATAAREAID]" ColumnId="204" />

  <Column Name="[MARPROJECTENGINEER]" ColumnId="219" />

  </ColumnGroup>

  </MissingIndex>

  </MissingIndexGroup>

  </MissingIndexes>

  <RelOp AvgRowSize="57" EstimateCPU="0.00100473" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1002.73" LogicalOp="Sequence" NodeId="0" Parallel="false" PhysicalOp="Sequence" EstimatedTotalSubtreeCost="4.82017">

  <OutputList>

  <ColumnReference Column="Union1014" />

  <ColumnReference Column="Union1015" />

  <ColumnReference Column="Union1016" />

  </OutputList>

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRows="33" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <Sequence>

  <RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Table-valued function" NodeId="1" Parallel="false" PhysicalOp="Table-valued function" EstimatedTotalSubtreeCost="1.157E-06">

  <OutputList />

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="1" />

  </RunTimeInformation>

  <TableValuedFunction>

  <DefinedValues />

  <Object Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" />

  </TableValuedFunction>

  </RelOp>

  <RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Table-valued function" NodeId="2" Parallel="false" PhysicalOp="Table-valued function" EstimatedTotalSubtreeCost="1.157E-06">

  <OutputList />

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="1" />

  </RunTimeInformation>

  <TableValuedFunction>

  <DefinedValues />

  <Object Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" />

  </TableValuedFunction>

  </RelOp>

  <RelOp AvgRowSize="57" EstimateCPU="0.00570447" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1002.73" LogicalOp="Union" NodeId="3" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="4.81916">

  <OutputList>

  <ColumnReference Column="Union1016" />

  <ColumnReference Column="Union1015" />

  <ColumnReference Column="Union1014" />

  </OutputList>

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRows="33" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <Merge>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Union1016" />

  <ColumnReference Column="Union1016" />

  <ColumnReference Column="Expr1013" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Column="Union1015" />

  <ColumnReference Column="Union1015" />

  <ColumnReference Column="Expr1012" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Column="Union1014" />

  <ColumnReference Column="Union1014" />

  <ColumnReference Column="Expr1011" />

  </DefinedValue>

  </DefinedValues>

  <RelOp AvgRowSize="57" EstimateCPU="0.00570447" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1002.73" LogicalOp="Union" NodeId="4" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="4.81916">

  <OutputList>

  <ColumnReference Column="Union1016" />

  <ColumnReference Column="Union1015" />

  <ColumnReference Column="Union1014" />

  </OutputList>

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRows="32" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <Merge>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Union1016" />

  <ColumnReference Column="Union1016" />

  <ColumnReference Column="Expr1010" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Column="Union1015" />

  <ColumnReference Column="Union1015" />

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="name" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Column="Union1014" />

  <ColumnReference Column="Union1014" />

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="emplid" />

  </DefinedValue>

  </DefinedValues>

  <RelOp AvgRowSize="57" EstimateCPU="0.00570447" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1002.73" LogicalOp="Union" NodeId="5" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="4.81916">

  <OutputList>

  <ColumnReference Column="Union1016" />

  <ColumnReference Column="Union1015" />

  <ColumnReference Column="Union1014" />

  </OutputList>

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRows="26" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <Merge>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Union1016" />

  <ColumnReference Column="Expr1002" />

  <ColumnReference Column="Expr1006" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Column="Union1015" />

  <ColumnReference Column="Expr1001" />

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="name" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Column="Union1014" />

  <ColumnReference Column="Expr1000" />

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="emplid" />

  </DefinedValue>

  </DefinedValues>

  <RelOp AvgRowSize="19" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Constant Scan" NodeId="6" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="1.157E-06">

  <OutputList>

  <ColumnReference Column="Expr1000" />

  <ColumnReference Column="Expr1001" />

  <ColumnReference Column="Expr1002" />

  </OutputList>

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <ConstantScan>

  <Values>

  <Row>

  <ScalarOperator ScalarString="'All'">

  <Const ConstValue="'All'" />

  </ScalarOperator>

  <ScalarOperator ScalarString="'All'">

  <Const ConstValue="'All'" />

  </ScalarOperator>

  <ScalarOperator ScalarString="(0)">

  <Const ConstValue="(0)" />

  </ScalarOperator>

  </Row>

  </Values>

  </ConstantScan>

  </RelOp>

  <RelOp AvgRowSize="57" EstimateCPU="0.000115219" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="83.4017" LogicalOp="Aggregate" NodeId="10" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.338647">

  <OutputList>

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="emplid" />

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="name" />

  <ColumnReference Column="Expr1006" />

  </OutputList>

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRows="25" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <StreamAggregate>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1006" />

  <ScalarOperator ScalarString="ANY([Expr1006])">

  <Aggregate AggType="ANY" Distinct="false">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1006" />

  </Identifier>

  </ScalarOperator>

  </Aggregate>

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <GroupBy>

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="name" />

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="emplid" />

  </GroupBy>

  <RelOp AvgRowSize="57" EstimateCPU="1.47037E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="147.037" LogicalOp="Compute Scalar" NodeId="11" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.338531">

  <OutputList>

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="emplid" />

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="name" />

  <ColumnReference Column="Expr1006" />

  </OutputList>

  <ComputeScalar>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1006" />

  <ScalarOperator ScalarString="(1)">

  <Const ConstValue="(1)" />

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <RelOp AvgRowSize="53" EstimateCPU="0.0165904" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="147.037" LogicalOp="Inner Join" NodeId="12" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.338517">

  <OutputList>

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="emplid" />

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="name" />

  </OutputList>

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRows="1250" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <NestedLoops Optimized="false">

  <Predicate>

  <ScalarOperator ScalarString="[DynamicsV5Realtime].[dbo].[PROJTABLE].[RESPONSIBLEFINANCIAL] as [proj].[RESPONSIBLEFINANCIAL]=[Expr1018]">

  <Compare CompareOp="EQ">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[DynamicsV5Realtime]" Schema="[dbo]" Table="[PROJTABLE]" Alias="[proj]" Column="RESPONSIBLEFINANCIAL" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1018" />

  </Identifier>

  </ScalarOperator>

  </Compare>

  </ScalarOperator>

  </Predicate>

  <RelOp AvgRowSize="75" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="13" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0146445">

  <OutputList>

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="emplid" />

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="name" />

  <ColumnReference Column="Expr1018" />

  </OutputList>

  <ComputeScalar>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1018" />

  <ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(20),[AxReports].[dbo].[udf_R000_EmployeeList].[emplid] as [pe].[emplid],0)">

  <Convert DataType="nvarchar" Length="40" Style="0" Implicit="true">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="emplid" />

  </Identifier>

  </ScalarOperator>

  </Convert>

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <RelOp AvgRowSize="53" EstimateCPU="0.000100053" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Sort" NodeId="14" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0146444">

  <OutputList>

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="emplid" />

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="name" />

  </OutputList>

  <MemoryFractions Input="0.5" Output="0.5" />

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1161" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <Sort Distinct="false">

  <OrderBy>

  <OrderByColumn Ascending="true">

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="name" />

  </OrderByColumn>

  <OrderByColumn Ascending="true">

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="emplid" />

  </OrderByColumn>

  </OrderBy>

  <RelOp AvgRowSize="53" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Table Scan" NodeId="15" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831">

  <OutputList>

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="emplid" />

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="name" />

  </OutputList>

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRows="1161" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <TableScan Ordered="false" ForcedIndex="false" NoExpandHint="false">

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="emplid" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="name" />

  </DefinedValue>

  </DefinedValues>

  <Object Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" />

  </TableScan>

  </RelOp>

  </Sort>

  </RelOp>

  </ComputeScalar>

  </RelOp>

  <RelOp AvgRowSize="13" EstimateCPU="0.0045229" EstimateIO="0.301568" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3969" LogicalOp="Clustered Index Seek" NodeId="19" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.306091">

  <OutputList>

  <ColumnReference Database="[DynamicsV5Realtime]" Schema="[dbo]" Table="[PROJTABLE]" Alias="[proj]" Column="RESPONSIBLEFINANCIAL" />

  </OutputList>

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRows="4608009" ActualEndOfScans="1161" ActualExecutions="1161" />

  </RunTimeInformation>

  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Database="[DynamicsV5Realtime]" Schema="[dbo]" Table="[PROJTABLE]" Alias="[proj]" Column="RESPONSIBLEFINANCIAL" />

  </DefinedValue>

  </DefinedValues>

  <Object Database="[DynamicsV5Realtime]" Schema="[dbo]" Table="[PROJTABLE]" Index="[I_624PROJECTIDX]" Alias="[proj]" />

  <SeekPredicates>

  <SeekPredicate>

  <Prefix ScanType="EQ">

  <RangeColumns>

  <ColumnReference Database="[DynamicsV5Realtime]" Schema="[dbo]" Table="[PROJTABLE]" Alias="[proj]" Column="DATAAREAID" />

  </RangeColumns>

  <RangeExpressions>

  <ScalarOperator ScalarString="N'ajb'">

  <Const ConstValue="N'ajb'" />

  </ScalarOperator>

  </RangeExpressions>

  </Prefix>

  </SeekPredicate>

  </SeekPredicates>

  </IndexScan>

  </RelOp>

  </NestedLoops>

  </RelOp>

  </ComputeScalar>

  </RelOp>

  </StreamAggregate>

  </RelOp>

  </Merge>

  </RelOp>

  <RelOp AvgRowSize="57" EstimateCPU="0.00126294" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="109.586" LogicalOp="Aggregate" NodeId="27" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="4.47481">

  <OutputList>

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="emplid" />

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="name" />

  <ColumnReference Column="Expr1010" />

  </OutputList>

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRows="15" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <StreamAggregate>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1010" />

  <ScalarOperator ScalarString="ANY([Expr1010])">

  <Aggregate AggType="ANY" Distinct="false">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1010" />

  </Identifier>

  </ScalarOperator>

  </Aggregate>

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <GroupBy>

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="name" />

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="emplid" />

  </GroupBy>

  <RelOp AvgRowSize="57" EstimateCPU="0.00024163" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2416.3" LogicalOp="Compute Scalar" NodeId="28" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="4.47354">

  <OutputList>

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="emplid" />

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="name" />

  <ColumnReference Column="Expr1010" />

  </OutputList>

  <ComputeScalar>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1010" />

  <ScalarOperator ScalarString="(1)">

  <Const ConstValue="(1)" />

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <RelOp AvgRowSize="53" EstimateCPU="0.101001" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2416.3" LogicalOp="Inner Join" NodeId="29" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="4.4733">

  <OutputList>

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="emplid" />

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="name" />

  </OutputList>

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRows="974" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <NestedLoops Optimized="false">

  <Predicate>

  <ScalarOperator ScalarString="[DynamicsV5Realtime].[dbo].[SALESTABLE].[MARPROJECTENGINEER] as [shed].[MARPROJECTENGINEER]=[Expr1019]">

  <Compare CompareOp="EQ">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[DynamicsV5Realtime]" Schema="[dbo]" Table="[SALESTABLE]" Alias="[shed]" Column="MARPROJECTENGINEER" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1019" />

  </Identifier>

  </ScalarOperator>

  </Compare>

  </ScalarOperator>

  </Predicate>

  <RelOp AvgRowSize="75" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="30" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0146445">

  <OutputList>

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="emplid" />

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="name" />

  <ColumnReference Column="Expr1019" />

  </OutputList>

  <ComputeScalar>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1019" />

  <ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(20),[AxReports].[dbo].[udf_R000_EmployeeList].[emplid] as [pe].[emplid],0)">

  <Convert DataType="nvarchar" Length="40" Style="0" Implicit="true">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="emplid" />

  </Identifier>

  </ScalarOperator>

  </Convert>

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <RelOp AvgRowSize="53" EstimateCPU="0.000100053" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Sort" NodeId="31" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0146444">

  <OutputList>

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="emplid" />

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="name" />

  </OutputList>

  <MemoryFractions Input="0.5" Output="0.5" />

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1161" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <Sort Distinct="false">

  <OrderBy>

  <OrderByColumn Ascending="true">

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="name" />

  </OrderByColumn>

  <OrderByColumn Ascending="true">

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="emplid" />

  </OrderByColumn>

  </OrderBy>

  <RelOp AvgRowSize="53" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Table Scan" NodeId="32" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831">

  <OutputList>

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="emplid" />

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="name" />

  </OutputList>

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRows="1161" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <TableScan Ordered="false" ForcedIndex="false" NoExpandHint="false">

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="emplid" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" Column="name" />

  </DefinedValue>

  </DefinedValues>

  <Object Database="[AxReports]" Schema="[dbo]" Table="[udf_R000_EmployeeList]" Alias="[pe]" />

  </TableScan>

  </RelOp>

  </Sort>

  </RelOp>

  </ComputeScalar>

  </RelOp>

  <RelOp AvgRowSize="31" EstimateCPU="0.0267363" EstimateIO="4.32367" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="24163" LogicalOp="Clustered Index Seek" NodeId="36" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="4.35041">

  <OutputList>

  <ColumnReference Database="[DynamicsV5Realtime]" Schema="[dbo]" Table="[SALESTABLE]" Alias="[shed]" Column="MARPROJECTENGINEER" />

  </OutputList>

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRows="19383763" ActualEndOfScans="1161" ActualExecutions="1161" />

  </RunTimeInformation>

  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Database="[DynamicsV5Realtime]" Schema="[dbo]" Table="[SALESTABLE]" Alias="[shed]" Column="MARPROJECTENGINEER" />

  </DefinedValue>

  </DefinedValues>

  <Object Database="[DynamicsV5Realtime]" Schema="[dbo]" Table="[SalesTable]" Index="[I_366SALESIDX]" Alias="[shed]" />

  <SeekPredicates>

  <SeekPredicate>

  <Prefix ScanType="EQ">

  <RangeColumns>

  <ColumnReference Database="[DynamicsV5Realtime]" Schema="[dbo]" Table="[SALESTABLE]" Alias="[shed]" Column="DATAAREAID" />

  </RangeColumns>

  <RangeExpressions>

  <ScalarOperator ScalarString="N'ajb'">

  <Const ConstValue="N'ajb'" />

  </ScalarOperator>

  </RangeExpressions>

  </Prefix>

  </SeekPredicate>

  </SeekPredicates>

  </IndexScan>

  </RelOp>

  </NestedLoops>

  </RelOp>

  </ComputeScalar>

  </RelOp>

  </StreamAggregate>

  </RelOp>

  </Merge>

  </RelOp>

  <RelOp AvgRowSize="22" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Constant Scan" NodeId="44" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="1.157E-06">

  <OutputList>

  <ColumnReference Column="Expr1011" />

  <ColumnReference Column="Expr1012" />

  <ColumnReference Column="Expr1013" />

  </OutputList>

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <ConstantScan>

  <Values>

  <Row>

  <ScalarOperator ScalarString="'ZZ99'">

  <Const ConstValue="'ZZ99'" />

  </ScalarOperator>

  <ScalarOperator ScalarString="'Unknown'">

  <Const ConstValue="'Unknown'" />

  </ScalarOperator>

  <ScalarOperator ScalarString="(2)">

  <Const ConstValue="(2)" />

  </ScalarOperator>

  </Row>

  </Values>

  </ConstantScan>

  </RelOp>

  </Merge>

  </RelOp>

  </Sequence>

  </RelOp>

  </QueryPlan>

  </StmtSimple>

  </Statements>

  </Batch>

  </BatchSequence>

  </ShowPlanXML>

• salliven

  SSCrazy

  Points: 2644

  More actions

  February 28, 2012 at 6:35 am

  #1452551

  Please check this query:

  Select 'All' AS 'EmplID', 'All' AS 'Name', 0 AS 'SortOrder'

  Union ALL

  Select DISTINCT pe.EMPLID, pe.Name, 1 AS 'SortOrder'

  From DynamicsV5Realtime.dbo.PROJTABLE proj

  Join DynamicsV5Realtime.dbo.SalesTable shed On proj.RESPONSIBLEFINANCIAL = shed.MARProjectEngineer

  Join udf_R000_EmployeeList() pe On proj.RESPONSIBLEFINANCIAL = pe.EMPLID

  Where proj.DATAAREAID = 'ajb' AND shed.DATAAREAID = 'ajb'

  Union ALL

  Select 'ZZ99' AS 'EmplID', 'Unknown' AS 'Name', 2 AS 'SortOrder'

  ORDER BY 'SortOrder', 'Name'

• Kwisatz78

  SSChampion

  Points: 12478

  More actions

  February 28, 2012 at 6:59 am

  #1452562

  hi and thanks

  The result set is not the same, but I will have a play around with it.

  Thanks

• salliven

  SSCrazy

  Points: 2644

  More actions

  February 28, 2012 at 7:04 am

  #1452569

  Then change this line:

  Where proj.DATAAREAID = 'ajb' AND shed.DATAAREAID = 'ajb'

  on this:

  Where proj.DATAAREAID = 'ajb' OR shed.DATAAREAID = 'ajb'

• Kwisatz78

  SSChampion

  Points: 12478

  More actions

  February 28, 2012 at 7:08 am

  #1452574

  Still different am afraid

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  February 28, 2012 at 8:29 am

  #1452640

  From DynamicsV5Realtime.dbo.PROJTABLE proj

  Join DynamicsV5Realtime.dbo.SalesTable shed On proj.RESPONSIBLEFINANCIAL = shed.MARProjectEngineer

  Join udf_R000_EmployeeList() pe On proj.RESPONSIBLEFINANCIAL = pe.EMPLID

  is very different to your original query and cannot produce the same resultset.

  Try to split your query into separate SELECT INTO #temptable parts.

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  February 28, 2012 at 8:50 am

  #1452660

  Try the following:

  Selectpe.EMPLID, pe.Name

  Into #allempl

  From DynamicsV5Realtime.dbo.PROJTABLE proj

  Join udf_R000_EmployeeList() pe On proj.RESPONSIBLEFINANCIAL = pe.EMPLID

  Where proj.DATAAREAID = 'ajb'

  Union ALL

  Select pe.EMPLID, pe.Name, 1 AS 'SortOrder'

  From DynamicsV5Realtime.dbo.SalesTable shed

  Join udf_R000_EmployeeList() pe On shed.MARProjectEngineer = pe.EMPLID

  Where shed.DATAAREAID = 'ajb'

  -- as there is no dedupe, you may have duplicates in #allempl

  -- therefore you will need to dedupe

  Select'All' AS EmplID, 'All' AS Name, 0 AS SortOrder

  Union All

  SelectEMPLID, pe.Name, 1

  From #allempl

  Group By EMPLID, pe.Name

  Union All

  Select'ZZ99', 'Unknown', 2

  ORDER BY SortOrder, Name

  Check the performance of " Select EMPLID, pe.Name From #allempl Group By EMPLID, pe.Name ". You may find that the slowest part of your query is de-duping of the employee records.

  Also you can simply try to reduce number of times you lookup the name by joining to udf just once:

  -- this CTE will produce de-duped list of all relevant EMPLID's

  ;with cte_ddemp

  as

  (

  Selectproj.RESPONSIBLEFINANCIAL as EMPLID

  From DynamicsV5Realtime.dbo.PROJTABLE proj

  Where proj.DATAAREAID = 'ajb'

  Union

  Select shed.MARProjectEngineer as EMPLID

  From DynamicsV5Realtime.dbo.SalesTable shed

  Where shed.DATAAREAID = 'ajb'

  )

  -- No you can use UNION ALL and perform single name lookup

  Select 'All' AS EmplID, 'All' AS Name, 0 AS SortOrder

  Union All

  Selecte.EMPLID, pe.Name, 1

  From cte_ddemp e

  Join udf_R000_EmployeeList() pe On pe.EMPLID = e.EMPLID

  Union All

  Select'ZZ99', 'Unknown', 2

  ORDER BY SortOrder, Name

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• Kwisatz78

  SSChampion

  Points: 12478

  More actions

  February 28, 2012 at 9:08 am

  #1452678

  Ok thanks, something else is that the UDF is a table value function as shown below, would this be better as a view as I understand that TFs do not make use of statistics or indexes?

  RETURNS @tbl TABLE (emplid varchar(20) NOT NULL,

  partyid varchar(20) NOT NULL,

  name varchar(60) NOT NULL,

  email varchar(80) NULL,

  sortorder tinyint NOT NULL,

  phone varchar(60) NULL)

  As

  BEGIN

  INSERT Into @tbl

  Select e.emplid, e.partyid, isnull(p.Name,e.emplid) as Name, Email, 1, Phone

  FromBIDATAWSQL.DynamicsV5Realtime.dbo.empltable e

  Left Join BIDATAWSQL.DynamicsV5Realtime.dbo.dirpartytable p

  One.partyid = p.partyid

  Ande.dataareaid = p.dataareaid

  Left Join

  (Select PartyID, Email, a.dataareaid, Phone

  FromBIDATAWSQL.DynamicsV5Realtime.dbo.DIRECOMMUNICATIONADDRESS a

  JoinBIDATAWSQL.DynamicsV5Realtime.dbo.DIRPARTYECOMMUNICATIONRELA2608 b

  Ona.recid = b.valuesrecid

  Anda.dataareaid = b.dataareaid

  Whereecommunicationtypeid = 'email'

  Anda.dataareaid = 'grp')email

  One.partyid = email.partyid

  Ande.dataareaid = email.dataareaid

  Wheree.dataareaid = 'grp'

  Andp.[Type] = 1

  Union

  Select 'All', 'All', 'All', 'All', 0, 'All'

  Order By 5,3

  RETURN

  END

  GO

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  February 28, 2012 at 9:21 am

  #1452691

  As the query from your original post only needs a Name, there is no point to use UDF you have. Looks like you can join directly to BIDATAWSQL.DynamicsV5Realtime.dbo.empltable

  Also, as you use a table from linked server, you can forget about using indexes on this table....

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• Kwisatz78

  SSChampion

  Points: 12478

  More actions

  February 28, 2012 at 9:33 am

  #1452697

  Hi

  I don't believe the UDF is actually using a linked server, for some reason the 4 part naming convention is being used when all the code I have posted is on the same server. I will look at writing the query to link directly to the table however.

  You have been very helpful thanks.