Link to home
Start Free TrialLog in
Avatar of Robb Hill
Robb HillFlag for United States of America

asked on

SQL Improvement ( Speed)

I have a query that after a data  increase on the table went from executing in less than 10 seconds to upward 50+ seconds...Please help me address.

In this case I executing the procedure as follows:
exec caDoc_CRM.dbo.spGetTaskListForActivityPage @SiteCode='Root', @ClientBaseId=3, @OnlyTaskWithClients=0



USE [cadoc_crm]
GO
/****** Object:  StoredProcedure [dbo].[spGetTaskListForActivityPage]    Script Date: 2/9/2017 8:18:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[spGetTaskListForActivityPage]

	@SiteCode varchar(20), @ClientBaseId int, @TaskId int = null, @ClientId int = null, @ContactId int = null, @OnlyTaskWithClients bit = 0
	


as

if IsNull(@TaskId, -1) <= 0
	select @TaskId = null

if IsNull(@ClientId, -1) <= 0
	select @ClientId = null
	
if IsNull(@ContactId, -1) <= 0
	select @ContactId = null

if object_id('tempdb.dbo.#tmpTaskIds') is not null
	drop table #tmpTaskIds

create table #tmpTaskIds(nTaskId int)
create index tmpTaskIds_nTaskId on #tmpTaskIds(nTaskId)

insert into #tmpTaskIds
	select nTaskId
		from caDoc_CRM.dbo.tTask 
		where ((cSiteCode = @SiteCode and lCompleted = 0) or (@TaskId is not null and nTaskId = @TaskId))

declare @IdTable table(TaskId int, ClientId int)

insert into @IdTable
	select tc.nTaskId, tc.nClientId
		from caDoc_CRM.dbo.tTaskXClient tc
		inner join #tmpTaskIds t on tc.nTaskId = t.nTaskId
		where @ClientId is null or (@ClientId is not null and nClientId = @ClientId)

insert into @IdTable
	select distinct tc.nTaskId, cli.nId 
		from caDoc_CRM.dbo.tTaskXContact tc
		inner join caDoc_CRM.dbo.tContact con on tc.nContactId = con.nId
			and (@ContactId is null or (@ContactId is not null and con.nId = @ContactId))
		inner join caDoc_CRM.dbo.tClient cli on con.nIdClient = cli.nId
			and (@ClientId is null or (@ClientId is not null and cli.nId = @ClientId))
		inner join #tmpTaskIds t on tc.nTaskId = t.nTaskId
		where @ClientId is null or (@ClientId is not null and cli.nId = @ClientId)

delete from @IdTable
	where ClientId = @ClientBaseId
		and TaskId in (select TaskId from @IdTable where ClientId != @ClientBaseId)

if object_id('tempdb.dbo.#tmpTaskIds') is not null
	drop table #tmpTaskIds
	
if object_id('tempdb.dbo.#tmpTaskResults') is not null
	drop table #tmpTaskResults

select distinct t.nTaskID
		, t.cTask cTask
		, ISNULL(c.cName, '[unassigned]') cClientName
		, ISNULL(c.nid, 0) nId
	into #tmpTaskResults
	from caDoc_CRM.dbo.tTask t
	left join @IdTable tc on t.nTaskId = tc.TaskId	
	left join caDoc_CRM.dbo.tClient c on tc.ClientId = c.nID
	order by 3, 2

if @OnlyTaskWithClients = 1
	delete from #tmpTaskResults where nId <= 0

select * from #tmpTaskResults
	

Open in new window


Here is the excecution plan:

<?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.3.1" Build="12.0.5203.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="17" StatementEstRows="15607.9" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="2.75326" StatementText="insert into #tmpTaskIds&#xD;&#xA;	select nTaskId&#xD;&#xA;		from caDoc_CRM.dbo.tTask &#xD;&#xA;		where ((cSiteCode = @SiteCode and lCompleted = 0) or (@TaskId is not null and nTaskId = @TaskId))" StatementType="INSERT" QueryHash="0xAA97472805FAEE04" QueryPlanHash="0x2EB0DA5521728196" RetrievedFromCache="true">
          <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="1" MemoryGrant="2336" CachedPlanSize="24" CompileTime="4" CompileCPU="4" CompileMemory="376">
            <MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="2336" RequiredMemory="512" DesiredMemory="2336" RequestedMemory="2336" GrantWaitTime="0" GrantedMemory="2336" MaxUsedMemory="896" MaxQueryMemory="3004400" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="104857" EstimatedAvailableDegreeOfParallelism="4" MaxCompileMemory="10254952" />
            <RelOp AvgRowSize="9" EstimateCPU="0.0156079" EstimateIO="0.247837" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15607.9" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Index Insert" EstimatedTotalSubtreeCost="2.75326">
              <OutputList />
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="15544" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="182" ActualCPUms="182" ActualScans="0" ActualLogicalReads="31258" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
              </RunTimeInformation>
              <Update DMLRequestSort="true">
                <Object Database="[tempdb]" Schema="[dbo]" Table="[#tmpTaskIds]" Index="[tmpTaskIds_nTaskId]" Storage="RowStore" />
                <SetPredicate>
                  <ScalarOperator ScalarString="[Bmk10001008] = [Bmk1000],[nTaskId1009] = [#tmpTaskIds].[nTaskId]">
                    <ScalarExpressionList>
                      <ScalarOperator>
                        <MultipleAssign>
                          <Assign>
                            <ColumnReference Column="Bmk10001008" />
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Column="Bmk1000" />
                              </Identifier>
                            </ScalarOperator>
                          </Assign>
                          <Assign>
                            <ColumnReference Column="nTaskId1009" />
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Table="[#tmpTaskIds]" Column="nTaskId" />
                              </Identifier>
                            </ScalarOperator>
                          </Assign>
                        </MultipleAssign>
                      </ScalarOperator>
                    </ScalarExpressionList>
                  </ScalarOperator>
                </SetPredicate>
                <RelOp AvgRowSize="19" EstimateCPU="0.998045" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15607.9" LogicalOp="Sort" NodeId="1" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="2.48982">
                  <OutputList>
                    <ColumnReference Column="Bmk1000" />
                    <ColumnReference Table="[#tmpTaskIds]" Column="nTaskId" />
                  </OutputList>
                  <MemoryFractions Input="1" Output="1" />
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="15544" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="120" ActualCPUms="120" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                  </RunTimeInformation>
                  <Sort Distinct="false">
                    <OrderBy>
                      <OrderByColumn Ascending="true">
                        <ColumnReference Table="[#tmpTaskIds]" Column="nTaskId" />
                      </OrderByColumn>
                      <OrderByColumn Ascending="true">
                        <ColumnReference Column="Bmk1000" />
                      </OrderByColumn>
                    </OrderBy>
                    <RelOp AvgRowSize="19" EstimateCPU="0.0156079" EstimateIO="0.14485" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15607.9" LogicalOp="Insert" NodeId="2" Parallel="false" PhysicalOp="Table Insert" EstimatedTotalSubtreeCost="1.48051">
                      <OutputList>
                        <ColumnReference Column="Bmk1000" />
                        <ColumnReference Table="[#tmpTaskIds]" Column="nTaskId" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="15544" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="96" ActualCPUms="95" ActualScans="0" ActualLogicalReads="15568" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                      </RunTimeInformation>
                      <Update DMLRequestSort="false">
                        <Object Database="[tempdb]" Schema="[dbo]" Table="[#tmpTaskIds]" Storage="RowStore" />
                        <SetPredicate>
                          <ScalarOperator ScalarString="[#tmpTaskIds].[nTaskId] = [cadoc_crm].[dbo].[tTask].[nTaskID]">
                            <ScalarExpressionList>
                              <ScalarOperator>
                                <MultipleAssign>
                                  <Assign>
                                    <ColumnReference Table="[#tmpTaskIds]" Column="nTaskId" />
                                    <ScalarOperator>
                                      <Identifier>
                                        <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Column="nTaskID" />
                                      </Identifier>
                                    </ScalarOperator>
                                  </Assign>
                                </MultipleAssign>
                              </ScalarOperator>
                            </ScalarExpressionList>
                          </ScalarOperator>
                        </SetPredicate>
                        <RelOp AvgRowSize="11" EstimateCPU="0.00156079" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15607.9" LogicalOp="Top" NodeId="3" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="1.32005">
                          <OutputList>
                            <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Column="nTaskID" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="15544" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="38" ActualCPUms="38" />
                          </RunTimeInformation>
                          <Top RowCount="true" IsPercent="false" WithTies="false">
                            <TopExpression>
                              <ScalarOperator ScalarString="(0)">
                                <Const ConstValue="(0)" />
                              </ScalarOperator>
                            </TopExpression>
                            <RelOp AvgRowSize="20" EstimateCPU="0.0600146" EstimateIO="1.17794" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15607.9" LogicalOp="Clustered Index Scan" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="1.23795" TableCardinality="54416">
                              <OutputList>
                                <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Column="nTaskID" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="0" ActualRows="15544" ActualRowsRead="54416" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="35" ActualCPUms="35" ActualScans="1" ActualLogicalReads="1592" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                              </RunTimeInformation>
                              <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Column="nTaskID" />
                                  </DefinedValue>
                                </DefinedValues>
                                <Object Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Index="[PK_tTask]" IndexKind="Clustered" Storage="RowStore" />
                                <Predicate>
                                  <ScalarOperator ScalarString="[cadoc_crm].[dbo].[tTask].[cSiteCode]=[@SiteCode] AND [cadoc_crm].[dbo].[tTask].[lCompleted]=(0) OR [@TaskId] IS NOT NULL AND [cadoc_crm].[dbo].[tTask].[nTaskID]=[@TaskId]">
                                    <Logical Operation="OR">
                                      <ScalarOperator>
                                        <Logical Operation="AND">
                                          <ScalarOperator>
                                            <Compare CompareOp="EQ">
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Column="cSiteCode" />
                                                </Identifier>
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Column="@SiteCode" />
                                                </Identifier>
                                              </ScalarOperator>
                                            </Compare>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Compare CompareOp="EQ">
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Column="lCompleted" />
                                                </Identifier>
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Const ConstValue="(0)" />
                                              </ScalarOperator>
                                            </Compare>
                                          </ScalarOperator>
                                        </Logical>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Logical Operation="AND">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Column="ConstExpr1005">
                                                <ScalarOperator>
                                                  <Compare CompareOp="IS NOT">
                                                    <ScalarOperator>
                                                      <Identifier>
                                                        <ColumnReference Column="@TaskId" />
                                                      </Identifier>
                                                    </ScalarOperator>
                                                    <ScalarOperator>
                                                      <Const ConstValue="NULL" />
                                                    </ScalarOperator>
                                                  </Compare>
                                                </ScalarOperator>
                                              </ColumnReference>
                                            </Identifier>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Compare CompareOp="EQ">
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Column="nTaskID" />
                                                </Identifier>
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Column="@TaskId" />
                                                </Identifier>
                                              </ScalarOperator>
                                            </Compare>
                                          </ScalarOperator>
                                        </Logical>
                                      </ScalarOperator>
                                    </Logical>
                                  </ScalarOperator>
                                </Predicate>
                              </IndexScan>
                            </RelOp>
                          </Top>
                        </RelOp>
                      </Update>
                    </RelOp>
                  </Sort>
                </RelOp>
              </Update>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@TaskId" ParameterCompiledValue="NULL" ParameterRuntimeValue="NULL" />
              <ColumnReference Column="@SiteCode" ParameterCompiledValue="'Root'" ParameterRuntimeValue="'Root'" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="19" StatementEstRows="15542.9" StatementId="2" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="1.14791" StatementText="insert into @IdTable&#xD;&#xA;	select tc.nTaskId, tc.nClientId&#xD;&#xA;		from caDoc_CRM.dbo.tTaskXClient tc&#xD;&#xA;		inner join #tmpTaskIds t on tc.nTaskId = t.nTaskId&#xD;&#xA;		where @ClientId is null or (@ClientId is not null and nClientId = @ClientId)" StatementType="INSERT" QueryHash="0xFE2D144169344B3A" QueryPlanHash="0x870FFFDB79F7FBB0" RetrievedFromCache="true">
          <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" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" MemoryGrant="4544" CachedPlanSize="40" CompileTime="16" CompileCPU="16" CompileMemory="368">
            <MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="4544" RequiredMemory="1024" DesiredMemory="4544" RequestedMemory="4544" GrantWaitTime="0" GrantedMemory="4544" MaxUsedMemory="1024" MaxQueryMemory="3004400" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="104857" EstimatedAvailableDegreeOfParallelism="4" MaxCompileMemory="10254328" />
            <RelOp AvgRowSize="9" EstimateCPU="0.0155429" EstimateIO="0.19554" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15542.9" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Table Insert" EstimatedTotalSubtreeCost="1.14791">
              <OutputList />
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="15539" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="72" ActualCPUms="72" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
              </RunTimeInformation>
              <Update DMLRequestSort="false">
                <Object Table="[@IdTable]" Storage="RowStore" />
                <SetPredicate>
                  <ScalarOperator ScalarString="[TaskId] = [cadoc_crm].[dbo].[tTaskXClient].[nTaskID] as [tc].[nTaskID],[ClientId] = [cadoc_crm].[dbo].[tTaskXClient].[nClientID] as [tc].[nClientID]">
                    <ScalarExpressionList>
                      <ScalarOperator>
                        <MultipleAssign>
                          <Assign>
                            <ColumnReference Column="TaskId" />
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nTaskID" />
                              </Identifier>
                            </ScalarOperator>
                          </Assign>
                          <Assign>
                            <ColumnReference Column="ClientId" />
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nClientID" />
                              </Identifier>
                            </ScalarOperator>
                          </Assign>
                        </MultipleAssign>
                      </ScalarOperator>
                    </ScalarExpressionList>
                  </ScalarOperator>
                </SetPredicate>
                <RelOp AvgRowSize="15" EstimateCPU="0.00155429" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15542.9" LogicalOp="Top" NodeId="1" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.936827">
                  <OutputList>
                    <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nTaskID" />
                    <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nClientID" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="15539" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="58" ActualCPUms="58" />
                  </RunTimeInformation>
                  <Top RowCount="true" IsPercent="false" WithTies="false">
                    <TopExpression>
                      <ScalarOperator ScalarString="(0)">
                        <Const ConstValue="(0)" />
                      </ScalarOperator>
                    </TopExpression>
                    <RelOp AvgRowSize="15" EstimateCPU="0.606846" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15542.9" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.935273">
                      <OutputList>
                        <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nTaskID" />
                        <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nClientID" />
                      </OutputList>
                      <MemoryFractions Input="1" Output="1" />
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="15539" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="56" ActualCPUms="56" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                      </RunTimeInformation>
                      <Hash>
                        <DefinedValues />
                        <HashKeysBuild>
                          <ColumnReference Table="#tmpTaskIds" Alias="[t]" Column="nTaskId" />
                        </HashKeysBuild>
                        <HashKeysProbe>
                          <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nTaskID" />
                        </HashKeysProbe>
                        <ProbeResidual>
                          <ScalarOperator ScalarString="#tmpTaskIds.[nTaskId] as [t].[nTaskId]=[cadoc_crm].[dbo].[tTaskXClient].[nTaskID] as [tc].[nTaskID]">
                            <Compare CompareOp="EQ">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Table="#tmpTaskIds" Alias="[t]" Column="nTaskId" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nTaskID" />
                                </Identifier>
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                        </ProbeResidual>
                        <RelOp AvgRowSize="11" EstimateCPU="0.0172554" EstimateIO="0.0209028" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15544" LogicalOp="Table Scan" NodeId="3" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0381582" TableCardinality="15544">
                          <OutputList>
                            <ColumnReference Table="#tmpTaskIds" Alias="[t]" Column="nTaskId" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="15544" ActualRowsRead="15544" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="4" ActualCPUms="4" ActualScans="1" ActualLogicalReads="25" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                          </RunTimeInformation>
                          <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Table="#tmpTaskIds" Alias="[t]" Column="nTaskId" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[tempdb]" Schema="[dbo]" Table="[#tmpTaskIds]" Alias="[t]" Storage="RowStore" />
                          </TableScan>
                        </RelOp>
                        <RelOp AvgRowSize="15" EstimateCPU="0.0600036" EstimateIO="0.182384" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="54406" LogicalOp="Clustered Index Scan" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.242388" TableCardinality="54406">
                          <OutputList>
                            <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nTaskID" />
                            <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nClientID" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="54406" ActualRowsRead="54406" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="22" ActualCPUms="22" ActualScans="1" ActualLogicalReads="245" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                          </RunTimeInformation>
                          <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nTaskID" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nClientID" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Index="[PK_tTaskXClient]" Alias="[tc]" IndexKind="Clustered" Storage="RowStore" />
                            <Predicate>
                              <ScalarOperator ScalarString="[@ClientId] IS NULL OR [@ClientId] IS NOT NULL AND [cadoc_crm].[dbo].[tTaskXClient].[nClientID] as [tc].[nClientID]=[@ClientId]">
                                <Logical Operation="OR">
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Column="ConstExpr1006">
                                        <ScalarOperator>
                                          <Compare CompareOp="IS">
                                            <ScalarOperator>
                                              <Identifier>
                                                <ColumnReference Column="@ClientId" />
                                              </Identifier>
                                            </ScalarOperator>
                                            <ScalarOperator>
                                              <Const ConstValue="NULL" />
                                            </ScalarOperator>
                                          </Compare>
                                        </ScalarOperator>
                                      </ColumnReference>
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Logical Operation="AND">
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Column="ConstExpr1007">
                                            <ScalarOperator>
                                              <Compare CompareOp="IS NOT">
                                                <ScalarOperator>
                                                  <Identifier>
                                                    <ColumnReference Column="@ClientId" />
                                                  </Identifier>
                                                </ScalarOperator>
                                                <ScalarOperator>
                                                  <Const ConstValue="NULL" />
                                                </ScalarOperator>
                                              </Compare>
                                            </ScalarOperator>
                                          </ColumnReference>
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Compare CompareOp="EQ">
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXClient]" Alias="[tc]" Column="nClientID" />
                                            </Identifier>
                                          </ScalarOperator>
                                          <ScalarOperator>
                                            <Identifier>
                                              <ColumnReference Column="@ClientId" />
                                            </Identifier>
                                          </ScalarOperator>
                                        </Compare>
                                      </ScalarOperator>
                                    </Logical>
                                  </ScalarOperator>
                                </Logical>
                              </ScalarOperator>
                            </Predicate>
                          </IndexScan>
                        </RelOp>
                      </Hash>
                    </RelOp>
                  </Top>
                </RelOp>
              </Update>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@ClientId" ParameterCompiledValue="NULL" ParameterRuntimeValue="NULL" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="20" StatementEstRows="24196.4" StatementId="3" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="2.64656" StatementText="insert into @IdTable&#xD;&#xA;	select distinct tc.nTaskId, cli.nId &#xD;&#xA;		from caDoc_CRM.dbo.tTaskXContact tc&#xD;&#xA;		inner join caDoc_CRM.dbo.tContact con on tc.nContactId = con.nId&#xD;&#xA;			and (@ContactId is null or (@ContactId is not null and con.nId = @ContactId))&#xD;&#xA;		inner join caDoc_CRM.dbo.tClient cli on con.nIdClient = cli.nId&#xD;&#xA;			and (@ClientId is null or (@ClientId is not null and cli.nId = @ClientId))&#xD;&#xA;		inner join #tmpTaskIds t on tc.nTaskId = t.nTaskId&#xD;&#xA;		where @ClientId is null or (@ClientId is not null and cli.nId = @ClientId)" StatementType="INSERT" QueryHash="0xE8ADD78618D62FAF" QueryPlanHash="0xA4E3A315B761F81B" RetrievedFromCache="true">
          <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" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" MemoryGrant="8992" CachedPlanSize="88" CompileTime="24" CompileCPU="24" CompileMemory="1288">
            <MemoryGrantInfo SerialRequiredMemory="2560" SerialDesiredMemory="8992" RequiredMemory="2560" DesiredMemory="8992" RequestedMemory="8992" GrantWaitTime="0" GrantedMemory="8992" MaxUsedMemory="2288" MaxQueryMemory="3004400" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="104857" EstimatedAvailableDegreeOfParallelism="4" MaxCompileMemory="10254328" />
            <RelOp AvgRowSize="9" EstimateCPU="0.0241964" EstimateIO="0.3026" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="24196.4" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Table Insert" EstimatedTotalSubtreeCost="2.64656">
              <OutputList />
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="15543" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="140" ActualCPUms="140" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
              </RunTimeInformation>
              <Update DMLRequestSort="false">
                <Object Table="[@IdTable]" Storage="RowStore" />
                <SetPredicate>
                  <ScalarOperator ScalarString="[TaskId] = [cadoc_crm].[dbo].[tTaskXContact].[nTaskID] as [tc].[nTaskID],[ClientId] = [cadoc_crm].[dbo].[tClient].[nid] as [cli].[nid]">
                    <ScalarExpressionList>
                      <ScalarOperator>
                        <MultipleAssign>
                          <Assign>
                            <ColumnReference Column="TaskId" />
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nTaskID" />
                              </Identifier>
                            </ScalarOperator>
                          </Assign>
                          <Assign>
                            <ColumnReference Column="ClientId" />
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
                              </Identifier>
                            </ScalarOperator>
                          </Assign>
                        </MultipleAssign>
                      </ScalarOperator>
                    </ScalarExpressionList>
                  </ScalarOperator>
                </SetPredicate>
                <RelOp AvgRowSize="15" EstimateCPU="0.00241964" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="24196.4" LogicalOp="Top" NodeId="1" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="2.31977">
                  <OutputList>
                    <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nTaskID" />
                    <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="15543" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="132" ActualCPUms="132" />
                  </RunTimeInformation>
                  <Top RowCount="true" IsPercent="false" WithTies="false">
                    <TopExpression>
                      <ScalarOperator ScalarString="(0)">
                        <Const ConstValue="(0)" />
                      </ScalarOperator>
                    </TopExpression>
                    <RelOp AvgRowSize="15" EstimateCPU="0.660025" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="24196.4" LogicalOp="Aggregate" NodeId="2" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="2.31735">
                      <OutputList>
                        <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nTaskID" />
                        <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
                      </OutputList>
                      <MemoryFractions Input="0.415423" Output="1" />
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="15543" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="131" ActualCPUms="131" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                      </RunTimeInformation>
                      <Hash>
                        <DefinedValues />
                        <HashKeysBuild>
                          <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nTaskID" />
                          <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
                        </HashKeysBuild>
                        <BuildResidual>
                          <ScalarOperator ScalarString="[cadoc_crm].[dbo].[tTaskXContact].[nTaskID] as [tc].[nTaskID] = [cadoc_crm].[dbo].[tTaskXContact].[nTaskID] as [tc].[nTaskID] AND [cadoc_crm].[dbo].[tClient].[nid] as [cli].[nid] = [cadoc_crm].[dbo].[tClient].[nid] as [cli].[nid]">
                            <Logical Operation="AND">
                              <ScalarOperator>
                                <Compare CompareOp="IS">
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nTaskID" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nTaskID" />
                                    </Identifier>
                                  </ScalarOperator>
                                </Compare>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Compare CompareOp="IS">
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
                                    </Identifier>
                                  </ScalarOperator>
                                </Compare>
                              </ScalarOperator>
                            </Logical>
                          </ScalarOperator>
                        </BuildResidual>
                        <RelOp AvgRowSize="15" EstimateCPU="0.798054" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="24196.4" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1.65732">
                          <OutputList>
                            <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nTaskID" />
                            <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
                          </OutputList>
                          <MemoryFractions Input="0.749574" Output="0.547264" />
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="24238" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="110" ActualCPUms="110" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                          </RunTimeInformation>
                          <Hash>
                            <DefinedValues />
                            <HashKeysBuild>
                              <ColumnReference Table="#tmpTaskIds" Alias="[t]" Column="nTaskId" />
                            </HashKeysBuild>
                            <HashKeysProbe>
                              <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nTaskID" />
                            </HashKeysProbe>
                            <ProbeResidual>
                              <ScalarOperator ScalarString="[cadoc_crm].[dbo].[tTaskXContact].[nTaskID] as [tc].[nTaskID]=#tmpTaskIds.[nTaskId] as [t].[nTaskId]">
                                <Compare CompareOp="EQ">
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nTaskID" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Table="#tmpTaskIds" Alias="[t]" Column="nTaskId" />
                                    </Identifier>
                                  </ScalarOperator>
                                </Compare>
                              </ScalarOperator>
                            </ProbeResidual>
                            <RelOp AvgRowSize="11" EstimateCPU="0.0172554" EstimateIO="0.0209028" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="15544" LogicalOp="Table Scan" NodeId="4" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0381582" TableCardinality="15544">
                              <OutputList>
                                <ColumnReference Table="#tmpTaskIds" Alias="[t]" Column="nTaskId" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="0" ActualRows="15544" ActualRowsRead="15544" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="4" ActualCPUms="4" ActualScans="1" ActualLogicalReads="25" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                              </RunTimeInformation>
                              <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Table="#tmpTaskIds" Alias="[t]" Column="nTaskId" />
                                  </DefinedValue>
                                </DefinedValues>
                                <Object Database="[tempdb]" Schema="[dbo]" Table="[#tmpTaskIds]" Alias="[t]" Storage="RowStore" />
                              </TableScan>
                            </RelOp>
                            <RelOp AvgRowSize="15" EstimateCPU="0.190786" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="82213" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="0.821105">
                              <OutputList>
                                <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nTaskID" />
                                <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="0" ActualRows="82213" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="61" ActualCPUms="61" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                              </RunTimeInformation>
                              <Merge ManyToMany="false">
                                <InnerSideJoinColumns>
                                  <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nContactID" />
                                </InnerSideJoinColumns>
                                <OuterSideJoinColumns>
                                  <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tContact]" Alias="[con]" Column="nid" />
                                </OuterSideJoinColumns>
                                <Residual>
                                  <ScalarOperator ScalarString="[cadoc_crm].[dbo].[tTaskXContact].[nContactID] as [tc].[nContactID]=[cadoc_crm].[dbo].[tContact].[nid] as [con].[nid]">
                                    <Compare CompareOp="EQ">
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nContactID" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tContact]" Alias="[con]" Column="nid" />
                                        </Identifier>
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                </Residual>
                                <RelOp AvgRowSize="15" EstimateCPU="0.035399" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2056" LogicalOp="Sort" NodeId="6" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.358081">
                                  <OutputList>
                                    <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tContact]" Alias="[con]" Column="nid" />
                                    <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
                                  </OutputList>
                                  <MemoryFractions Input="0.0511073" Output="0.0373134" />
                                  <RunTimeInformation>
                                    <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1862" ActualEndOfScans="0" ActualExecutions="1" ActualElapsedms="23" ActualCPUms="23" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                  </RunTimeInformation>
                                  <Sort Distinct="false">
                                    <OrderBy>
                                      <OrderByColumn Ascending="true">
                                        <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tContact]" Alias="[con]" Column="nid" />
                                      </OrderByColumn>
                                    </OrderBy>
                                    <RelOp AvgRowSize="15" EstimateCPU="0.170723" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2056" LogicalOp="Inner Join" NodeId="7" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.311421">
                                      <OutputList>
                                        <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tContact]" Alias="[con]" Column="nid" />
                                        <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
                                      </OutputList>
                                      <MemoryFractions Input="0.250426" Output="0.199319" />
                                      <RunTimeInformation>
                                        <RunTimeCountersPerThread Thread="0" ActualRows="2056" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="22" ActualCPUms="22" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                      </RunTimeInformation>
                                      <Hash>
                                        <DefinedValues />
                                        <HashKeysBuild>
                                          <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tContact]" Alias="[con]" Column="nidClient" />
                                        </HashKeysBuild>
                                        <HashKeysProbe>
                                          <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
                                        </HashKeysProbe>
                                        <RelOp AvgRowSize="15" EstimateCPU="0.0024186" EstimateIO="0.00608796" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2056" LogicalOp="Index Scan" NodeId="8" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.00850656" TableCardinality="2056">
                                          <OutputList>
                                            <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tContact]" Alias="[con]" Column="nid" />
                                            <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tContact]" Alias="[con]" Column="nidClient" />
                                          </OutputList>
                                          <RunTimeInformation>
                                            <RunTimeCountersPerThread Thread="0" ActualRows="2056" ActualRowsRead="2056" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="2" ActualCPUms="2" ActualScans="1" ActualLogicalReads="7" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                          </RunTimeInformation>
                                          <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                            <DefinedValues>
                                              <DefinedValue>
                                                <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tContact]" Alias="[con]" Column="nid" />
                                              </DefinedValue>
                                              <DefinedValue>
                                                <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tContact]" Alias="[con]" Column="nidClient" />
                                              </DefinedValue>
                                            </DefinedValues>
                                            <Object Database="[cadoc_crm]" Schema="[dbo]" Table="[tContact]" Index="[IX_tContact_nidClient]" Alias="[con]" IndexKind="NonClustered" Storage="RowStore" />
                                            <Predicate>
                                              <ScalarOperator ScalarString="[@ContactId] IS NULL OR [@ContactId] IS NOT NULL AND [cadoc_crm].[dbo].[tContact].[nid] as [con].[nid]=[@ContactId]">
                                                <Logical Operation="OR">
                                                  <ScalarOperator>
                                                    <Identifier>
                                                      <ColumnReference Column="ConstExpr1008">
                                                        <ScalarOperator>
                                                          <Compare CompareOp="IS">
                                                            <ScalarOperator>
                                                              <Identifier>
                                                                <ColumnReference Column="@ContactId" />
                                                              </Identifier>
                                                            </ScalarOperator>
                                                            <ScalarOperator>
                                                              <Const ConstValue="NULL" />
                                                            </ScalarOperator>
                                                          </Compare>
                                                        </ScalarOperator>
                                                      </ColumnReference>
                                                    </Identifier>
                                                  </ScalarOperator>
                                                  <ScalarOperator>
                                                    <Logical Operation="AND">
                                                      <ScalarOperator>
                                                        <Identifier>
                                                          <ColumnReference Column="ConstExpr1009">
                                                            <ScalarOperator>
                                                              <Compare CompareOp="IS NOT">
                                                                <ScalarOperator>
                                                                  <Identifier>
                                                                    <ColumnReference Column="@ContactId" />
                                                                  </Identifier>
                                                                </ScalarOperator>
                                                                <ScalarOperator>
                                                                  <Const ConstValue="NULL" />
                                                                </ScalarOperator>
                                                              </Compare>
                                                            </ScalarOperator>
                                                          </ColumnReference>
                                                        </Identifier>
                                                      </ScalarOperator>
                                                      <ScalarOperator>
                                                        <Compare CompareOp="EQ">
                                                          <ScalarOperator>
                                                            <Identifier>
                                                              <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tContact]" Alias="[con]" Column="nid" />
                                                            </Identifier>
                                                          </ScalarOperator>
                                                          <ScalarOperator>
                                                            <Identifier>
                                                              <ColumnReference Column="@ContactId" />
                                                            </Identifier>
                                                          </ScalarOperator>
                                                        </Compare>
                                                      </ScalarOperator>
                                                    </Logical>
                                                  </ScalarOperator>
                                                </Logical>
                                              </ScalarOperator>
                                            </Predicate>
                                          </IndexScan>
                                        </RelOp>
                                        <RelOp AvgRowSize="11" EstimateCPU="0.0308096" EstimateIO="0.0527546" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="27866" LogicalOp="Index Scan" NodeId="9" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.0835642" TableCardinality="27866">
                                          <OutputList>
                                            <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
                                          </OutputList>
                                          <RunTimeInformation>
                                            <RunTimeCountersPerThread Thread="0" ActualRows="27866" ActualRowsRead="27866" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="11" ActualCPUms="11" ActualScans="1" ActualLogicalReads="70" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                          </RunTimeInformation>
                                          <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                            <DefinedValues>
                                              <DefinedValue>
                                                <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
                                              </DefinedValue>
                                            </DefinedValues>
                                            <Object Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Index="[IX_cUDF3]" Alias="[cli]" IndexKind="NonClustered" Storage="RowStore" />
                                            <Predicate>
                                              <ScalarOperator ScalarString="([@ClientId] IS NULL OR [@ClientId] IS NOT NULL AND [cadoc_crm].[dbo].[tClient].[nid] as [cli].[nid]=[@ClientId]) AND ([@ClientId] IS NULL OR [@ClientId] IS NOT NULL AND [cadoc_crm].[dbo].[tClient].[nid] as [cli].[nid]=[@ClientId])">
                                                <Logical Operation="AND">
                                                  <ScalarOperator>
                                                    <Logical Operation="OR">
                                                      <ScalarOperator>
                                                        <Identifier>
                                                          <ColumnReference Column="ConstExpr1010">
                                                            <ScalarOperator>
                                                              <Compare CompareOp="IS">
                                                                <ScalarOperator>
                                                                  <Identifier>
                                                                    <ColumnReference Column="@ClientId" />
                                                                  </Identifier>
                                                                </ScalarOperator>
                                                                <ScalarOperator>
                                                                  <Const ConstValue="NULL" />
                                                                </ScalarOperator>
                                                              </Compare>
                                                            </ScalarOperator>
                                                          </ColumnReference>
                                                        </Identifier>
                                                      </ScalarOperator>
                                                      <ScalarOperator>
                                                        <Logical Operation="AND">
                                                          <ScalarOperator>
                                                            <Identifier>
                                                              <ColumnReference Column="ConstExpr1011">
                                                                <ScalarOperator>
                                                                  <Compare CompareOp="IS NOT">
                                                                    <ScalarOperator>
                                                                      <Identifier>
                                                                        <ColumnReference Column="@ClientId" />
                                                                      </Identifier>
                                                                    </ScalarOperator>
                                                                    <ScalarOperator>
                                                                      <Const ConstValue="NULL" />
                                                                    </ScalarOperator>
                                                                  </Compare>
                                                                </ScalarOperator>
                                                              </ColumnReference>
                                                            </Identifier>
                                                          </ScalarOperator>
                                                          <ScalarOperator>
                                                            <Compare CompareOp="EQ">
                                                              <ScalarOperator>
                                                                <Identifier>
                                                                  <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
                                                                </Identifier>
                                                              </ScalarOperator>
                                                              <ScalarOperator>
                                                                <Identifier>
                                                                  <ColumnReference Column="@ClientId" />
                                                                </Identifier>
                                                              </ScalarOperator>
                                                            </Compare>
                                                          </ScalarOperator>
                                                        </Logical>
                                                      </ScalarOperator>
                                                    </Logical>
                                                  </ScalarOperator>
                                                  <ScalarOperator>
                                                    <Logical Operation="OR">
                                                      <ScalarOperator>
                                                        <Identifier>
                                                          <ColumnReference Column="ConstExpr1012">
                                                            <ScalarOperator>
                                                              <Compare CompareOp="IS">
                                                                <ScalarOperator>
                                                                  <Identifier>
                                                                    <ColumnReference Column="@ClientId" />
                                                                  </Identifier>
                                                                </ScalarOperator>
                                                                <ScalarOperator>
                                                                  <Const ConstValue="NULL" />
                                                                </ScalarOperator>
                                                              </Compare>
                                                            </ScalarOperator>
                                                          </ColumnReference>
                                                        </Identifier>
                                                      </ScalarOperator>
                                                      <ScalarOperator>
                                                        <Logical Operation="AND">
                                                          <ScalarOperator>
                                                            <Identifier>
                                                              <ColumnReference Column="ConstExpr1013">
                                                                <ScalarOperator>
                                                                  <Compare CompareOp="IS NOT">
                                                                    <ScalarOperator>
                                                                      <Identifier>
                                                                        <ColumnReference Column="@ClientId" />
                                                                      </Identifier>
                                                                    </ScalarOperator>
                                                                    <ScalarOperator>
                                                                      <Const ConstValue="NULL" />
                                                                    </ScalarOperator>
                                                                  </Compare>
                                                                </ScalarOperator>
                                                              </ColumnReference>
                                                            </Identifier>
                                                          </ScalarOperator>
                                                          <ScalarOperator>
                                                            <Compare CompareOp="EQ">
                                                              <ScalarOperator>
                                                                <Identifier>
                                                                  <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[cli]" Column="nid" />
                                                                </Identifier>
                                                              </ScalarOperator>
                                                              <ScalarOperator>
                                                                <Identifier>
                                                                  <ColumnReference Column="@ClientId" />
                                                                </Identifier>
                                                              </ScalarOperator>
                                                            </Compare>
                                                          </ScalarOperator>
                                                        </Logical>
                                                      </ScalarOperator>
                                                    </Logical>
                                                  </ScalarOperator>
                                                </Logical>
                                              </ScalarOperator>
                                            </Predicate>
                                          </IndexScan>
                                        </RelOp>
                                      </Hash>
                                    </RelOp>
                                  </Sort>
                                </RelOp>
                                <RelOp AvgRowSize="15" EstimateCPU="0.0905913" EstimateIO="0.181644" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="82213" LogicalOp="Index Scan" NodeId="11" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.272235" TableCardinality="82213">
                                  <OutputList>
                                    <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nTaskID" />
                                    <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nContactID" />
                                  </OutputList>
                                  <RunTimeInformation>
                                    <RunTimeCountersPerThread Thread="0" ActualRows="82213" ActualRowsRead="82213" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="24" ActualCPUms="24" ActualScans="1" ActualLogicalReads="244" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                  </RunTimeInformation>
                                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nTaskID" />
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Alias="[tc]" Column="nContactID" />
                                      </DefinedValue>
                                    </DefinedValues>
                                    <Object Database="[cadoc_crm]" Schema="[dbo]" Table="[tTaskXContact]" Index="[IX_tTaskXContact_nContactID_lPrimary]" Alias="[tc]" IndexKind="NonClustered" Storage="RowStore" />
                                  </IndexScan>
                                </RelOp>
                              </Merge>
                            </RelOp>
                          </Hash>
                        </RelOp>
                      </Hash>
                    </RelOp>
                  </Top>
                </RelOp>
              </Update>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@ClientId" ParameterCompiledValue="NULL" ParameterRuntimeValue="NULL" />
              <ColumnReference Column="@ContactId" ParameterCompiledValue="NULL" ParameterRuntimeValue="NULL" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="21" StatementEstRows="1" StatementId="4" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.0297982" StatementText="delete from @IdTable&#xD;&#xA;	where ClientId = @ClientBaseId&#xD;&#xA;		and TaskId in (select TaskId from @IdTable where ClientId != @ClientBaseId)" StatementType="DELETE" QueryHash="0xC2480033B6C0C59A" QueryPlanHash="0x14853FB2BB609876" RetrievedFromCache="true">
          <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" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="24" CompileTime="11" CompileCPU="11" CompileMemory="496">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="104857" EstimatedAvailableDegreeOfParallelism="4" MaxCompileMemory="10254952" />
            <RelOp AvgRowSize="9" EstimateCPU="1E-06" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Delete" NodeId="0" Parallel="false" PhysicalOp="Table Delete" EstimatedTotalSubtreeCost="0.0297982">
              <OutputList />
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="15538" ActualRowsRead="15538" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="53338" ActualCPUms="53333" ActualScans="0" ActualLogicalReads="15538" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
              </RunTimeInformation>
              <Update DMLRequestSort="false">
                <Object Table="[@IdTable]" Storage="RowStore" />
                <RelOp AvgRowSize="15" EstimateCPU="0.00010046" EstimateIO="0.013125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Eager Spool" NodeId="1" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0.0197972">
                  <OutputList>
                    <ColumnReference Column="Bmk1000" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="15538" ActualRowsRead="15538" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="53276" ActualCPUms="53270" ActualScans="1" ActualLogicalReads="31366" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                  </RunTimeInformation>
                  <Spool>
                    <RelOp AvgRowSize="15" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Top" NodeId="2" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.00657174">
                      <OutputList>
                        <ColumnReference Column="Bmk1000" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="15538" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="53150" ActualCPUms="53145" />
                      </RunTimeInformation>
                      <Top RowCount="true" IsPercent="false" WithTies="false">
                        <TopExpression>
                          <ScalarOperator ScalarString="(0)">
                            <Const ConstValue="(0)" />
                          </ScalarOperator>
                        </TopExpression>
                        <RelOp AvgRowSize="15" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Left Semi Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657164">
                          <OutputList>
                            <ColumnReference Column="Bmk1000" />
                          </OutputList>
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="0" ActualRows="15538" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="53144" ActualCPUms="53139" />
                          </RunTimeInformation>
                          <NestedLoops Optimized="false">
                            <Predicate>
                              <ScalarOperator ScalarString="[TaskId]=[TaskId]">
                                <Compare CompareOp="EQ">
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Column="TaskId" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator>
                                    <Identifier>
                                      <ColumnReference Column="TaskId" />
                                    </Identifier>
                                  </ScalarOperator>
                                </Compare>
                              </ScalarOperator>
                            </Predicate>
                            <RelOp AvgRowSize="23" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Table Scan" NodeId="4" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
                              <OutputList>
                                <ColumnReference Column="Bmk1000" />
                                <ColumnReference Column="TaskId" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="0" ActualRows="15540" ActualRowsRead="31082" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="13" ActualCPUms="13" ActualScans="1" ActualLogicalReads="67" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                              </RunTimeInformation>
                              <TableScan Ordered="true" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Column="Bmk1000" />
                                  </DefinedValue>
                                  <DefinedValue>
                                    <ColumnReference Column="TaskId" />
                                  </DefinedValue>
                                </DefinedValues>
                                <Object Table="[@IdTable]" TableReferenceId="1" Storage="RowStore" />
                                <Predicate>
                                  <ScalarOperator ScalarString="[ClientId]=[@ClientBaseId]">
                                    <Compare CompareOp="EQ">
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Column="ClientId" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Column="@ClientBaseId" />
                                        </Identifier>
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                </Predicate>
                              </TableScan>
                            </RelOp>
                            <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Table Scan" NodeId="5" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
                              <OutputList>
                                <ColumnReference Column="TaskId" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="0" ActualRows="120768792" ActualRowsRead="120799872" ActualEndOfScans="2" ActualExecutions="15540" ActualElapsedms="42020" ActualCPUms="42020" ActualScans="1" ActualLogicalReads="277130" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                              </RunTimeInformation>
                              <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                <DefinedValues>
                                  <DefinedValue>
                                    <ColumnReference Column="TaskId" />
                                  </DefinedValue>
                                </DefinedValues>
                                <Object Table="[@IdTable]" TableReferenceId="2" Storage="RowStore" />
                                <Predicate>
                                  <ScalarOperator ScalarString="[ClientId]&lt;&gt;[@ClientBaseId]">
                                    <Compare CompareOp="NE">
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Column="ClientId" />
                                        </Identifier>
                                      </ScalarOperator>
                                      <ScalarOperator>
                                        <Identifier>
                                          <ColumnReference Column="@ClientBaseId" />
                                        </Identifier>
                                      </ScalarOperator>
                                    </Compare>
                                  </ScalarOperator>
                                </Predicate>
                              </TableScan>
                            </RelOp>
                          </NestedLoops>
                        </RelOp>
                      </Top>
                    </RelOp>
                  </Spool>
                </RelOp>
              </Update>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@ClientBaseId" ParameterCompiledValue="(3)" ParameterRuntimeValue="(3)" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="28" StatementEstRows="54416" StatementId="5" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="10.3282" StatementText="select distinct t.nTaskID&#xD;&#xA;		, t.cTask cTask&#xD;&#xA;		, ISNULL(c.cName, '[unassigned]') cClientName&#xD;&#xA;		, ISNULL(c.nid, 0) nId&#xD;&#xA;	into #tmpTaskResults&#xD;&#xA;	from caDoc_CRM.dbo.tTask t&#xD;&#xA;	left join @IdTable tc on t.nTaskId = tc.TaskId	&#xD;&#xA;	left join caDoc_CRM.dbo.tClient c on tc.ClientId = c.nID" StatementType="SELECT INTO" QueryHash="0x74727E7A6C753C8B" QueryPlanHash="0x38D001F58E23B1D4" RetrievedFromCache="true">
          <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="8" MemoryGrant="29000" CachedPlanSize="96" CompileTime="11" CompileCPU="11" CompileMemory="704">
            <ThreadStat Branches="2" UsedThreads="16">
              <ThreadReservation NodeId="0" ReservedThreads="16" />
            </ThreadStat>
            <MemoryGrantInfo SerialRequiredMemory="1536" SerialDesiredMemory="16776" RequiredMemory="13704" DesiredMemory="29000" RequestedMemory="29000" GrantWaitTime="0" GrantedMemory="29000" MaxUsedMemory="12696" MaxQueryMemory="3004400" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="104857" EstimatedAvailableDegreeOfParallelism="4" MaxCompileMemory="10254952" />
            <RelOp AvgRowSize="9" EstimateCPU="0.054416" EstimateIO="8.35135" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="54416" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Table Insert" EstimatedTotalSubtreeCost="10.3282">
              <OutputList />
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="54416" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="311" ActualCPUms="69" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
              </RunTimeInformation>
              <Update DMLRequestSort="false">
                <Object Table="[#tmpTaskResults]" Storage="RowStore" />
                <SetPredicate>
                  <ScalarOperator ScalarString="[#tmpTaskResults].[nTaskID] = [cadoc_crm].[dbo].[tTask].[nTaskID] as [t].[nTaskID],[#tmpTaskResults].[cTask] = [cadoc_crm].[dbo].[tTask].[cTask] as [t].[cTask],[#tmpTaskResults].[cClientName] = [Expr1007],[#tmpTaskResults].[nId] = [Expr1008]">
                    <ScalarExpressionList>
                      <ScalarOperator>
                        <MultipleAssign>
                          <Assign>
                            <ColumnReference Table="[#tmpTaskResults]" Column="nTaskID" />
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
                              </Identifier>
                            </ScalarOperator>
                          </Assign>
                          <Assign>
                            <ColumnReference Table="[#tmpTaskResults]" Column="cTask" />
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="cTask" />
                              </Identifier>
                            </ScalarOperator>
                          </Assign>
                          <Assign>
                            <ColumnReference Table="[#tmpTaskResults]" Column="cClientName" />
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Column="Expr1007" />
                              </Identifier>
                            </ScalarOperator>
                          </Assign>
                          <Assign>
                            <ColumnReference Table="[#tmpTaskResults]" Column="nId" />
                            <ScalarOperator>
                              <Identifier>
                                <ColumnReference Column="Expr1008" />
                              </Identifier>
                            </ScalarOperator>
                          </Assign>
                        </MultipleAssign>
                      </ScalarOperator>
                    </ScalarExpressionList>
                  </ScalarOperator>
                </SetPredicate>
                <RelOp AvgRowSize="186" EstimateCPU="0.0054416" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="54416" LogicalOp="Top" NodeId="1" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="1.9224">
                  <OutputList>
                    <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
                    <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="cTask" />
                    <ColumnReference Column="Expr1007" />
                    <ColumnReference Column="Expr1008" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="54416" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="274" ActualCPUms="33" />
                  </RunTimeInformation>
                  <Top RowCount="true" IsPercent="false" WithTies="false">
                    <TopExpression>
                      <ScalarOperator ScalarString="(0)">
                        <Const ConstValue="(0)" />
                      </ScalarOperator>
                    </TopExpression>
                    <RelOp AvgRowSize="186" EstimateCPU="0.237968" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="54416" LogicalOp="Gather Streams" NodeId="2" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="1.91696">
                      <OutputList>
                        <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
                        <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="cTask" />
                        <ColumnReference Column="Expr1007" />
                        <ColumnReference Column="Expr1008" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="54416" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="268" ActualCPUms="27" />
                      </RunTimeInformation>
                      <Parallelism>
                        <RelOp AvgRowSize="186" EstimateCPU="0.982352" EstimateIO="0.00281532" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="54416" LogicalOp="Distinct Sort" NodeId="3" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="1.67899">
                          <OutputList>
                            <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
                            <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="cTask" />
                            <ColumnReference Column="Expr1007" />
                            <ColumnReference Column="Expr1008" />
                          </OutputList>
                          <MemoryFractions Input="0.985827" Output="1" />
                          <RunTimeInformation>
                            <RunTimeCountersPerThread Thread="5" ActualRebinds="1" ActualRewinds="0" ActualRows="6850" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="74" ActualCPUms="10" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                            <RunTimeCountersPerThread Thread="7" ActualRebinds="1" ActualRewinds="0" ActualRows="6764" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="75" ActualCPUms="10" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                            <RunTimeCountersPerThread Thread="8" ActualRebinds="1" ActualRewinds="0" ActualRows="6786" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="74" ActualCPUms="9" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                            <RunTimeCountersPerThread Thread="3" ActualRebinds="1" ActualRewinds="0" ActualRows="6885" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="74" ActualCPUms="10" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                            <RunTimeCountersPerThread Thread="6" ActualRebinds="1" ActualRewinds="0" ActualRows="6765" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="75" ActualCPUms="10" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                            <RunTimeCountersPerThread Thread="2" ActualRebinds="1" ActualRewinds="0" ActualRows="6862" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="74" ActualCPUms="10" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                            <RunTimeCountersPerThread Thread="4" ActualRebinds="1" ActualRewinds="0" ActualRows="6812" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="75" ActualCPUms="10" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                            <RunTimeCountersPerThread Thread="1" ActualRebinds="1" ActualRewinds="0" ActualRows="6692" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="74" ActualCPUms="10" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                            <RunTimeCountersPerThread Thread="0" ActualRebinds="0" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" ActualElapsedms="163" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                          </RunTimeInformation>
                          <Sort Distinct="true">
                            <OrderBy>
                              <OrderByColumn Ascending="true">
                                <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
                              </OrderByColumn>
                              <OrderByColumn Ascending="true">
                                <ColumnReference Column="Expr1007" />
                              </OrderByColumn>
                              <OrderByColumn Ascending="true">
                                <ColumnReference Column="Expr1008" />
                              </OrderByColumn>
                            </OrderBy>
                            <RelOp AvgRowSize="186" EstimateCPU="0.277484" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="54416" LogicalOp="Repartition Streams" NodeId="4" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.693826">
                              <OutputList>
                                <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
                                <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="cTask" />
                                <ColumnReference Column="Expr1007" />
                                <ColumnReference Column="Expr1008" />
                              </OutputList>
                              <RunTimeInformation>
                                <RunTimeCountersPerThread Thread="7" ActualRows="6764" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="65" ActualCPUms="1" />
                                <RunTimeCountersPerThread Thread="5" ActualRows="6850" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="65" ActualCPUms="1" />
                                <RunTimeCountersPerThread Thread="8" ActualRows="6787" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="65" ActualCPUms="0" />
                                <RunTimeCountersPerThread Thread="6" ActualRows="6766" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="66" ActualCPUms="1" />
                                <RunTimeCountersPerThread Thread="4" ActualRows="6813" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="65" ActualCPUms="0" />
                                <RunTimeCountersPerThread Thread="3" ActualRows="6885" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="65" ActualCPUms="0" />
                                <RunTimeCountersPerThread Thread="2" ActualRows="6862" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="65" ActualCPUms="1" />
                                <RunTimeCountersPerThread Thread="1" ActualRows="6692" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="65" ActualCPUms="1" />
                                <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" ActualElapsedms="163" ActualCPUms="0" />
                              </RunTimeInformation>
                              <Parallelism PartitioningType="Hash">
                                <PartitionColumns>
                                  <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
                                  <ColumnReference Column="Expr1007" />
                                  <ColumnReference Column="Expr1008" />
                                </PartitionColumns>
                                <RelOp AvgRowSize="186" EstimateCPU="0.0013604" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="54416" LogicalOp="Compute Scalar" NodeId="5" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.416342">
                                  <OutputList>
                                    <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
                                    <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="cTask" />
                                    <ColumnReference Column="Expr1007" />
                                    <ColumnReference Column="Expr1008" />
                                  </OutputList>
                                  <ComputeScalar>
                                    <DefinedValues>
                                      <DefinedValue>
                                        <ColumnReference Column="Expr1007" />
                                        <ScalarOperator ScalarString="isnull([cadoc_crm].[dbo].[tClient].[cName] as [c].[cName],'[unassigned]')">
                                          <Intrinsic FunctionName="isnull">
                                            <ScalarOperator>
                                              <Identifier>
                                                <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="cName" />
                                              </Identifier>
                                            </ScalarOperator>
                                            <ScalarOperator>
                                              <Const ConstValue="'[unassigned]'" />
                                            </ScalarOperator>
                                          </Intrinsic>
                                        </ScalarOperator>
                                      </DefinedValue>
                                      <DefinedValue>
                                        <ColumnReference Column="Expr1008" />
                                        <ScalarOperator ScalarString="isnull([cadoc_crm].[dbo].[tClient].[nid] as [c].[nid],(0))">
                                          <Intrinsic FunctionName="isnull">
                                            <ScalarOperator>
                                              <Identifier>
                                                <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="nid" />
                                              </Identifier>
                                            </ScalarOperator>
                                            <ScalarOperator>
                                              <Const ConstValue="(0)" />
                                            </ScalarOperator>
                                          </Intrinsic>
                                        </ScalarOperator>
                                      </DefinedValue>
                                    </DefinedValues>
                                    <RelOp AvgRowSize="168" EstimateCPU="0.166219" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="54416" LogicalOp="Right Outer Join" NodeId="6" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.414982">
                                      <OutputList>
                                        <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
                                        <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="cTask" />
                                        <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="nid" />
                                        <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="cName" />
                                      </OutputList>
                                      <Warnings>
                                        <SpillToTempDb SpillLevel="1" SpilledThreadCount="8" />
                                        <HashSpillDetails GrantedMemoryKb="23040" UsedMemoryKb="9216" WritesToTempDb="512" ReadsFromTempDb="512" />
                                      </Warnings>
                                      <MemoryFractions Input="1" Output="0.0141732" />
                                      <RunTimeInformation>
                                        <RunTimeCountersPerThread Thread="5" ActualRows="4890" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="225" ActualCPUms="29" ActualScans="4" ActualLogicalReads="16" ActualPhysicalReads="2" ActualReadAheads="64" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                        <RunTimeCountersPerThread Thread="4" ActualRows="7954" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="219" ActualCPUms="34" ActualScans="4" ActualLogicalReads="16" ActualPhysicalReads="2" ActualReadAheads="64" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                        <RunTimeCountersPerThread Thread="7" ActualRows="7937" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="216" ActualCPUms="32" ActualScans="4" ActualLogicalReads="16" ActualPhysicalReads="2" ActualReadAheads="64" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                        <RunTimeCountersPerThread Thread="3" ActualRows="6842" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="222" ActualCPUms="36" ActualScans="4" ActualLogicalReads="16" ActualPhysicalReads="2" ActualReadAheads="64" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                        <RunTimeCountersPerThread Thread="6" ActualRows="6450" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="222" ActualCPUms="31" ActualScans="4" ActualLogicalReads="16" ActualPhysicalReads="2" ActualReadAheads="64" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                        <RunTimeCountersPerThread Thread="2" ActualRows="6167" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="223" ActualCPUms="35" ActualScans="4" ActualLogicalReads="16" ActualPhysicalReads="2" ActualReadAheads="64" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                        <RunTimeCountersPerThread Thread="1" ActualRows="6648" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="221" ActualCPUms="31" ActualScans="4" ActualLogicalReads="16" ActualPhysicalReads="2" ActualReadAheads="64" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                        <RunTimeCountersPerThread Thread="8" ActualRows="7531" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="223" ActualCPUms="32" ActualScans="4" ActualLogicalReads="16" ActualPhysicalReads="2" ActualReadAheads="64" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                        <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                      </RunTimeInformation>
                                      <Hash>
                                        <DefinedValues />
                                        <HashKeysBuild>
                                          <ColumnReference Table="@IdTable" Alias="[tc]" Column="TaskId" />
                                        </HashKeysBuild>
                                        <HashKeysProbe>
                                          <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
                                        </HashKeysProbe>
                                        <ProbeResidual>
                                          <ScalarOperator ScalarString="[cadoc_crm].[dbo].[tTask].[nTaskID] as [t].[nTaskID]=@IdTable.[TaskId] as [tc].[TaskId]">
                                            <Compare CompareOp="EQ">
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
                                                </Identifier>
                                              </ScalarOperator>
                                              <ScalarOperator>
                                                <Identifier>
                                                  <ColumnReference Table="@IdTable" Alias="[tc]" Column="TaskId" />
                                                </Identifier>
                                              </ScalarOperator>
                                            </Compare>
                                          </ScalarOperator>
                                        </ProbeResidual>
                                        <RelOp AvgRowSize="41" EstimateCPU="0.0285049" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Distribute Streams" NodeId="7" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0350753">
                                          <OutputList>
                                            <ColumnReference Table="@IdTable" Alias="[tc]" Column="TaskId" />
                                            <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="nid" />
                                            <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="cName" />
                                          </OutputList>
                                          <RunTimeInformation>
                                            <RunTimeCountersPerThread Thread="5" ActualRows="15544" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="145" ActualCPUms="2" />
                                            <RunTimeCountersPerThread Thread="4" ActualRows="15544" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="144" ActualCPUms="2" />
                                            <RunTimeCountersPerThread Thread="7" ActualRows="15544" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="145" ActualCPUms="1" />
                                            <RunTimeCountersPerThread Thread="3" ActualRows="15544" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="142" ActualCPUms="2" />
                                            <RunTimeCountersPerThread Thread="6" ActualRows="15544" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="146" ActualCPUms="1" />
                                            <RunTimeCountersPerThread Thread="2" ActualRows="15544" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="142" ActualCPUms="2" />
                                            <RunTimeCountersPerThread Thread="1" ActualRows="15544" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="145" ActualCPUms="1" />
                                            <RunTimeCountersPerThread Thread="8" ActualRows="15544" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="147" ActualCPUms="1" />
                                            <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" ActualElapsedms="0" ActualCPUms="0" />
                                          </RunTimeInformation>
                                          <Parallelism PartitioningType="Broadcast">
                                            <RelOp AvgRowSize="41" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Left Outer Join" NodeId="8" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038">
                                              <OutputList>
                                                <ColumnReference Table="@IdTable" Alias="[tc]" Column="TaskId" />
                                                <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="nid" />
                                                <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="cName" />
                                              </OutputList>
                                              <RunTimeInformation>
                                                <RunTimeCountersPerThread Thread="1" ActualRows="15544" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="97" ActualCPUms="90" />
                                                <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" ActualElapsedms="0" ActualCPUms="0" />
                                              </RunTimeInformation>
                                              <NestedLoops Optimized="false">
                                                <OuterReferences>
                                                  <ColumnReference Table="@IdTable" Alias="[tc]" Column="ClientId" />
                                                </OuterReferences>
                                                <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Table Scan" NodeId="9" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
                                                  <OutputList>
                                                    <ColumnReference Table="@IdTable" Alias="[tc]" Column="TaskId" />
                                                    <ColumnReference Table="@IdTable" Alias="[tc]" Column="ClientId" />
                                                  </OutputList>
                                                  <RunTimeInformation>
                                                    <RunTimeCountersPerThread Thread="1" ActualRows="15544" ActualRowsRead="15544" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="8" ActualCPUms="8" ActualScans="1" ActualLogicalReads="38" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                                    <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                                  </RunTimeInformation>
                                                  <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                                    <DefinedValues>
                                                      <DefinedValue>
                                                        <ColumnReference Table="@IdTable" Alias="[tc]" Column="TaskId" />
                                                      </DefinedValue>
                                                      <DefinedValue>
                                                        <ColumnReference Table="@IdTable" Alias="[tc]" Column="ClientId" />
                                                      </DefinedValue>
                                                    </DefinedValues>
                                                    <Object Table="[@IdTable]" Alias="[tc]" Storage="RowStore" />
                                                  </TableScan>
                                                </RelOp>
                                                <RelOp AvgRowSize="37" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="10" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="27866">
                                                  <OutputList>
                                                    <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="nid" />
                                                    <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="cName" />
                                                  </OutputList>
                                                  <RunTimeInformation>
                                                    <RunTimeCountersPerThread Thread="1" ActualRows="15544" ActualRowsRead="15544" Batches="0" ActualEndOfScans="0" ActualExecutions="15544" ActualExecutionMode="Row" ActualElapsedms="71" ActualCPUms="64" ActualScans="0" ActualLogicalReads="46632" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                                    <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                                  </RunTimeInformation>
                                                  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                                    <DefinedValues>
                                                      <DefinedValue>
                                                        <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="nid" />
                                                      </DefinedValue>
                                                      <DefinedValue>
                                                        <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="cName" />
                                                      </DefinedValue>
                                                    </DefinedValues>
                                                    <Object Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Index="[PK_tClient]" Alias="[c]" IndexKind="Clustered" Storage="RowStore" />
                                                    <SeekPredicates>
                                                      <SeekPredicateNew>
                                                        <SeekKeys>
                                                          <Prefix ScanType="EQ">
                                                            <RangeColumns>
                                                              <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tClient]" Alias="[c]" Column="nid" />
                                                            </RangeColumns>
                                                            <RangeExpressions>
                                                              <ScalarOperator ScalarString="@IdTable.[ClientId] as [tc].[ClientId]">
                                                                <Identifier>
                                                                  <ColumnReference Table="@IdTable" Alias="[tc]" Column="ClientId" />
                                                                </Identifier>
                                                              </ScalarOperator>
                                                            </RangeExpressions>
                                                          </Prefix>
                                                        </SeekKeys>
                                                      </SeekPredicateNew>
                                                    </SeekPredicates>
                                                  </IndexScan>
                                                </RelOp>
                                              </NestedLoops>
                                            </RelOp>
                                          </Parallelism>
                                        </RelOp>
                                        <RelOp AvgRowSize="140" EstimateCPU="0.0150037" EstimateIO="0.198681" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="54416" LogicalOp="Index Scan" NodeId="11" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.213684" TableCardinality="54416">
                                          <OutputList>
                                            <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
                                            <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="cTask" />
                                          </OutputList>
                                          <RunTimeInformation>
                                            <RunTimeCountersPerThread Thread="5" ActualRows="4890" ActualRowsRead="4890" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="19" ActualCPUms="2" ActualScans="1" ActualLogicalReads="96" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                            <RunTimeCountersPerThread Thread="4" ActualRows="7954" ActualRowsRead="7954" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="22" ActualCPUms="4" ActualScans="1" ActualLogicalReads="156" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                            <RunTimeCountersPerThread Thread="7" ActualRows="7937" ActualRowsRead="7937" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="21" ActualCPUms="4" ActualScans="1" ActualLogicalReads="152" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                            <RunTimeCountersPerThread Thread="3" ActualRows="6842" ActualRowsRead="6842" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="21" ActualCPUms="3" ActualScans="1" ActualLogicalReads="132" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                            <RunTimeCountersPerThread Thread="6" ActualRows="6450" ActualRowsRead="6450" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="21" ActualCPUms="3" ActualScans="1" ActualLogicalReads="124" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                            <RunTimeCountersPerThread Thread="2" ActualRows="6166" ActualRowsRead="6166" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="21" ActualCPUms="3" ActualScans="1" ActualLogicalReads="120" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                            <RunTimeCountersPerThread Thread="1" ActualRows="6646" ActualRowsRead="6646" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="20" ActualCPUms="3" ActualScans="1" ActualLogicalReads="131" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                            <RunTimeCountersPerThread Thread="8" ActualRows="7531" ActualRowsRead="7531" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="23" ActualCPUms="4" ActualScans="1" ActualLogicalReads="148" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                            <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="5" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                                          </RunTimeInformation>
                                          <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                            <DefinedValues>
                                              <DefinedValue>
                                                <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="nTaskID" />
                                              </DefinedValue>
                                              <DefinedValue>
                                                <ColumnReference Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Alias="[t]" Column="cTask" />
                                              </DefinedValue>
                                            </DefinedValues>
                                            <Object Database="[cadoc_crm]" Schema="[dbo]" Table="[tTask]" Index="[IX_cTask]" Alias="[t]" IndexKind="NonClustered" Storage="RowStore" />
                                          </IndexScan>
                                        </RelOp>
                                      </Hash>
                                    </RelOp>
                                  </ComputeScalar>
                                </RelOp>
                              </Parallelism>
                            </RelOp>
                          </Sort>
                        </RelOp>
                      </Parallelism>
                    </RelOp>
                  </Top>
                </RelOp>
              </Update>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="32" StatementEstRows="54416" StatementId="6" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.340177" StatementText="select * from #tmpTaskResults" StatementType="SELECT" QueryHash="0x7E910497C022928A" QueryPlanHash="0x2DB185700FE9C196" RetrievedFromCache="true">
          <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="1" CachedPlanSize="8" CompileTime="0" CompileCPU="0" CompileMemory="72">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="104857" EstimatedAvailableDegreeOfParallelism="4" MaxCompileMemory="10251024" />
            <RelOp AvgRowSize="186" EstimateCPU="0.0599361" EstimateIO="0.280241" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="54416" LogicalOp="Table Scan" NodeId="0" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.340177" TableCardinality="54416">
              <OutputList>
                <ColumnReference Table="[#tmpTaskResults]" Column="nTaskID" />
                <ColumnReference Table="[#tmpTaskResults]" Column="cTask" />
                <ColumnReference Table="[#tmpTaskResults]" Column="cClientName" />
                <ColumnReference Table="[#tmpTaskResults]" Column="nId" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="54416" ActualRowsRead="54416" ActualEndOfScans="1" ActualExecutions="1" ActualElapsedms="35" ActualCPUms="35" ActualScans="1" ActualLogicalReads="376" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
              </RunTimeInformation>
              <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Table="[#tmpTaskResults]" Column="nTaskID" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Table="[#tmpTaskResults]" Column="cTask" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Table="[#tmpTaskResults]" Column="cClientName" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Table="[#tmpTaskResults]" Column="nId" />
                  </DefinedValue>
                </DefinedValues>
                <Object Database="[tempdb]" Schema="[dbo]" Table="[#tmpTaskResults]" Storage="RowStore" />
              </TableScan>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Open in new window

Avatar of Primoz Ivancic
Primoz Ivancic

What was the code before the changes - there's a lot happening to just figure out the slow parts by scanning all the code.

If your code is not doing something dodgy like loop (which it doesn't appear to), it is almost always related to indexes. I can't read your execution plan, but if you find the bottlenecks, add indexes to the relevant tables - this includes your temporary tables. Basically any 'where' clauses should be indexed (depending on the amount of data).

Other stuff that may help is reducing the amount of ORs used so try something like:
from
where @ClientId is null or (@ClientId is not null and cli.nId = @ClientId)
into
where cli.nId = ISNULL(@ClientId, cli.nId)
Avatar of Robb Hill

ASKER

it seems query 5 is about 67 % of the batch.   thats an insert into a temp that is joining a table to a temp table.  

i think the issue is here.  

no idea how to speed up that structure.
no code changes, no index changes, no missing indexes in dmv
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Pawan..what were your thoughts to speed this up?
also if i change the null statements does that provide same logic
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ok i will test now.   sounds good.   hopefully!  will respond in 30. minutes
Pawan:

That just went from almost a mintute to 3 seconds...Awesome.

To Primoz's point earlier..should we also incorporate the idea here:

Other stuff that may help is reducing the amount of ORs used so try something like:
from
where @ClientId is null or (@ClientId is not null and cli.nId = @ClientId)
into
where cli.nId = ISNULL(@ClientId, cli.nId)


will this give same output or potentially have unexpected behavior.


Thanks again!!!!!!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok thanks again.
Glad to help!
You are a life saver!!!! Thanks Pawan Kumar and thanks for explaining.  Its good to understand the pros and the cons.  I have inherited a very old database and there is alot of outdated queries that are just now having noticable performance issues.
Other stuff that may help is reducing the amount of ORs used so try something like:
from
where @ClientId is null or (@ClientId is not null and cli.nId = @ClientId)
into
where cli.nId = ISNULL(@ClientId, cli.nId)

I'm afraid this is NOT a good performance tuning objective.
Adding ISNULL() does NOT improve performance, it potentially makes it much worse because it forces table scans instead of access to indexes



as a general rule AVOID using functions in joins and where clause .... ISNULL()   is a function