Tuning query help

Question

Post reply

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>

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

salliven SSCrazy Points: 2644 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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 · Answer 4

Kwisatz78

SSChampion

Points: 12478

February 28, 2012 at 7:08 am

#1452574

Still different am afraid

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

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 · Answer 6

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 · Answer 7

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 · Answer 8

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 · Answer 9

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.