January 11, 2012 at 4:14 pm
Each of my clients have their own code for the same product.
Client 1 calls a widget WIG
Client 2 calls a widget Main...etc.
To simplify life on our end, I am standardizing the names of each of these products. Also to save space (which I am sure is not going to be much) I am using ID fields to store each product name on my transactions table, and link these back to a driver table.
Create Table dbo.Clients
(Client_ID Int Identity(1,1)
,ClientName Varchar(100))
Go
Insert Into dbo.Clients
(ClientName)
Select 'Achme Widget'
Union All
Select 'Widegts R Us'
Go
Create Table dbo.ProductDriver
(Prod_ID Int Identity(1,1)
,Client_ID Int
,Client_Prod_Name Varchar(100)
,Our_Prod_Name Varchar(100))
Go
Insert Into dbo.ProductDriver
(Client_ID
,Client_Prod_Name
,Our_Prod_Name)
Select 1,'WIG','Widget'
Union All
Select 2,'Main','Widget'
Go
I am bringing in data from one of our client's feeds, and am trying to convert this. My process (Real code because it won't give away any company sensitive data, and I don't have time to recreate for the sample data given above right now.):
--------------------
--=== Update LoanType on dbo.Loan_Stage
Update LS
Set LS.LoanType =
(Select LT.Loan_Type_ID
From dbo.Loan_Types LT
Where LS.LoanType = LT.CSO_LoanType_Value)
From dbo.Loan_Stage LS Inner Join dbo.Loan_Types LT
On LS.LoanType = LT.CSO_LoanType_Value
----------
Update LA
Set LA.LoanType = LS.LoanType
,LA.LastUpdate = GetDate()
,LA.LastUpdateBy =
(Select Entity_ID
From dbo.Entity
Where EntityType = 'System'
And Fname = 'Client Name Withheld'
And Lname = 'Import')
From dbo.LoanAcct LA Inner Join dbo.Loan_Stage LS
On LA.LoanNum = LS.LoanNum
And LA.Entity_ID = LS.Entity_ID
And LA.CSO_ID = LS.CSO_ID
Where LA.LoanType <> LS.LoanType
Or LA.LoanType Is Null
This works, but I don't think this is the best way to do it. I have similar logic without the driver table update step. I can run around 4.5k in about a second on the other process. This process runs about 5.7k in 40 seconds. The only real difference is this driver table logic. Any thoughts would be appreciated.
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
January 11, 2012 at 4:49 pm
So the snag might not be in the code I referenced above. I just ran another update on another table using the same logic. I had to complete updates on 3 different columns on over 50k records. This has run in a max of 3 seconds.
Still curious if there is a better way to do this, but I'll have to keep digging to find why my last update takes so much longer.
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
January 11, 2012 at 4:55 pm
Any way you can attach the exec plan for the one that is taking a long time to run?
I think the clues to improving will be found there.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 11, 2012 at 5:05 pm
Ok, here is the full script:
If Object_ID('dbo.Loan_Stage') Is Not Null
Drop Table dbo.Loan_Stage
Go
------------------------------
--=== Populate dbo.Loan_Stage with 1 version of the
-- LoanNum / CustomerID combination. Keep the
-- Max(BatchDate) for update logic used later
Select TC.LoanNum
,Max(TC.BatchDate) As BatchDate
,E.Customer_ID
,E.Entity_ID
,E.CSO_ID
Into dbo.Loan_Stage
From dbo.Temp_Cust TC Inner Join dbo.Entity E
On Ltrim(Rtrim(TC.CustomerID)) = Ltrim(Rtrim(E.Customer_ID))
Where E.EntityType = 'Cust'
And CSO_ID =
(Select CSO_ID
From dbo.CSO
Where CSO = 'Client Name Withheld')
Group By TC.LoanNum
,E.Customer_ID
,E.Entity_ID
,E.CSO_ID
Order By E.Customer_ID
,TC.LoanNum
Go
------------------------------
--=== Add required fields
Alter Table dbo.Loan_Stage
Add Maturity DateTime
,Principal Money
,LoanType Varchar(20)
Go
------------------------------
--=== Update required fields
Update LS
Set LS.Maturity = TC.Maturity
,LS.Principal = TC.Principal
,LS.LoanType = TC.LoanType
From dbo.Loan_Stage LS Inner Join dbo.Temp_Cust TC
On LS.LoanNum = TC.LoanNum
And LS.Customer_ID = TC.CustomerID
And LS.BatchDate = TC.BatchDate
Go
----------------------------------------
--=== Remove Not Null constraint from LastUpdate
-- and LastUpdateBy for the update process.
Alter Table dbo.LoanAcct
Alter Column LastUpdate DateTime Null
Go
----------
Alter Table dbo.LoanAcct
Alter Column LastUpdateBy Int Null
Go
----------------------------------------
--=== Insert only new LoanAcct's via
-- an Except clause.
Insert Into dbo.LoanAcct
(Entity_ID
,LoanNum
,CSO_ID)
Select Entity_ID
,Ltrim(Rtrim(LoanNum))
,CSO_ID
From dbo.Loan_Stage
Except
Select Entity_ID
,Ltrim(Rtrim(LoanNum))
,CSO_ID
From dbo.LoanAcct
Where CSO_ID =
(Select CSO_ID
From dbo.CSO
Where CSO = 'Client Name Withheld')
Go
------------------------------
--=== Add data to LastUpdate before
-- re-adding the Not Null constraint
Update dbo.LoanAcct
Set LastUpdate = GetDate()
Where LastUpdate Is Null
And CSO_ID =
(Select CSO_ID
From dbo.CSO
Where CSO = 'Client Name Withheld')
Go
----------
--=== Go ahead and update LastUpdateBy
-- field where they are Null as well
Update dbo.LoanAcct
Set LastUpdateBy =
(Select Entity_ID
From dbo.Entity
Where EntityType = 'System'
And Fname = 'Client Name Withheld'
And Lname = 'Import')
Where LastUpdateBy Is Null
And CSO_ID =
(Select CSO_ID
From dbo.CSO
Where CSO = 'Client Name Withheld')
Go
----------------------------------------
--=== Reset Not Null constraints
Alter Table dbo.LoanAcct
Alter Column LastUpdate DateTime Not Null
Go
----------
Alter Table dbo.LoanAcct
Alter Column LastUpdateBy Int Not Null
Go
------------------------------------------------------------
--=== At this point all LoanNum's have been loaded, but the
-- new records have Null values for all other required
-- fields. Also we still need to update existing records
-- to match whatever the latest version of the name and other
-- fields values are. This should be able to be completed in
-- one update per each field.
----------
--=== Update Maturity
Update LA
Set LA.Maturity = LS.Maturity
,LA.LastUpdate = GetDate()
,LA.LastUpdateBy =
(Select Entity_ID
From dbo.Entity
Where EntityType = 'System'
And Fname = 'Client Name Withheld'
And Lname = 'Import')
From dbo.LoanAcct LA Inner Join dbo.Loan_Stage LS
On LA.LoanNum = LS.LoanNum
And LA.Entity_ID = LS.Entity_ID
And LA.CSO_ID = LS.CSO_ID
Where LA.Maturity <> LS.Maturity
Or LA.Maturity Is Null
----------
--=== Update Principal
Update LA
Set LA.Principal = LS.Principal
,LA.LastUpdate = GetDate()
,LA.LastUpdateBy =
(Select Entity_ID
From dbo.Entity
Where EntityType = 'System'
And Fname = 'Client Name Withheld'
And Lname = 'Import')
From dbo.LoanAcct LA Inner Join dbo.Loan_Stage LS
On LA.LoanNum = LS.LoanNum
And LA.Entity_ID = LS.Entity_ID
And LA.CSO_ID = LS.CSO_ID
Where LA.Principal <> LS.Principal
Or LA.Principal Is Null
--------------------
--=== Update LoanType on dbo.Loan_Stage
Update LS
Set LS.LoanType =
(Select LT.Loan_Type_ID
From dbo.Loan_Types LT
Where LS.LoanType = LT.CSO_LoanType_Value)
From dbo.Loan_Stage LS Inner Join dbo.Loan_Types LT
On LS.LoanType = LT.CSO_LoanType_Value
----------
Update LA
Set LA.LoanType = LS.LoanType
,LA.LastUpdate = GetDate()
,LA.LastUpdateBy =
(Select Entity_ID
From dbo.Entity
Where EntityType = 'System'
And Fname = 'Client Name Withheld'
And Lname = 'Import')
From dbo.LoanAcct LA Inner Join dbo.Loan_Stage LS
On LA.LoanNum = LS.LoanNum
And LA.Entity_ID = LS.Entity_ID
And LA.CSO_ID = LS.CSO_ID
Where LA.LoanType <> LS.LoanType
Or LA.LoanType Is Null
----------------------------------------
--=== House cleaning
If Object_ID('dbo.Loan_Stage') Is Not Null
Drop Table dbo.Loan_Stage
Go
And here is the execution plan in XML format (sorry, I don't know another way to load that here):
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.1600.1" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="11299.7" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="7.70593" StatementText="Select TC.LoanNum ,Max(TC.BatchDate) As BatchDate ,E.Customer_ID ,E.Entity_ID ,E.CSO_ID Into dbo.Loan_Stage From dbo.Temp_Cust TC Inner Join dbo.Entity E On Ltrim(Rtrim(TC.CustomerID)) = Ltrim(Rtrim(E.Customer_ID)) Where E.EntityType = 'Cust' And CSO_ID = (Select CSO_ID From dbo.CSO Where CSO = 'Client Name Withheld') Group By TC.LoanNum ,E.Customer_ID ,E.Entity_ID ,E.CSO_ID Order By E.Customer_ID ,TC.LoanNum " StatementType="SELECT INTO" QueryHash="0x260AED2DCC7BDB61" QueryPlanHash="0xF8140DC52D05972B">
<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="16" MemoryGrant="54784" CachedPlanSize="128" CompileTime="187" CompileCPU="186" CompileMemory="880">
<RelOp AvgRowSize="9" EstimateCPU="0.0112997" EstimateIO="0.581083" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="11299.7" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Table Insert" EstimatedTotalSubtreeCost="7.70593">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Update DMLRequestSort="false">
<Object Schema="[dbo]" Table="[Loan_Stage]" />
<SetPredicate>
<ScalarOperator ScalarString="[dbo.Loan_Stage].[LoanNum] = [VanGogh_Dev].[dbo].[Temp_Cust].[LoanNum] as [TC].[LoanNum],[dbo.Loan_Stage].[BatchDate] = [Expr1012],[dbo.Loan_Stage].[Customer_ID] = [VanGogh_Dev].[dbo].[Entity].[Customer_ID] as [E].[Customer_ID],[dbo.Loan_Stage].[Entity_ID] = [VanGogh_Dev].[dbo].[Entity].[Entity_ID] as [E].[Entity_ID],[dbo.Loan_Stage].[CSO_ID] = [VanGogh_Dev].[dbo].[Entity].[CSO_ID] as [E].[CSO_ID]">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Table="[dbo.Loan_Stage]" Column="LoanNum" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Table="[dbo.Loan_Stage]" Column="BatchDate" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1012" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Table="[dbo.Loan_Stage]" Column="Customer_ID" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Customer_ID" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Table="[dbo.Loan_Stage]" Column="Entity_ID" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Entity_ID" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Table="[dbo.Loan_Stage]" Column="CSO_ID" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="CSO_ID" />
</Identifier>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="62" EstimateCPU="0.00112997" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="11299.7" LogicalOp="Top" NodeId="1" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="7.11355">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Customer_ID" />
<ColumnReference Column="Expr1012" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="62" EstimateCPU="0.0812196" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="11299.7" LogicalOp="Gather Streams" NodeId="2" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="7.11242">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Customer_ID" />
<ColumnReference Column="Expr1012" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Parallelism>
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Customer_ID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="62" EstimateCPU="0.0873025" EstimateIO="0.00140766" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="11299.7" LogicalOp="Sort" NodeId="3" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="7.0312">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Customer_ID" />
<ColumnReference Column="Expr1012" />
</OutputList>
<MemoryFractions Input="0.563492" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="5" ActualRebinds="1" ActualRewinds="0" ActualRows="335" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="10" ActualRebinds="1" ActualRewinds="0" ActualRows="307" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRebinds="1" ActualRewinds="0" ActualRows="316" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="7" ActualRebinds="1" ActualRewinds="0" ActualRows="324" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="8" ActualRebinds="1" ActualRewinds="0" ActualRows="319" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="4" ActualRebinds="1" ActualRewinds="0" ActualRows="292" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="16" ActualRebinds="1" ActualRewinds="0" ActualRows="320" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="14" ActualRebinds="1" ActualRewinds="0" ActualRows="322" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="13" ActualRebinds="1" ActualRewinds="0" ActualRows="344" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="11" ActualRebinds="1" ActualRewinds="0" ActualRows="348" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="12" ActualRebinds="1" ActualRewinds="0" ActualRows="301" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="9" ActualRebinds="1" ActualRewinds="0" ActualRows="357" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="15" ActualRebinds="1" ActualRewinds="0" ActualRows="308" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="6" ActualRebinds="1" ActualRewinds="0" ActualRows="334" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRebinds="1" ActualRewinds="0" ActualRows="329" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRebinds="1" ActualRewinds="0" ActualRows="323" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRebinds="0" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Customer_ID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="62" EstimateCPU="0.227053" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="11299.7" LogicalOp="Aggregate" NodeId="4" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="6.94249">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Customer_ID" />
<ColumnReference Column="Expr1012" />
</OutputList>
<MemoryFractions Input="0.150582" Output="0.436508" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="5" ActualRows="335" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="10" ActualRows="307" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="316" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="7" ActualRows="324" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="8" ActualRows="319" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="4" ActualRows="292" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="11" ActualRows="348" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="13" ActualRows="344" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="14" ActualRows="322" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="16" ActualRows="320" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="12" ActualRows="301" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="9" ActualRows="357" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="15" ActualRows="308" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="6" ActualRows="334" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="329" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="323" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Hash>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1012" />
<ScalarOperator ScalarString="MAX([partialagg1019])">
<Aggregate AggType="MAX" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Column="partialagg1019" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Customer_ID" />
<ScalarOperator ScalarString="ANY([VanGogh_Dev].[dbo].[Entity].[Customer_ID] as [E].[Customer_ID])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Customer_ID" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="CSO_ID" />
<ScalarOperator ScalarString="ANY([VanGogh_Dev].[dbo].[Entity].[CSO_ID] as [E].[CSO_ID])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="CSO_ID" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<HashKeysBuild>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Entity_ID" />
</HashKeysBuild>
<BuildResidual>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[Temp_Cust].[LoanNum] as [TC].[LoanNum] = [VanGogh_Dev].[dbo].[Temp_Cust].[LoanNum] as [TC].[LoanNum] AND [VanGogh_Dev].[dbo].[Entity].[Entity_ID] as [E].[Entity_ID] = [VanGogh_Dev].[dbo].[Entity].[Entity_ID] as [E].[Entity_ID]">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Entity_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Entity_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</BuildResidual>
<RelOp AvgRowSize="62" EstimateCPU="0.14685" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="90397.8" LogicalOp="Repartition Streams" NodeId="5" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="6.71544">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Customer_ID" />
<ColumnReference Column="partialagg1019" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="8" ActualRows="319" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="4" ActualRows="292" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="10" ActualRows="307" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="12" ActualRows="301" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="13" ActualRows="344" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="14" ActualRows="322" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="16" ActualRows="320" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="15" ActualRows="308" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="11" ActualRows="348" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="9" ActualRows="357" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="323" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="316" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="329" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="7" ActualRows="324" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="5" ActualRows="335" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="6" ActualRows="334" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Entity_ID" />
</PartitionColumns>
<RelOp AvgRowSize="62" EstimateCPU="2.82318" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="90397.8" LogicalOp="Partial Aggregate" NodeId="6" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="6.56859">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Customer_ID" />
<ColumnReference Column="partialagg1019" />
</OutputList>
<MemoryFractions Input="0" Output="0" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="13" ActualRows="322" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="12" ActualRows="278" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="16" ActualRows="320" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="11" ActualRows="319" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="5" ActualRows="323" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="15" ActualRows="341" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="14" ActualRows="311" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="7" ActualRows="297" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="302" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="8" ActualRows="358" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="9" ActualRows="332" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="10" ActualRows="368" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="6" ActualRows="331" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="340" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="305" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="4" ActualRows="332" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Hash>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="partialagg1019" />
<ScalarOperator ScalarString="MAX([VanGogh_Dev].[dbo].[Temp_Cust].[BatchDate] as [TC].[BatchDate])">
<Aggregate AggType="MAX" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="BatchDate" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Customer_ID" />
<ScalarOperator ScalarString="ANY([VanGogh_Dev].[dbo].[Entity].[Customer_ID] as [E].[Customer_ID])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Customer_ID" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="CSO_ID" />
<ScalarOperator ScalarString="ANY([VanGogh_Dev].[dbo].[Entity].[CSO_ID] as [E].[CSO_ID])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="CSO_ID" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<HashKeysBuild>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Entity_ID" />
</HashKeysBuild>
<BuildResidual>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[Temp_Cust].[LoanNum] as [TC].[LoanNum] = [VanGogh_Dev].[dbo].[Temp_Cust].[LoanNum] as [TC].[LoanNum] AND [VanGogh_Dev].[dbo].[Entity].[Entity_ID] as [E].[Entity_ID] = [VanGogh_Dev].[dbo].[Entity].[Entity_ID] as [E].[Entity_ID]">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Entity_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Entity_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</BuildResidual>
<RelOp AvgRowSize="62" EstimateCPU="0.0515868" EstimateIO="7.40189E-05" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1901530" LogicalOp="Inner Join" NodeId="7" Parallel="true" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="3.74541">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="BatchDate" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Customer_ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="13" ActualRows="5219" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="12" ActualRows="4330" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="16" ActualRows="4880" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="11" ActualRows="5428" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="5" ActualRows="4889" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="15" ActualRows="5371" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="14" ActualRows="4682" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="9" ActualRows="5339" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="7" ActualRows="5097" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="10" ActualRows="6277" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="8" ActualRows="5527" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="4760" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="6" ActualRows="5090" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="4" ActualRows="5369" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="5787" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="4968" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Merge ManyToMany="true">
<InnerSideJoinColumns>
<ColumnReference Column="Expr1015" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Column="Expr1016" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[Expr1015]=[Expr1016]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1015" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1016" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="78" EstimateCPU="0.000362685" EstimateIO="0.00140766" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="229.064" LogicalOp="Sort" NodeId="8" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.119405">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Customer_ID" />
<ColumnReference Column="Expr1016" />
</OutputList>
<MemoryFractions Input="0.00805802" Output="0.00684463" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="13" ActualRebinds="1" ActualRewinds="0" ActualRows="251" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="12" ActualRebinds="1" ActualRewinds="0" ActualRows="230" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="16" ActualRebinds="1" ActualRewinds="0" ActualRows="251" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="11" ActualRebinds="1" ActualRewinds="0" ActualRows="261" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="5" ActualRebinds="1" ActualRewinds="0" ActualRows="255" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="15" ActualRebinds="1" ActualRewinds="0" ActualRows="270" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="14" ActualRebinds="1" ActualRewinds="0" ActualRows="243" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="9" ActualRebinds="1" ActualRewinds="0" ActualRows="275" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="10" ActualRebinds="1" ActualRewinds="0" ActualRows="291" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="8" ActualRebinds="1" ActualRewinds="0" ActualRows="283" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="7" ActualRebinds="1" ActualRewinds="0" ActualRows="234" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="6" ActualRebinds="1" ActualRewinds="0" ActualRows="254" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRebinds="1" ActualRewinds="0" ActualRows="262" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="4" ActualRebinds="1" ActualRewinds="0" ActualRows="260" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRebinds="1" ActualRewinds="0" ActualRows="242" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRebinds="1" ActualRewinds="0" ActualRows="223" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRebinds="0" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Column="Expr1016" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="78" EstimateCPU="0.0288105" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="229.064" LogicalOp="Repartition Streams" NodeId="9" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.117634">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Customer_ID" />
<ColumnReference Column="Expr1016" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="13" ActualRows="251" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="12" ActualRows="230" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="16" ActualRows="251" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="11" ActualRows="261" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="5" ActualRows="255" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="15" ActualRows="270" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="14" ActualRows="243" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="8" ActualRows="283" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="9" ActualRows="275" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="7" ActualRows="234" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="10" ActualRows="291" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="6" ActualRows="254" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="262" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="4" ActualRows="260" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="242" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="223" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Column="Expr1016" />
</PartitionColumns>
<RelOp AvgRowSize="78" EstimateCPU="0.00213441" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="229.064" LogicalOp="Inner Join" NodeId="10" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0888237">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Customer_ID" />
<ColumnReference Column="Expr1016" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="5" ActualRows="249" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="14" ActualRows="307" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="15" ActualRows="185" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="13" ActualRows="310" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="250" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="16" ActualRows="163" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="12" ActualRows="309" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="11" ActualRows="252" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="9" ActualRows="370" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="10" ActualRows="352" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="8" ActualRows="63" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="7" ActualRows="313" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="6" ActualRows="309" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="4" ActualRows="35" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="307" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="311" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<Predicate>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[Entity].[CSO_ID] as [E].[CSO_ID]=[Expr1014]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="CSO_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1014" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
<RelOp AvgRowSize="11" EstimateCPU="0.0285019" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Distribute Streams" NodeId="11" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0318171">
<OutputList>
<ColumnReference Column="Expr1014" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="14" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="13" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="16" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="15" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="12" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="11" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="9" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="10" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="8" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="7" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="6" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="4" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="5" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Parallelism PartitioningType="Broadcast">
<RelOp AvgRowSize="11" EstimateCPU="4.8E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Assert" NodeId="12" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="0.00331518">
<OutputList>
<ColumnReference Column="Expr1014" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="1" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Assert StartupExpression="false">
<RelOp AvgRowSize="19" EstimateCPU="1.1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="13" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.0033147">
<OutputList>
<ColumnReference Column="Expr1013" />
<ColumnReference Column="Expr1014" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="1" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1013" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1014" />
<ScalarOperator ScalarString="ANY([VanGogh_Dev].[dbo].[CSO].[CSO_ID])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[CSO]" Column="CSO_ID" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="28" EstimateCPU="0.000179" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="14" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.003304" TableCardinality="20">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[CSO]" Column="CSO_ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="1" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[CSO]" Column="CSO_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[CSO]" Index="[PK__CSO__6B727A9137510C18]" IndexKind="Clustered" />
<Predicate>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[CSO].[CSO]='Client Name Withheld'">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[CSO]" Column="CSO" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'Client Name Withheld'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</StreamAggregate>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN [Expr1013]>(1) THEN (0) ELSE NULL END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1013" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Predicate>
</Assert>
</RelOp>
</Parallelism>
</RelOp>
<RelOp AvgRowSize="78" EstimateCPU="5.10625E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4085" LogicalOp="Compute Scalar" NodeId="19" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0536467">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Customer_ID" />
<ColumnReference Column="Expr1016" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1016" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(50),ltrim(rtrim([VanGogh_Dev].[dbo].[Entity].[Customer_ID] as [E].[Customer_ID])),0)">
<Convert DataType="nvarchar" Length="100" Style="0" Implicit="true">
<ScalarOperator>
<Intrinsic FunctionName="ltrim">
<ScalarOperator>
<Intrinsic FunctionName="rtrim">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Customer_ID" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="32" EstimateCPU="0.000591487" EstimateIO="0.0527546" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4085" LogicalOp="Clustered Index Scan" NodeId="20" Parallel="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0533461" TableCardinality="4159">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Customer_ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="5" ActualRows="249" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="14" ActualRows="307" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="15" ActualRows="185" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="13" ActualRows="310" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="250" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="16" ActualRows="163" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="12" ActualRows="309" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="11" ActualRows="252" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="9" ActualRows="370" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="10" ActualRows="352" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="8" ActualRows="63" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="7" ActualRows="313" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="6" ActualRows="309" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="4" ActualRows="35" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="307" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="311" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Entity_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="CSO_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="Customer_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Index="[PK__Entity__9BC0DE463B219CFC]" Alias="[E]" IndexKind="Clustered" />
<Predicate>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[Entity].[EntityType] as [E].[EntityType]='Cust'">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Alias="[E]" Column="EntityType" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'Cust'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
</NestedLoops>
</RelOp>
</Parallelism>
</RelOp>
</Sort>
</RelOp>
<RelOp AvgRowSize="57" EstimateCPU="0.778316" EstimateIO="0.00140766" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="83013" LogicalOp="Sort" NodeId="25" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="3.57434">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="BatchDate" />
<ColumnReference Column="Expr1015" />
</OutputList>
<MemoryFractions Input="0.842574" Output="0.842574" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="13" ActualRebinds="1" ActualRewinds="0" ActualRows="5219" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="12" ActualRebinds="1" ActualRewinds="0" ActualRows="4330" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="16" ActualRebinds="1" ActualRewinds="0" ActualRows="4880" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="11" ActualRebinds="1" ActualRewinds="0" ActualRows="5428" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="5" ActualRebinds="1" ActualRewinds="0" ActualRows="4889" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="15" ActualRebinds="1" ActualRewinds="0" ActualRows="5371" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="14" ActualRebinds="1" ActualRewinds="0" ActualRows="4682" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="9" ActualRebinds="1" ActualRewinds="0" ActualRows="5339" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="10" ActualRebinds="1" ActualRewinds="0" ActualRows="6277" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="7" ActualRebinds="1" ActualRewinds="0" ActualRows="5097" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="8" ActualRebinds="1" ActualRewinds="0" ActualRows="5527" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="6" ActualRebinds="1" ActualRewinds="0" ActualRows="5090" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="4" ActualRebinds="1" ActualRewinds="0" ActualRows="5369" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRebinds="1" ActualRewinds="0" ActualRows="5787" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRebinds="1" ActualRewinds="0" ActualRows="4968" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRebinds="1" ActualRewinds="0" ActualRows="4760" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRebinds="0" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Column="Expr1015" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="57" EstimateCPU="0.100433" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="83013" LogicalOp="Repartition Streams" NodeId="26" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="2.79462">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="BatchDate" />
<ColumnReference Column="Expr1015" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="13" ActualRows="5219" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="12" ActualRows="4330" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="16" ActualRows="4880" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="11" ActualRows="5428" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="5" ActualRows="4889" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="15" ActualRows="5371" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="14" ActualRows="4682" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="9" ActualRows="5339" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="10" ActualRows="6277" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="7" ActualRows="5097" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="8" ActualRows="5527" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="6" ActualRows="5090" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="5787" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="4" ActualRows="5369" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="4968" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="1" ActualRows="4760" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Parallelism PartitioningType="Hash">
<PartitionColumns>
<ColumnReference Column="Expr1015" />
</PartitionColumns>
<RelOp AvgRowSize="57" EstimateCPU="0.00103766" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="83013" LogicalOp="Compute Scalar" NodeId="27" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="2.69418">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="BatchDate" />
<ColumnReference Column="Expr1015" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1015" />
<ScalarOperator ScalarString="ltrim(rtrim([VanGogh_Dev].[dbo].[Temp_Cust].[CustomerID] as [TC].[CustomerID]))">
<Intrinsic FunctionName="ltrim">
<ScalarOperator>
<Intrinsic FunctionName="rtrim">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="CustomerID" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="65" EstimateCPU="0.0114241" EstimateIO="2.68172" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="83013" LogicalOp="Table Scan" NodeId="28" Parallel="true" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="2.69315" TableCardinality="83013">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="CustomerID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="BatchDate" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="1" ActualRows="4392" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="14" ActualRows="4454" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="16" ActualRows="4420" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="15" ActualRows="5863" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="13" ActualRows="4416" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="12" ActualRows="5869" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="11" ActualRows="5868" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="6" ActualRows="4389" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="10" ActualRows="5867" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="9" ActualRows="5930" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="8" ActualRows="4388" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="7" ActualRows="4394" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="3" ActualRows="5189" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="2" ActualRows="5871" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="5" ActualRows="5864" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="4" ActualRows="5839" ActualEndOfScans="1" ActualExecutions="1" />
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="CustomerID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="BatchDate" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" IndexKind="Heap" />
</TableScan>
</RelOp>
</ComputeScalar>
</RelOp>
</Parallelism>
</RelOp>
</Sort>
</RelOp>
</Merge>
</RelOp>
</Hash>
</RelOp>
</Parallelism>
</RelOp>
</Hash>
</RelOp>
</Sort>
</RelOp>
</Parallelism>
</RelOp>
</Top>
</RelOp>
</Update>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="3.89473" StatementText="Update LS Set LS.Maturity = TC.Maturity ,LS.Principal = TC.Principal ,LS.LoanType = TC.LoanType From dbo.Loan_Stage LS Inner Join dbo.Temp_Cust TC On LS.LoanNum = TC.LoanNum And LS.Customer_ID = TC.CustomerID And LS.BatchDate = TC.BatchDate " StatementType="UPDATE" QueryHash="0xB36911A2B9FC1019" QueryPlanHash="0xC0806ED0F17E82E6">
<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="4872" CachedPlanSize="64" CompileTime="67" CompileCPU="66" CompileMemory="704">
<RelOp AvgRowSize="9" EstimateCPU="1E-06" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Update" NodeId="0" Parallel="false" PhysicalOp="Table Update" EstimatedTotalSubtreeCost="3.89473">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Update DMLRequestSort="false">
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" IndexKind="Heap" />
<SetPredicate>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[Loan_Stage].[Maturity] as [LS].[Maturity] = [Expr1006],[VanGogh_Dev].[dbo].[Loan_Stage].[Principal] as [LS].[Principal] = [VanGogh_Dev].[dbo].[Temp_Cust].[Principal] as [TC].[Principal],[VanGogh_Dev].[dbo].[Loan_Stage].[LoanType] as [LS].[LoanType] = [Expr1007]">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Maturity" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1006" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Principal" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="Principal" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanType" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1007" />
</Identifier>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="45" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Top" NodeId="1" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="3.88472">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="Principal" />
<ColumnReference Column="Expr1006" />
<ColumnReference Column="Expr1007" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="45" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="3.88472">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="Principal" />
<ColumnReference Column="Expr1006" />
<ColumnReference Column="Expr1007" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1006" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(datetime,[VanGogh_Dev].[dbo].[Temp_Cust].[Maturity] as [TC].[Maturity],0)">
<Convert DataType="datetime" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="Maturity" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1007" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(20),[VanGogh_Dev].[dbo].[Temp_Cust].[LoanType] as [TC].[LoanType],0)">
<Convert DataType="varchar" Length="20" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanType" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="46" EstimateCPU="0.000100046" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Distinct Sort" NodeId="3" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="3.88472">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="Maturity" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="Principal" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanType" />
</OutputList>
<MemoryFractions Input="0.00959233" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="true">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Column="Bmk1000" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="46" EstimateCPU="1.05963" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="3.87336">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="Maturity" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="Principal" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanType" />
</OutputList>
<MemoryFractions Input="1" Output="0.990408" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="BatchDate" />
<ColumnReference Column="Expr1017" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="BatchDate" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="CustomerID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[Loan_Stage].[LoanNum] as [LS].[LoanNum]=[VanGogh_Dev].[dbo].[Temp_Cust].[LoanNum] as [TC].[LoanNum] AND [VanGogh_Dev].[dbo].[Loan_Stage].[BatchDate] as [LS].[BatchDate]=[VanGogh_Dev].[dbo].[Temp_Cust].[BatchDate] as [TC].[BatchDate] AND [Expr1017]=[VanGogh_Dev].[dbo].[Temp_Cust].[CustomerID] as [TC].[CustomerID]">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanNum" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="BatchDate" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="BatchDate" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1017" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="CustomerID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="62" EstimateCPU="0.0005179" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Compute Scalar" NodeId="5" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0406079">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="BatchDate" />
<ColumnReference Column="Expr1017" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1017" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(50),[VanGogh_Dev].[dbo].[Loan_Stage].[Customer_ID] as [LS].[Customer_ID],0)">
<Convert DataType="nvarchar" Length="100" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Customer_ID" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="62" EstimateCPU="0.0058539" EstimateIO="0.0342361" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Table Scan" NodeId="6" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.04009" TableCardinality="5179">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="BatchDate" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Customer_ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TableScan Ordered="true" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Bmk1000" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanNum" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="BatchDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Customer_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" IndexKind="Heap" />
</TableScan>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="96" EstimateCPU="0.0914713" EstimateIO="2.68164" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="83013" LogicalOp="Table Scan" NodeId="10" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="2.77311" TableCardinality="83013">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="CustomerID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="Maturity" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="Principal" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanType" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="BatchDate" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="83013" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="CustomerID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanNum" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="Maturity" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="Principal" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="LoanType" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" Column="BatchDate" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Temp_Cust]" Alias="[TC]" IndexKind="Heap" />
</TableScan>
</RelOp>
</Hash>
</RelOp>
</Sort>
</RelOp>
</ComputeScalar>
</RelOp>
</Top>
</RelOp>
</Update>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="4750.96" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.633458" StatementText="Insert Into dbo.LoanAcct (Entity_ID ,LoanNum ,CSO_ID) Select Entity_ID ,Ltrim(Rtrim(LoanNum)) ,CSO_ID From dbo.Loan_Stage Except Select Entity_ID ,Ltrim(Rtrim(LoanNum)) ,CSO_ID From dbo.LoanAcct Where CSO_ID = (Select CSO_ID From dbo.CSO Where CSO = 'Client Name Withheld') " StatementType="INSERT" QueryHash="0xFC83CB10C303E41A" QueryPlanHash="0x5D3097AEC405F64F">
<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="3136" CachedPlanSize="56" CompileTime="40" CompileCPU="40" CompileMemory="776">
<MissingIndexes>
<MissingIndexGroup Impact="7.2412">
<MissingIndex Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[CSO_ID]" ColumnId="4" />
</ColumnGroup>
<ColumnGroup Usage="INCLUDE">
<Column Name="[Entity_ID]" ColumnId="2" />
<Column Name="[LoanNum]" ColumnId="3" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
</MissingIndexes>
<RelOp AvgRowSize="9" EstimateCPU="0.00475096" EstimateIO="0.238359" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4750.96" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Insert" EstimatedTotalSubtreeCost="0.633458">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Update WithUnorderedPrefetch="true" DMLRequestSort="false">
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Index="[PK__LoanAcct__55727D803EF22DE0]" IndexKind="Clustered" />
<SetPredicate>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[LoanAcct].[Entity_ID] = [VanGogh_Dev].[dbo].[Loan_Stage].[Entity_ID],[VanGogh_Dev].[dbo].[LoanAcct].[LoanNum] = RaiseIfNullInsert([Expr1017]),[VanGogh_Dev].[dbo].[LoanAcct].[CSO_ID] = RaiseIfNullInsert([VanGogh_Dev].[dbo].[Loan_Stage].[CSO_ID]),[VanGogh_Dev].[dbo].[LoanAcct].[LoanAcct_ID] = [Expr1016],[VanGogh_Dev].[dbo].[LoanAcct].[Inactive] = NULL,[VanGogh_Dev].[dbo].[LoanAcct].[Maturity] = NULL,[VanGogh_Dev].[dbo].[LoanAcct].[Principal] = NULL,[VanGogh_Dev].[dbo].[LoanAcct].[LoanType] = NULL,[VanGogh_Dev].[dbo].[LoanAcct].[LastUpdate] = NULL,[VanGogh_Dev].[dbo].[LoanAcct].[LastUpdateBy] = NULL">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="Entity_ID" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="Entity_ID" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="LoanNum" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullInsert">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1017" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="CSO_ID" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullInsert">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="CSO_ID" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="LoanAcct_ID" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1016" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="Inactive" />
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="Maturity" />
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="Principal" />
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="LoanType" />
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="LastUpdate" />
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="LastUpdateBy" />
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="60" EstimateCPU="0.000475096" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4750.96" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.390349">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="CSO_ID" />
<ColumnReference Column="Expr1016" />
<ColumnReference Column="Expr1017" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1017" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(75),[Expr1007],0)">
<Convert DataType="varchar" Length="75" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1007" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="73" EstimateCPU="0.000475096" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4750.96" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.389874">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="CSO_ID" />
<ColumnReference Column="Expr1007" />
<ColumnReference Column="Expr1016" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1016" />
<ScalarOperator ScalarString="getidentity((1024058734),(12),NULL)">
<Intrinsic FunctionName="getidentity">
<ScalarOperator>
<Const ConstValue="(1024058734)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(12)" />
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="69" EstimateCPU="0.000475096" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4750.96" LogicalOp="Top" NodeId="4" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.389399">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="CSO_ID" />
<ColumnReference Column="Expr1007" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="69" EstimateCPU="0.0277638" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4750.96" LogicalOp="Right Anti Semi Join" NodeId="5" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="0.388924">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="CSO_ID" />
<ColumnReference Column="Expr1007" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Merge ManyToMany="false">
<InnerSideJoinColumns>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="Entity_ID" />
<ColumnReference Column="Expr1007" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="CSO_ID" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="Entity_ID" />
<ColumnReference Column="Expr1020" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="CSO_ID" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[Loan_Stage].[Entity_ID]=[VanGogh_Dev].[dbo].[LoanAcct].[Entity_ID] AND [Expr1007] = [Expr1020] AND [VanGogh_Dev].[dbo].[Loan_Stage].[CSO_ID]=[VanGogh_Dev].[dbo].[LoanAcct].[CSO_ID]">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="Entity_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="Entity_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1007" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1020" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="CSO_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="CSO_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="94" EstimateCPU="0.105077" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5163.98" LogicalOp="Distinct Sort" NodeId="6" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.187167">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="CSO_ID" />
<ColumnReference Column="Expr1020" />
</OutputList>
<MemoryFractions Input="1" Output="0.551282" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="true">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="Entity_ID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Column="Expr1020" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="94" EstimateCPU="0.0216482" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Inner Join" NodeId="7" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0708287">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="CSO_ID" />
<ColumnReference Column="Expr1020" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<Predicate>
<ScalarOperator ScalarString="[Expr1019]=[VanGogh_Dev].[dbo].[LoanAcct].[CSO_ID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1019" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="CSO_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
<RelOp AvgRowSize="11" EstimateCPU="4.8E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Assert" NodeId="8" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="0.00331518">
<OutputList>
<ColumnReference Column="Expr1019" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Assert StartupExpression="false">
<RelOp AvgRowSize="19" EstimateCPU="1.1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="9" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.0033147">
<OutputList>
<ColumnReference Column="Expr1018" />
<ColumnReference Column="Expr1019" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1018" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1019" />
<ScalarOperator ScalarString="ANY([VanGogh_Dev].[dbo].[CSO].[CSO_ID])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[CSO]" Column="CSO_ID" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="28" EstimateCPU="0.000179" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="10" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.003304" TableCardinality="20">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[CSO]" Column="CSO_ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[CSO]" Column="CSO_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[CSO]" Index="[PK__CSO__6B727A9137510C18]" IndexKind="Clustered" />
<Predicate>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[CSO].[CSO]='Client Name Withheld'">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[CSO]" Column="CSO" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'Client Name Withheld'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</StreamAggregate>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN [Expr1018]>(1) THEN (0) ELSE NULL END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1018" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Predicate>
</Assert>
</RelOp>
<RelOp AvgRowSize="94" EstimateCPU="0.0005179" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Compute Scalar" NodeId="15" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0443116">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="CSO_ID" />
<ColumnReference Column="Expr1020" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1020" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(75),ltrim(rtrim([VanGogh_Dev].[dbo].[LoanAcct].[LoanNum])),0)">
<Convert DataType="nvarchar" Length="150" Style="0" Implicit="true">
<ScalarOperator>
<Intrinsic FunctionName="ltrim">
<ScalarOperator>
<Intrinsic FunctionName="rtrim">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="LoanNum" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="32" EstimateCPU="0.0058539" EstimateIO="0.0379398" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Clustered Index Scan" NodeId="16" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0437937" TableCardinality="5179">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="CSO_ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="Entity_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="LoanNum" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="CSO_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Index="[PK__LoanAcct__55727D803EF22DE0]" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
</NestedLoops>
</RelOp>
</Sort>
</RelOp>
<RelOp AvgRowSize="69" EstimateCPU="0.105077" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5163.98" LogicalOp="Distinct Sort" NodeId="21" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.173983">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="CSO_ID" />
<ColumnReference Column="Expr1007" />
</OutputList>
<MemoryFractions Input="0.448718" Output="0.448718" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="true">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="Entity_ID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Column="Expr1007" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="CSO_ID" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="69" EstimateCPU="0.0005179" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Compute Scalar" NodeId="22" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0576449">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="CSO_ID" />
<ColumnReference Column="Expr1007" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1007" />
<ScalarOperator ScalarString="ltrim(rtrim([VanGogh_Dev].[dbo].[Loan_Stage].[LoanNum]))">
<Intrinsic FunctionName="ltrim">
<ScalarOperator>
<Intrinsic FunctionName="rtrim">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="LoanNum" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="45" EstimateCPU="0.0058539" EstimateIO="0.0512731" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Table Scan" NodeId="23" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.057127" TableCardinality="5179">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="CSO_ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="LoanNum" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="Entity_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Column="CSO_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" IndexKind="Heap" />
</TableScan>
</RelOp>
</ComputeScalar>
</RelOp>
</Sort>
</RelOp>
</Merge>
</RelOp>
</Top>
</RelOp>
</ComputeScalar>
</RelOp>
</ComputeScalar>
</RelOp>
</Update>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0596005" StatementText="Update dbo.LoanAcct Set LastUpdate = GetDate() Where LastUpdate Is Null And CSO_ID = (Select CSO_ID From dbo.CSO Where CSO = 'Client Name Withheld') " StatementType="UPDATE" QueryHash="0x02FA6F7A0152F0F2" QueryPlanHash="0xF4991A1BBA9F27DF">
<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="32" CompileTime="11" CompileCPU="11" CompileMemory="352">
<MissingIndexes>
<MissingIndexGroup Impact="50.0353">
<MissingIndex Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[LastUpdate]" ColumnId="8" />
</ColumnGroup>
<ColumnGroup Usage="INCLUDE">
<Column Name="[LoanAcct_ID]" ColumnId="1" />
<Column Name="[CSO_ID]" ColumnId="4" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
<MissingIndexGroup Impact="50.0428">
<MissingIndex Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[CSO_ID]" ColumnId="4" />
<Column Name="[LastUpdate]" ColumnId="8" />
</ColumnGroup>
<ColumnGroup Usage="INCLUDE">
<Column Name="[LoanAcct_ID]" ColumnId="1" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
</MissingIndexes>
<RelOp AvgRowSize="9" EstimateCPU="1E-06" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Update" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Update" EstimatedTotalSubtreeCost="0.0596005">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Update DMLRequestSort="false">
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Index="[PK__LoanAcct__55727D803EF22DE0]" IndexKind="Clustered" />
<SetPredicate>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[LoanAcct].[LastUpdate] = [Expr1007]">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="LastUpdate" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1007" />
</Identifier>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="19" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0495995">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="LoanAcct_ID" />
<ColumnReference Column="Expr1007" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1007" />
<ScalarOperator ScalarString="getdate()">
<Identifier>
<ColumnReference Column="ConstExpr1012">
<ScalarOperator>
<Intrinsic FunctionName="getdate" />
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="11" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Top" NodeId="2" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.0495994">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="LoanAcct_ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="11" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0495993">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="LoanAcct_ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<Predicate>
<ScalarOperator ScalarString="[Expr1014]=[VanGogh_Dev].[dbo].[LoanAcct].[CSO_ID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1014" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="CSO_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
<RelOp AvgRowSize="11" EstimateCPU="4.8E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Assert" NodeId="4" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="0.00331518">
<OutputList>
<ColumnReference Column="Expr1014" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<Assert StartupExpression="false">
<RelOp AvgRowSize="19" EstimateCPU="1.1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="5" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.0033147">
<OutputList>
<ColumnReference Column="Expr1013" />
<ColumnReference Column="Expr1014" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1013" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1014" />
<ScalarOperator ScalarString="ANY([VanGogh_Dev].[dbo].[CSO].[CSO_ID])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[CSO]" Column="CSO_ID" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="28" EstimateCPU="0.000179" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="6" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.003304" TableCardinality="20">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[CSO]" Column="CSO_ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[CSO]" Column="CSO_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[CSO]" Index="[PK__CSO__6B727A9137510C18]" IndexKind="Clustered" />
<Predicate>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[CSO].[CSO]='Client Name Withheld'">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[CSO]" Column="CSO" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'Client Name Withheld'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</StreamAggregate>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN [Expr1013]>(1) THEN (0) ELSE NULL END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1013" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Predicate>
</Assert>
</RelOp>
<RelOp AvgRowSize="23" EstimateCPU="0.0058539" EstimateIO="0.0379398" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="11" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0437937" TableCardinality="5179">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="LoanAcct_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="CSO_ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="LoanAcct_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="CSO_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Index="[PK__LoanAcct__55727D803EF22DE0]" IndexKind="Clustered" />
<Predicate>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[LoanAcct].[LastUpdate] IS NULL">
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="LastUpdate" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</Top>
</RelOp>
</ComputeScalar>
</RelOp>
</Update>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.122" StatementText="Update dbo.LoanAcct Set LastUpdateBy = (Select Entity_ID From dbo.Entity Where EntityType = 'System' And Fname = 'Client Name Withheld' And Lname = 'Import') Where LastUpdateBy Is Null And CSO_ID = (Select CSO_ID From dbo.CSO Where CSO = 'Client Name Withheld') " StatementType="UPDATE" QueryHash="0xD187A6D302A3CB3F" QueryPlanHash="0x918BD393C2769213">
<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="40" CompileTime="13" CompileCPU="13" CompileMemory="536">
<MissingIndexes>
<MissingIndexGroup Impact="47.9739">
<MissingIndex Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[EntityType]" ColumnId="3" />
<Column Name="[Lname]" ColumnId="5" />
<Column Name="[Fname]" ColumnId="6" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
<MissingIndexGroup Impact="26.2651">
<MissingIndex Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[LastUpdateBy]" ColumnId="9" />
</ColumnGroup>
<ColumnGroup Usage="INCLUDE">
<Column Name="[LoanAcct_ID]" ColumnId="1" />
<Column Name="[CSO_ID]" ColumnId="4" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
<MissingIndexGroup Impact="26.2688">
<MissingIndex Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[CSO_ID]" ColumnId="4" />
<Column Name="[LastUpdateBy]" ColumnId="9" />
</ColumnGroup>
<ColumnGroup Usage="INCLUDE">
<Column Name="[LoanAcct_ID]" ColumnId="1" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
</MissingIndexes>
<RelOp AvgRowSize="9" EstimateCPU="1E-06" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Update" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Update" EstimatedTotalSubtreeCost="0.122">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Update DMLRequestSort="false">
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Index="[PK__LoanAcct__55727D803EF22DE0]" IndexKind="Clustered" />
<SetPredicate>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[LoanAcct].[LastUpdateBy] = [Expr1011]">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="LastUpdateBy" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1011" />
</Identifier>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="15" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.111999">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="LoanAcct_ID" />
<ColumnReference Column="Expr1011" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1011" />
<ScalarOperator ScalarString="[Expr1017]">
<Identifier>
<ColumnReference Column="Expr1017" />
</Identifier>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="15" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Left Outer Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.111999">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="LoanAcct_ID" />
<ColumnReference Column="Expr1017" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<RelOp AvgRowSize="11" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Top" NodeId="3" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.0495994">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="LoanAcct_ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="11" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0495993">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="LoanAcct_ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<Predicate>
<ScalarOperator ScalarString="[Expr1019]=[VanGogh_Dev].[dbo].[LoanAcct].[CSO_ID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1019" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="CSO_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
<RelOp AvgRowSize="11" EstimateCPU="4.8E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Assert" NodeId="5" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="0.00331518">
<OutputList>
<ColumnReference Column="Expr1019" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<Assert StartupExpression="false">
<RelOp AvgRowSize="19" EstimateCPU="1.1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="6" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.0033147">
<OutputList>
<ColumnReference Column="Expr1018" />
<ColumnReference Column="Expr1019" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1018" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1019" />
<ScalarOperator ScalarString="ANY([VanGogh_Dev].[dbo].[CSO].[CSO_ID])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[CSO]" Column="CSO_ID" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="28" EstimateCPU="0.000179" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="7" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.003304" TableCardinality="20">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[CSO]" Column="CSO_ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[CSO]" Column="CSO_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[CSO]" Index="[PK__CSO__6B727A9137510C18]" IndexKind="Clustered" />
<Predicate>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[CSO].[CSO]='Client Name Withheld'">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[CSO]" Column="CSO" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'Client Name Withheld'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</StreamAggregate>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN [Expr1018]>(1) THEN (0) ELSE NULL END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1018" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Predicate>
</Assert>
</RelOp>
<RelOp AvgRowSize="19" EstimateCPU="0.0058539" EstimateIO="0.0379398" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="12" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0437937" TableCardinality="5179">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="LoanAcct_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="CSO_ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="LoanAcct_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="CSO_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Index="[PK__LoanAcct__55727D803EF22DE0]" IndexKind="Clustered" />
<Predicate>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[LoanAcct].[LastUpdateBy] IS NULL">
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Column="LastUpdateBy" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</Top>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="4.8E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Assert" NodeId="15" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="0.0623957">
<OutputList>
<ColumnReference Column="Expr1017" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Assert StartupExpression="false">
<RelOp AvgRowSize="19" EstimateCPU="1.1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="16" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.0623952">
<OutputList>
<ColumnReference Column="Expr1016" />
<ColumnReference Column="Expr1017" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1016" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1017" />
<ScalarOperator ScalarString="ANY([VanGogh_Dev].[dbo].[Entity].[Entity_ID])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Column="Entity_ID" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="35" EstimateCPU="0.0047319" EstimateIO="0.0527546" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="17" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0574865" TableCardinality="4159">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Column="Entity_ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Column="Entity_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Index="[PK__Entity__9BC0DE463B219CFC]" IndexKind="Clustered" />
<Predicate>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[Entity].[EntityType]='System' AND [VanGogh_Dev].[dbo].[Entity].[Fname]='Client Name Withheld' AND [VanGogh_Dev].[dbo].[Entity].[Lname]='Import'">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Column="EntityType" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'System'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Column="Fname" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'Client Name Withheld'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Column="Lname" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'Import'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</StreamAggregate>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN [Expr1016]>(1) THEN (0) ELSE NULL END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1016" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Predicate>
</Assert>
</RelOp>
</NestedLoops>
</RelOp>
</ComputeScalar>
</RelOp>
</Update>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1.2285" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.449082" StatementText="Update LA Set LA.Maturity = LS.Maturity ,LA.LastUpdate = GetDate() ,LA.LastUpdateBy = (Select Entity_ID From dbo.Entity Where EntityType = 'System' And Fname = 'Client Name Withheld' And Lname = 'Import') From dbo.LoanAcct LA Inner Join dbo.Loan_Stage LS On LA.LoanNum = LS.LoanNum And LA.Entity_ID = LS.Entity_ID And LA.CSO_ID = LS.CSO_ID Where LA.Maturity <> LS.Maturity Or LA.Maturity Is Null ---------- --=== Update Principal " StatementType="UPDATE" QueryHash="0x50A6D991C8D55505" QueryPlanHash="0xAF5A201BCE194D17">
<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="3008" CachedPlanSize="56" CompileTime="35" CompileCPU="34" CompileMemory="808">
<MissingIndexes>
<MissingIndexGroup Impact="13.5194">
<MissingIndex Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[EntityType]" ColumnId="3" />
<Column Name="[Lname]" ColumnId="5" />
<Column Name="[Fname]" ColumnId="6" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
</MissingIndexes>
<RelOp AvgRowSize="9" EstimateCPU="1.2285E-06" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.2285" LogicalOp="Update" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Update" EstimatedTotalSubtreeCost="0.449082">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Update DMLRequestSort="false">
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Index="[PK__LoanAcct__55727D803EF22DE0]" Alias="[LA]" IndexKind="Clustered" />
<SetPredicate>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[LoanAcct].[Maturity] as [LA].[Maturity] = [VanGogh_Dev].[dbo].[Loan_Stage].[Maturity] as [LS].[Maturity],[VanGogh_Dev].[dbo].[LoanAcct].[LastUpdate] as [LA].[LastUpdate] = RaiseIfNullUpdate([Expr1005]),[VanGogh_Dev].[dbo].[LoanAcct].[LastUpdateBy] as [LA].[LastUpdateBy] = RaiseIfNullUpdate([Expr1010])">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Maturity" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Maturity" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LastUpdate" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullUpdate">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1005" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LastUpdateBy" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullUpdate">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1010" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="31" EstimateCPU="1.2285E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.2285" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.439081">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Maturity" />
<ColumnReference Column="Expr1005" />
<ColumnReference Column="Expr1010" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1005" />
<ScalarOperator ScalarString="getdate()">
<Identifier>
<ColumnReference Column="ConstExpr1016">
<ScalarOperator>
<Intrinsic FunctionName="getdate" />
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1010" />
<ScalarOperator ScalarString="[Expr1018]">
<Identifier>
<ColumnReference Column="Expr1018" />
</Identifier>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="23" EstimateCPU="5.13513E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.2285" LogicalOp="Left Outer Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.439081">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Maturity" />
<ColumnReference Column="Expr1018" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<RelOp AvgRowSize="19" EstimateCPU="1.2285E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.2285" LogicalOp="Top" NodeId="3" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.374495">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Maturity" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="19" EstimateCPU="0.000100588" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.2285" LogicalOp="Distinct Sort" NodeId="4" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.374495">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Maturity" />
</OutputList>
<MemoryFractions Input="0.0172414" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="true">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="19" EstimateCPU="0.0283754" EstimateIO="0.000626" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.2285" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="0.363133">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Maturity" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Merge ManyToMany="true">
<InnerSideJoinColumns>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanNum" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="CSO_ID" />
<ColumnReference Column="Expr1022" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[LoanAcct].[Entity_ID] as [LA].[Entity_ID]=[VanGogh_Dev].[dbo].[Loan_Stage].[Entity_ID] as [LS].[Entity_ID] AND [VanGogh_Dev].[dbo].[LoanAcct].[CSO_ID] as [LA].[CSO_ID]=[VanGogh_Dev].[dbo].[Loan_Stage].[CSO_ID] as [LS].[CSO_ID] AND [Expr1022]=[VanGogh_Dev].[dbo].[Loan_Stage].[LoanNum] as [LS].[LoanNum] AND ([VanGogh_Dev].[dbo].[LoanAcct].[Maturity] as [LA].[Maturity]<>[VanGogh_Dev].[dbo].[Loan_Stage].[Maturity] as [LS].[Maturity] OR [VanGogh_Dev].[dbo].[LoanAcct].[Maturity] as [LA].[Maturity] IS NULL)">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Entity_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Entity_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="CSO_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="CSO_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1022" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanNum" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Logical Operation="OR">
<ScalarOperator>
<Compare CompareOp="NE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Maturity" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Maturity" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Maturity" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="44" EstimateCPU="0.105077" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Sort" NodeId="6" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.16065">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Maturity" />
<ColumnReference Column="Expr1022" />
</OutputList>
<MemoryFractions Input="1" Output="0.465517" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Entity_ID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="CSO_ID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Column="Expr1022" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="44" EstimateCPU="0.0005179" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Compute Scalar" NodeId="7" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0443116">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Maturity" />
<ColumnReference Column="Expr1022" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1022" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(75),[VanGogh_Dev].[dbo].[LoanAcct].[LoanNum] as [LA].[LoanNum],0)">
<Convert DataType="nvarchar" Length="150" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanNum" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="44" EstimateCPU="0.0058539" EstimateIO="0.0379398" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Clustered Index Scan" NodeId="8" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0437937" TableCardinality="5179">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Maturity" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Entity_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanNum" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="CSO_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Maturity" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Index="[PK__LoanAcct__55727D803EF22DE0]" Alias="[LA]" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
</Sort>
</RelOp>
<RelOp AvgRowSize="53" EstimateCPU="0.105077" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Sort" NodeId="12" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.173465">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Maturity" />
</OutputList>
<MemoryFractions Input="0.517241" Output="0.517241" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Entity_ID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="CSO_ID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanNum" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="53" EstimateCPU="0.0058539" EstimateIO="0.0512731" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Table Scan" NodeId="13" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.057127" TableCardinality="5179">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Maturity" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanNum" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Entity_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="CSO_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Maturity" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" IndexKind="Heap" />
</TableScan>
</RelOp>
</Sort>
</RelOp>
</Merge>
</RelOp>
</Sort>
</RelOp>
</Top>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="4.8E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0.2285" EstimateRows="1" LogicalOp="Assert" NodeId="16" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="0.0645808">
<OutputList>
<ColumnReference Column="Expr1018" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Assert StartupExpression="false">
<RelOp AvgRowSize="19" EstimateCPU="1.1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0.2285" EstimateRows="1" LogicalOp="Aggregate" NodeId="17" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.0645802">
<OutputList>
<ColumnReference Column="Expr1017" />
<ColumnReference Column="Expr1018" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1017" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1018" />
<ScalarOperator ScalarString="ANY([VanGogh_Dev].[dbo].[Entity].[Entity_ID])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Column="Entity_ID" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="35" EstimateCPU="0.0046534" EstimateIO="0.0528331" EstimateRebinds="0" EstimateRewinds="0.2285" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="18" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0585498" TableCardinality="4159">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Column="Entity_ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Column="Entity_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Index="[PK__Entity__9BC0DE463B219CFC]" IndexKind="Clustered" />
<Predicate>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[Entity].[EntityType]='System' AND [VanGogh_Dev].[dbo].[Entity].[Fname]='Client Name Withheld' AND [VanGogh_Dev].[dbo].[Entity].[Lname]='Import'">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Column="EntityType" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'System'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Column="Fname" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'Client Name Withheld'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Column="Lname" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'Import'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</StreamAggregate>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN [Expr1017]>(1) THEN (0) ELSE NULL END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1017" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Predicate>
</Assert>
</RelOp>
</NestedLoops>
</RelOp>
</ComputeScalar>
</RelOp>
</Update>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="2" StatementEstRows="1.2285" StatementId="2" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.449082" StatementText="Update LA Set LA.Principal = LS.Principal ,LA.LastUpdate = GetDate() ,LA.LastUpdateBy = (Select Entity_ID From dbo.Entity Where EntityType = 'System' And Fname = 'Client Name Withheld' And Lname = 'Import') From dbo.LoanAcct LA Inner Join dbo.Loan_Stage LS On LA.LoanNum = LS.LoanNum And LA.Entity_ID = LS.Entity_ID And LA.CSO_ID = LS.CSO_ID Where LA.Principal <> LS.Principal Or LA.Principal Is Null -------------------- --=== Update LoanType on dbo.Loan_Stage " StatementType="UPDATE" QueryHash="0xA3A13078EE506485" QueryPlanHash="0xF62D1D8FC77A6A53">
<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="3024" CachedPlanSize="56" CompileTime="37" CompileCPU="37" CompileMemory="824">
<MissingIndexes>
<MissingIndexGroup Impact="13.5194">
<MissingIndex Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[EntityType]" ColumnId="3" />
<Column Name="[Lname]" ColumnId="5" />
<Column Name="[Fname]" ColumnId="6" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
</MissingIndexes>
<RelOp AvgRowSize="9" EstimateCPU="1.2285E-06" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.2285" LogicalOp="Update" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Update" EstimatedTotalSubtreeCost="0.449082">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Update DMLRequestSort="false">
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Index="[PK__LoanAcct__55727D803EF22DE0]" Alias="[LA]" IndexKind="Clustered" />
<SetPredicate>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[LoanAcct].[Principal] as [LA].[Principal] = [Expr1005],[VanGogh_Dev].[dbo].[LoanAcct].[LastUpdate] as [LA].[LastUpdate] = RaiseIfNullUpdate([Expr1006]),[VanGogh_Dev].[dbo].[LoanAcct].[LastUpdateBy] as [LA].[LastUpdateBy] = RaiseIfNullUpdate([Expr1011])">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Principal" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1005" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LastUpdate" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullUpdate">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1006" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LastUpdateBy" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullUpdate">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1011" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="32" EstimateCPU="1.2285E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.2285" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.439081">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Column="Expr1005" />
<ColumnReference Column="Expr1006" />
<ColumnReference Column="Expr1011" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1006" />
<ScalarOperator ScalarString="getdate()">
<Identifier>
<ColumnReference Column="ConstExpr1017">
<ScalarOperator>
<Intrinsic FunctionName="getdate" />
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1011" />
<ScalarOperator ScalarString="[Expr1019]">
<Identifier>
<ColumnReference Column="Expr1019" />
</Identifier>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="24" EstimateCPU="5.13513E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.2285" LogicalOp="Left Outer Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.439081">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Column="Expr1005" />
<ColumnReference Column="Expr1019" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<RelOp AvgRowSize="20" EstimateCPU="1.2285E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.2285" LogicalOp="Top" NodeId="3" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.374495">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Column="Expr1005" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="20" EstimateCPU="1.2285E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.2285" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.374495">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Column="Expr1005" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1005" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(decimal(18,2),[VanGogh_Dev].[dbo].[Loan_Stage].[Principal] as [LS].[Principal],0)">
<Convert DataType="decimal" Precision="18" Scale="2" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Principal" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="19" EstimateCPU="0.000100588" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.2285" LogicalOp="Distinct Sort" NodeId="5" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.374495">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Principal" />
</OutputList>
<MemoryFractions Input="0.017094" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="true">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="19" EstimateCPU="0.0283754" EstimateIO="0.000626" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.2285" LogicalOp="Inner Join" NodeId="6" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="0.363133">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Principal" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Merge ManyToMany="true">
<InnerSideJoinColumns>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanNum" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="CSO_ID" />
<ColumnReference Column="Expr1023" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[LoanAcct].[Entity_ID] as [LA].[Entity_ID]=[VanGogh_Dev].[dbo].[Loan_Stage].[Entity_ID] as [LS].[Entity_ID] AND [VanGogh_Dev].[dbo].[LoanAcct].[CSO_ID] as [LA].[CSO_ID]=[VanGogh_Dev].[dbo].[Loan_Stage].[CSO_ID] as [LS].[CSO_ID] AND [Expr1023]=[VanGogh_Dev].[dbo].[Loan_Stage].[LoanNum] as [LS].[LoanNum] AND ([VanGogh_Dev].[dbo].[LoanAcct].[Principal] as [LA].[Principal]<>[VanGogh_Dev].[dbo].[Loan_Stage].[Principal] as [LS].[Principal] OR [VanGogh_Dev].[dbo].[LoanAcct].[Principal] as [LA].[Principal] IS NULL)">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Entity_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Entity_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="CSO_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="CSO_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1023" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanNum" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Logical Operation="OR">
<ScalarOperator>
<Compare CompareOp="NE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Principal" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Principal" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Principal" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="45" EstimateCPU="0.105077" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Sort" NodeId="7" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.16065">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Principal" />
<ColumnReference Column="Expr1023" />
</OutputList>
<MemoryFractions Input="1" Output="0.470085" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Entity_ID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="CSO_ID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Column="Expr1023" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="45" EstimateCPU="0.0005179" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Compute Scalar" NodeId="8" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0443116">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Principal" />
<ColumnReference Column="Expr1023" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1023" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(75),[VanGogh_Dev].[dbo].[LoanAcct].[LoanNum] as [LA].[LoanNum],0)">
<Convert DataType="nvarchar" Length="150" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanNum" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="45" EstimateCPU="0.0058539" EstimateIO="0.0379398" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Clustered Index Scan" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0437937" TableCardinality="5179">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Principal" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Entity_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanNum" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="CSO_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Principal" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Index="[PK__LoanAcct__55727D803EF22DE0]" Alias="[LA]" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
</Sort>
</RelOp>
<RelOp AvgRowSize="53" EstimateCPU="0.105077" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Sort" NodeId="13" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.173465">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Principal" />
</OutputList>
<MemoryFractions Input="0.512821" Output="0.512821" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Entity_ID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="CSO_ID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanNum" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="53" EstimateCPU="0.0058539" EstimateIO="0.0512731" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Table Scan" NodeId="14" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.057127" TableCardinality="5179">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Principal" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanNum" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Entity_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="CSO_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Principal" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" IndexKind="Heap" />
</TableScan>
</RelOp>
</Sort>
</RelOp>
</Merge>
</RelOp>
</Sort>
</RelOp>
</ComputeScalar>
</RelOp>
</Top>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="4.8E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0.2285" EstimateRows="1" LogicalOp="Assert" NodeId="20" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="0.0645808">
<OutputList>
<ColumnReference Column="Expr1019" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Assert StartupExpression="false">
<RelOp AvgRowSize="19" EstimateCPU="1.1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0.2285" EstimateRows="1" LogicalOp="Aggregate" NodeId="21" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.0645802">
<OutputList>
<ColumnReference Column="Expr1018" />
<ColumnReference Column="Expr1019" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1018" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1019" />
<ScalarOperator ScalarString="ANY([VanGogh_Dev].[dbo].[Entity].[Entity_ID])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Column="Entity_ID" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="35" EstimateCPU="0.0046534" EstimateIO="0.0528331" EstimateRebinds="0" EstimateRewinds="0.2285" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="22" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0585498" TableCardinality="4159">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Column="Entity_ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Column="Entity_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Index="[PK__Entity__9BC0DE463B219CFC]" IndexKind="Clustered" />
<Predicate>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[Entity].[EntityType]='System' AND [VanGogh_Dev].[dbo].[Entity].[Fname]='Client Name Withheld' AND [VanGogh_Dev].[dbo].[Entity].[Lname]='Import'">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Column="EntityType" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'System'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Column="Fname" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'Client Name Withheld'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Column="Lname" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'Import'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</StreamAggregate>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN [Expr1018]>(1) THEN (0) ELSE NULL END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1018" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Predicate>
</Assert>
</RelOp>
</NestedLoops>
</RelOp>
</ComputeScalar>
</RelOp>
</Update>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="3" StatementEstRows="5179" StatementId="3" StatementOptmLevel="FULL" StatementSubTreeCost="1.24325" StatementText="Update LS Set LS.LoanType = (Select LT.Loan_Type_ID From dbo.Loan_Types LT Where LS.LoanType = LT.CSO_LoanType_Value) From dbo.Loan_Stage LS Inner Join dbo.Loan_Types LT On LS.LoanType = LT.CSO_LoanType_Value ---------- " StatementType="UPDATE" QueryHash="0x8E0AC7894092417C" QueryPlanHash="0x6490B4DCAFFB83C0">
<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="2256" CachedPlanSize="56" CompileTime="36" CompileCPU="35" CompileMemory="456">
<RelOp AvgRowSize="9" EstimateCPU="0.005179" EstimateIO="0.449189" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Update" NodeId="0" Parallel="false" PhysicalOp="Table Update" EstimatedTotalSubtreeCost="1.24325">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Update DMLRequestSort="false">
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" IndexKind="Heap" />
<SetPredicate>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[Loan_Stage].[LoanType] as [LS].[LoanType] = [Expr1008]">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanType" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1008" />
</Identifier>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="29" EstimateCPU="0.0005179" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.788879">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Column="Expr1008" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1008" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(20),[Expr1014],0)">
<Convert DataType="varchar" Length="20" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1014" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="19" EstimateCPU="0.0216482" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Left Outer Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.788361">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Column="Expr1014" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanType" />
</OuterReferences>
<RelOp AvgRowSize="29" EstimateCPU="0.105077" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Sort" NodeId="3" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.234596">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanType" />
</OutputList>
<MemoryFractions Input="1" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanType" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="29" EstimateCPU="0.0005179" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Top" NodeId="4" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.118258">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanType" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="29" EstimateCPU="0.0573259" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Right Semi Join" NodeId="5" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.11774">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanType" />
</OutputList>
<MemoryFractions Input="0" Output="0" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Types]" Alias="[LT]" Column="CSO_LoanType_Value" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanType" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[Loan_Types].[CSO_LoanType_Value] as [LT].[CSO_LoanType_Value]=[VanGogh_Dev].[dbo].[Loan_Stage].[LoanType] as [LS].[LoanType]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Types]" Alias="[LT]" Column="CSO_LoanType_Value" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanType" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="14" EstimateCPU="0.0001592" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2" LogicalOp="Index Scan" NodeId="6" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.0032842" TableCardinality="2">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Types]" Alias="[LT]" Column="CSO_LoanType_Value" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Types]" Alias="[LT]" Column="CSO_LoanType_Value" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Types]" Index="[idx_CSO_LoanType_Value]" Alias="[LT]" TableReferenceId="1" IndexKind="NonClustered" />
</IndexScan>
</RelOp>
<RelOp AvgRowSize="29" EstimateCPU="0.0058539" EstimateIO="0.0512731" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Table Scan" NodeId="7" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.057127" TableCardinality="5179">
<OutputList>
<ColumnReference Column="Bmk1000" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanType" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TableScan Ordered="true" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Bmk1000" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanType" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" IndexKind="Heap" />
</TableScan>
</RelOp>
</Hash>
</RelOp>
</Top>
</RelOp>
</Sort>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.00010028" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="5178" EstimateRows="1" LogicalOp="Lazy Spool" NodeId="10" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0.532117">
<OutputList>
<ColumnReference Column="Expr1014" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="5178" ActualRows="5179" ActualEndOfScans="0" ActualExecutions="5179" />
</RunTimeInformation>
<Spool>
<RelOp AvgRowSize="11" EstimateCPU="4.8E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Assert" NodeId="11" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="0.00328468">
<OutputList>
<ColumnReference Column="Expr1014" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<Assert StartupExpression="false">
<RelOp AvgRowSize="19" EstimateCPU="1.1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="12" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.0032842">
<OutputList>
<ColumnReference Column="Expr1013" />
<ColumnReference Column="Expr1014" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1013" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1014" />
<ScalarOperator ScalarString="ANY([VanGogh_Dev].[dbo].[Loan_Types].[Loan_Type_ID] as [LT].[Loan_Type_ID])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Types]" Alias="[LT]" Column="Loan_Type_ID" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Seek" NodeId="13" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="2">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Types]" Alias="[LT]" Column="Loan_Type_ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Types]" Alias="[LT]" Column="Loan_Type_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Types]" Index="[idx_CSO_LoanType_Value]" Alias="[LT]" TableReferenceId="2" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Types]" Alias="[LT]" Column="CSO_LoanType_Value" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[Loan_Stage].[LoanType] as [LS].[LoanType]">
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanType" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</StreamAggregate>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN [Expr1013]>(1) THEN (0) ELSE NULL END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1013" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Predicate>
</Assert>
</RelOp>
</Spool>
</RelOp>
</NestedLoops>
</RelOp>
</ComputeScalar>
</RelOp>
</Update>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="4" StatementEstRows="1.2285" StatementId="4" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.4496" StatementText="Update LA Set LA.LoanType = LS.LoanType ,LA.LastUpdate = GetDate() ,LA.LastUpdateBy = (Select Entity_ID From dbo.Entity Where EntityType = 'System' And Fname = 'Client Name Withheld' And Lname = 'Import') From dbo.LoanAcct LA Inner Join dbo.Loan_Stage LS On LA.LoanNum = LS.LoanNum And LA.Entity_ID = LS.Entity_ID And LA.CSO_ID = LS.CSO_ID Where LA.LoanType <> LS.LoanType Or LA.LoanType Is Null ---------------------------------------- --=== House cleaning " StatementType="UPDATE" QueryHash="0xCFC80E2F98D24D99" QueryPlanHash="0xC2F82092DB8D4602">
<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="2928" CachedPlanSize="64" CompileTime="35" CompileCPU="34" CompileMemory="824">
<MissingIndexes>
<MissingIndexGroup Impact="13.5038">
<MissingIndex Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[EntityType]" ColumnId="3" />
<Column Name="[Lname]" ColumnId="5" />
<Column Name="[Fname]" ColumnId="6" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
</MissingIndexes>
<RelOp AvgRowSize="9" EstimateCPU="1.2285E-06" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.2285" LogicalOp="Update" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Update" EstimatedTotalSubtreeCost="0.4496">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Update DMLRequestSort="false">
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Index="[PK__LoanAcct__55727D803EF22DE0]" Alias="[LA]" IndexKind="Clustered" />
<SetPredicate>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[LoanAcct].[LoanType] as [LA].[LoanType] = [Expr1005],[VanGogh_Dev].[dbo].[LoanAcct].[LastUpdate] as [LA].[LastUpdate] = RaiseIfNullUpdate([Expr1006]),[VanGogh_Dev].[dbo].[LoanAcct].[LastUpdateBy] as [LA].[LastUpdateBy] = RaiseIfNullUpdate([Expr1011])">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanType" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1005" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LastUpdate" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullUpdate">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1006" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LastUpdateBy" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullUpdate">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1011" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="27" EstimateCPU="1.2285E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.2285" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.439599">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Column="Expr1005" />
<ColumnReference Column="Expr1006" />
<ColumnReference Column="Expr1011" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1006" />
<ScalarOperator ScalarString="getdate()">
<Identifier>
<ColumnReference Column="ConstExpr1017">
<ScalarOperator>
<Intrinsic FunctionName="getdate" />
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1011" />
<ScalarOperator ScalarString="[Expr1019]">
<Identifier>
<ColumnReference Column="Expr1019" />
</Identifier>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="19" EstimateCPU="5.13513E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.2285" LogicalOp="Left Outer Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.439599">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Column="Expr1005" />
<ColumnReference Column="Expr1019" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<RelOp AvgRowSize="15" EstimateCPU="1.2285E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.2285" LogicalOp="Top" NodeId="3" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.375013">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Column="Expr1005" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="15" EstimateCPU="1.2285E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.2285" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.375013">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Column="Expr1005" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1005" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[VanGogh_Dev].[dbo].[Loan_Stage].[LoanType] as [LS].[LoanType],0)">
<Convert DataType="int" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanType" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="16" EstimateCPU="0.000100585" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.2285" LogicalOp="Distinct Sort" NodeId="5" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.375013">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanType" />
</OutputList>
<MemoryFractions Input="0.018018" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="true">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="16" EstimateCPU="0.0283754" EstimateIO="0.000626" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.2285" LogicalOp="Inner Join" NodeId="6" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="0.363651">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanType" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Merge ManyToMany="true">
<InnerSideJoinColumns>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanNum" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="CSO_ID" />
<ColumnReference Column="Expr1023" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[LoanAcct].[Entity_ID] as [LA].[Entity_ID]=[VanGogh_Dev].[dbo].[Loan_Stage].[Entity_ID] as [LS].[Entity_ID] AND [VanGogh_Dev].[dbo].[LoanAcct].[CSO_ID] as [LA].[CSO_ID]=[VanGogh_Dev].[dbo].[Loan_Stage].[CSO_ID] as [LS].[CSO_ID] AND [Expr1023]=[VanGogh_Dev].[dbo].[Loan_Stage].[LoanNum] as [LS].[LoanNum] AND ([VanGogh_Dev].[dbo].[LoanAcct].[LoanType] as [LA].[LoanType]<>[Expr1024] OR [VanGogh_Dev].[dbo].[LoanAcct].[LoanType] as [LA].[LoanType] IS NULL)">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Entity_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Entity_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="CSO_ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="CSO_ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1023" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanNum" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Logical Operation="OR">
<ScalarOperator>
<Compare CompareOp="NE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanType" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1024" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanType" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="40" EstimateCPU="0.105077" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Sort" NodeId="7" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.16065">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanType" />
<ColumnReference Column="Expr1023" />
</OutputList>
<MemoryFractions Input="1" Output="0.468468" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Entity_ID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="CSO_ID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Column="Expr1023" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="40" EstimateCPU="0.0005179" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Compute Scalar" NodeId="8" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0443116">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanType" />
<ColumnReference Column="Expr1023" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1023" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(75),[VanGogh_Dev].[dbo].[LoanAcct].[LoanNum] as [LA].[LoanNum],0)">
<Convert DataType="nvarchar" Length="150" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanNum" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="40" EstimateCPU="0.0058539" EstimateIO="0.0379398" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Clustered Index Scan" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0437937" TableCardinality="5179">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanType" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanAcct_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="Entity_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanNum" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="CSO_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Alias="[LA]" Column="LoanType" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[LoanAcct]" Index="[PK__LoanAcct__55727D803EF22DE0]" Alias="[LA]" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
</Sort>
</RelOp>
<RelOp AvgRowSize="52" EstimateCPU="0.0005179" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Compute Scalar" NodeId="13" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.173983">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanType" />
<ColumnReference Column="Expr1024" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1024" />
<ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[VanGogh_Dev].[dbo].[Loan_Stage].[LoanType] as [LS].[LoanType],0)">
<Convert DataType="int" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanType" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="48" EstimateCPU="0.105077" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Sort" NodeId="14" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.173465">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanType" />
</OutputList>
<MemoryFractions Input="0.513514" Output="0.513514" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Entity_ID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="CSO_ID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanNum" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="48" EstimateCPU="0.0058539" EstimateIO="0.0512731" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5179" LogicalOp="Table Scan" NodeId="15" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.057127" TableCardinality="5179">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanNum" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Entity_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="CSO_ID" />
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanType" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="5179" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanNum" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="Entity_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="CSO_ID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" Column="LoanType" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Loan_Stage]" Alias="[LS]" IndexKind="Heap" />
</TableScan>
</RelOp>
</Sort>
</RelOp>
</ComputeScalar>
</RelOp>
</Merge>
</RelOp>
</Sort>
</RelOp>
</ComputeScalar>
</RelOp>
</Top>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="4.8E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0.2285" EstimateRows="1" LogicalOp="Assert" NodeId="24" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="0.0645808">
<OutputList>
<ColumnReference Column="Expr1019" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<Assert StartupExpression="false">
<RelOp AvgRowSize="19" EstimateCPU="1.1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0.2285" EstimateRows="1" LogicalOp="Aggregate" NodeId="25" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.0645802">
<OutputList>
<ColumnReference Column="Expr1018" />
<ColumnReference Column="Expr1019" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1018" />
<ScalarOperator ScalarString="Count(*)">
<Aggregate AggType="countstar" Distinct="false" />
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1019" />
<ScalarOperator ScalarString="ANY([VanGogh_Dev].[dbo].[Entity].[Entity_ID])">
<Aggregate AggType="ANY" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Column="Entity_ID" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="35" EstimateCPU="0.0046534" EstimateIO="0.0528331" EstimateRebinds="0" EstimateRewinds="0.2285" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="26" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0585498" TableCardinality="4159">
<OutputList>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Column="Entity_ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Column="Entity_ID" />
</DefinedValue>
</DefinedValues>
<Object Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Index="[PK__Entity__9BC0DE463B219CFC]" IndexKind="Clustered" />
<Predicate>
<ScalarOperator ScalarString="[VanGogh_Dev].[dbo].[Entity].[EntityType]='System' AND [VanGogh_Dev].[dbo].[Entity].[Fname]='Client Name Withheld' AND [VanGogh_Dev].[dbo].[Entity].[Lname]='Import'">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Column="EntityType" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'System'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Column="Fname" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'Client Name Withheld'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[VanGogh_Dev]" Schema="[dbo]" Table="[Entity]" Column="Lname" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'Import'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</StreamAggregate>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN [Expr1018]>(1) THEN (0) ELSE NULL END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1018" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Predicate>
</Assert>
</RelOp>
</NestedLoops>
</RelOp>
</ComputeScalar>
</RelOp>
</Update>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
As I noted earlier, I ran a similar process without the replacement logic on nearly the same amount of records. It runs in about a second.
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
January 11, 2012 at 5:18 pm
For giggles, try an OPTION (MAXDOP 1) on the query.
To attach the .sqlplan, rt-click on the execution plan. Save execution plan as... (first option). Should pull up an explorer window with filetype selected of .sqlplan. Save this somewhere. When you're here on the site and responding, down below the posting area you'll see "Edit Attachments". Click this, browse, and then include your .sqlplan to the message.
EDIT: Also, can you script out the schema and indexes on these two tables:
dbo.Temp_Cust
dbo.Entity
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 11, 2012 at 5:47 pm
Please post the sqlplan following the instructions provided by Craig. There is an error somewhere in the xml that you posted.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 12, 2012 at 11:10 am
Ok, to do this with all my cards on the table I have altered the scripts to remove any identifying company names, etc.
Create a database called VanGogh_Dev, then run the attached scripts in order.
Both execution plans have been included as attachments.
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
January 12, 2012 at 12:01 pm
I'm having trouble getting those scripts to copy-and-paste cleanly, because of their size. Think you could post them as .txt file attachments, instead of in the body of the post?
(If you remove them from the post, the page will also load a lot faster.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2012 at 12:20 pm
I made the requested changes.
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
January 12, 2012 at 12:41 pm
I must be missing something.
I ran the create script as posted. I ran the populate script as posted. It looks like c. 5000 records in Temp_Cust, and a few rows here and there in a few other tables (though most of the tables are empty).
Then I added "SET STATISTIC TIME, IO ON" at the top of the "05 Import Loan Data to LoanAcct" script, and it ran in under 1 second, but then ended up with unclosed transactions. I added "WHILE @@TRANCOUNT > 0 COMMIT" to the end of it, and it finishes in under 1 second.
But even after that, there don't appear to be any rowcount changes in any tables. Is that correct?
Am I missing something, or is that the behavior you're seeing?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2012 at 12:58 pm
05 Import To Loan Account should populate dbo.LoanAcct with all 5306 records.
What I'm seeing is a massive delay in the processing time from 04 to 05.
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
January 12, 2012 at 1:13 pm
Ok I just looked like I didn't know what I was talking about on part of the question in my last post...because I didn't know what I was talking about in part of the question asked of me. I used the live data instead of the data posted for public use, but I have attached what I'm looking at. I included the next script which is running even worse, but uses the same logic. Don't worry about getting test data for that one, I'm just adding the results in if they will help.
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
January 12, 2012 at 2:44 pm
Do I need to run file 04 before running 05, or are they separate processes?
I may have misread it. I thought 04 and 05 were two versions of getting the end-result you need, not two steps towards one end result.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2012 at 2:48 pm
Correct, they are 2 steps. Running step 4 will populate data needed for step 5.
[font="Arial"]“Any fool can know. The point is to understand.”
- Albert Einstein
"DOH!"
- Homer Simpson[/font]
January 19, 2012 at 7:38 am
I haven't had time to get back to this one yet. Sorry about that.
The next step I'd take on it is running step five (that's the slow one, right?) with IO and time stats turned on, and see which piece of it is eating up the time and/or hogging the scans/reads.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply