December 8, 2014 at 9:05 am
I have a query that takes 20 seconds. I need to speed it up. Table is partitioned and indexes are in place. I've noticed that the majority of the costs percentages is with a Sort and a Merge Join. Is there anything I can do to speed this up? The execution plan XML is below:
<?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.6000.34" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="4" StatementEstRows="11190800000" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="4879270" StatementText="WITH GroupNetworkCTE AS ( SELECT ID FROM OPENXML (@hdoc, '/NewDataSet/Provider',1) WITH ([ID] varchar(15), [IDType] varchar(15)) ), pcpMemberCTE AS ( SELECT m.* FROM Iport.Member m ( NOLOCK ) INNER JOIN GroupNetworkCTE g ON m.CurrentPCPProviderID = g.ID ), claimCTE as ( SELECT clm.ClaimID , clm.MemberRegionCodeDescription , clm.ClaimProviderName , clm.ClaimStatusCodeDescription , clm.ClaimBilledAmount , clm.PaymentAmount , clm.CopayAmt , clm.CoinInsuranceAmount , clm.MaxFeeAmount , clm.CheckNumber , clm.ProviderNPI , clm.ClaimProviderId , clm.SourceDataKey , clm.MemberID , clm.BeginServiceDate , clm.EndServiceDate , clm.PaidDate , clm.CheckDate , mem.ReverseFullName , ISNULL(mem.EmployeeFlag, 0) as EmployeeFlag FROM IPort.claim clm WITH(NOLOCK) LEFT JOIN pcpMemberCTE mem WITH(NOLOCK) ON mem.MemberID = clm.MemberID where ClaimStatusCodeDescription = 'PAID' ) SELECT clm.* from claimCTE clm INNER JOIN GroupNetworkCTE gn ON clm.ClaimProviderId = gn.ID UNION SELECT clm.* from claimCTE clm INNER JOIN GroupNetworkCTE gn ON clm.ProviderNPI = gn.ID UNION SELECT clm.* from claimCTE clm INNER JOIN pcpMemberCTE mem ON mem.MemberID = clm.MemberID OPTION(OPTIMIZE FOR UNKNOWN) " StatementType="SELECT" QueryHash="0xF35F66B9FCA1E669" QueryPlanHash="0xF7DCE3D5E23679ED">
<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="1335480" CachedPlanSize="472" CompileTime="141" CompileCPU="141" CompileMemory="6560">
<RelOp AvgRowSize="269" EstimateCPU="24619.7" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="11190800000" LogicalOp="Union" NodeId="0" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="4879270">
<OutputList>
<ColumnReference Column="Union1052" />
<ColumnReference Column="Union1053" />
<ColumnReference Column="Union1054" />
<ColumnReference Column="Union1055" />
<ColumnReference Column="Union1056" />
<ColumnReference Column="Union1057" />
<ColumnReference Column="Union1058" />
<ColumnReference Column="Union1059" />
<ColumnReference Column="Union1060" />
<ColumnReference Column="Union1061" />
<ColumnReference Column="Union1062" />
<ColumnReference Column="Union1063" />
<ColumnReference Column="Union1064" />
<ColumnReference Column="Union1065" />
<ColumnReference Column="Union1066" />
<ColumnReference Column="Union1067" />
<ColumnReference Column="Union1068" />
<ColumnReference Column="Union1069" />
<ColumnReference Column="Union1070" />
<ColumnReference Column="Union1071" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="4" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Merge>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Union1052" />
<ColumnReference Column="Union1052" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1053" />
<ColumnReference Column="Union1053" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberRegionCodeDescription" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1054" />
<ColumnReference Column="Union1054" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderName" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1055" />
<ColumnReference Column="Union1055" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimStatusCodeDescription" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1056" />
<ColumnReference Column="Union1056" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimBilledAmount" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1057" />
<ColumnReference Column="Union1057" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaymentAmount" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1058" />
<ColumnReference Column="Union1058" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CopayAmt" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1059" />
<ColumnReference Column="Union1059" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CoinInsuranceAmount" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1060" />
<ColumnReference Column="Union1060" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MaxFeeAmount" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1061" />
<ColumnReference Column="Union1061" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckNumber" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1062" />
<ColumnReference Column="Union1062" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1063" />
<ColumnReference Column="Union1063" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1064" />
<ColumnReference Column="Union1064" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="SourceDataKey" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1065" />
<ColumnReference Column="Union1065" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1066" />
<ColumnReference Column="Union1066" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="BeginServiceDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1067" />
<ColumnReference Column="Union1067" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="EndServiceDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1068" />
<ColumnReference Column="Union1068" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaidDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1069" />
<ColumnReference Column="Union1069" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1070" />
<ColumnReference Column="Union1070" />
<ColumnReference Column="Expr1043" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1071" />
<ColumnReference Column="Union1071" />
<ColumnReference Column="Expr1045" />
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="269" EstimateCPU="24619.7" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="11190800000" LogicalOp="Union" NodeId="1" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="4879270">
<OutputList>
<ColumnReference Column="Union1052" />
<ColumnReference Column="Union1053" />
<ColumnReference Column="Union1054" />
<ColumnReference Column="Union1055" />
<ColumnReference Column="Union1056" />
<ColumnReference Column="Union1057" />
<ColumnReference Column="Union1058" />
<ColumnReference Column="Union1059" />
<ColumnReference Column="Union1060" />
<ColumnReference Column="Union1061" />
<ColumnReference Column="Union1062" />
<ColumnReference Column="Union1063" />
<ColumnReference Column="Union1064" />
<ColumnReference Column="Union1065" />
<ColumnReference Column="Union1066" />
<ColumnReference Column="Union1067" />
<ColumnReference Column="Union1068" />
<ColumnReference Column="Union1069" />
<ColumnReference Column="Union1070" />
<ColumnReference Column="Union1071" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="4" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Merge>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Union1052" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1053" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberRegionCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberRegionCodeDescription" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1054" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderName" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderName" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1055" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimStatusCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimStatusCodeDescription" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1056" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimBilledAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimBilledAmount" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1057" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaymentAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaymentAmount" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1058" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CopayAmt" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CopayAmt" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1059" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CoinInsuranceAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CoinInsuranceAmount" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1060" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MaxFeeAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MaxFeeAmount" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1061" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckNumber" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckNumber" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1062" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1063" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1064" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="SourceDataKey" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="SourceDataKey" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1065" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1066" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="BeginServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="BeginServiceDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1067" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="EndServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="EndServiceDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1068" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaidDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaidDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1069" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1070" />
<ColumnReference Column="Expr1011" />
<ColumnReference Column="Expr1027" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Union1071" />
<ColumnReference Column="Expr1013" />
<ColumnReference Column="Expr1029" />
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="222" EstimateCPU="150068" EstimateIO="702865" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1997260000" LogicalOp="Distinct Sort" NodeId="2" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="856305">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="BeginServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="EndServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckNumber" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberRegionCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderName" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimStatusCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimBilledAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaymentAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CopayAmt" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CoinInsuranceAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MaxFeeAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaidDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="SourceDataKey" />
<ColumnReference Column="Expr1011" />
<ColumnReference Column="Expr1013" />
</OutputList>
<MemoryFractions Input="0.999384" Output="0.178397" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="2" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="true">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberRegionCodeDescription" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderName" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimBilledAmount" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaymentAmount" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CopayAmt" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CoinInsuranceAmount" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MaxFeeAmount" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckNumber" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="SourceDataKey" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="BeginServiceDate" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="EndServiceDate" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaidDate" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckDate" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Column="Expr1011" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Column="Expr1013" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="222" EstimateCPU="199.726" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1997260000" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="3372.54">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="BeginServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="EndServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckNumber" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberRegionCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderName" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimStatusCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimBilledAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaymentAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CopayAmt" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CoinInsuranceAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MaxFeeAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaidDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="SourceDataKey" />
<ColumnReference Column="Expr1011" />
<ColumnReference Column="Expr1013" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1013" />
<ScalarOperator ScalarString="isnull([Expr1012],(0))">
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1012" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="222" EstimateCPU="2748.73" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1997260000" LogicalOp="Right Outer Join" NodeId="4" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="3172.81">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="BeginServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="EndServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckNumber" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberRegionCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderName" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimStatusCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimBilledAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaymentAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CopayAmt" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CoinInsuranceAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MaxFeeAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaidDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="SourceDataKey" />
<ColumnReference Column="Expr1011" />
<ColumnReference Column="Expr1012" />
</OutputList>
<MemoryFractions Input="0.923857" Output="0.000568989" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="MemberID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[Portal].[IPort].[Member].[MemberID] as [m].[MemberID]=[Portal].[IPort].[Claim].[MemberID] as [clm].[MemberID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="MemberID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="58" EstimateCPU="7.13696" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1222420" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="32.7712">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="MemberID" />
<ColumnReference Column="Expr1011" />
<ColumnReference Column="Expr1012" />
</OutputList>
<MemoryFractions Input="1" Output="0.0761435" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Table="[OpenXML]" Column="ID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="CurrentPCPProviderID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[OpenXML].[ID]=[Portal].[IPort].[Member].[CurrentPCPProviderID] as [m].[CurrentPCPProviderID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Table="[OpenXML]" Column="ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="CurrentPCPProviderID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="18" EstimateCPU="3.36333" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10000" LogicalOp="Remote Scan" NodeId="6" Parallel="false" PhysicalOp="Remote Scan" EstimatedTotalSubtreeCost="3.36333">
<OutputList>
<ColumnReference Table="[OpenXML]" Column="ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="302" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<RemoteScan />
</RelOp>
<RelOp AvgRowSize="67" EstimateCPU="0.0844738" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="844738" LogicalOp="Compute Scalar" NodeId="7" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="22.2709">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="MemberID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="CurrentPCPProviderID" />
<ColumnReference Column="Expr1011" />
<ColumnReference Column="Expr1012" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1011" />
<ScalarOperator ScalarString="[Portal].[IPort].[Member].[ReverseFullName] as [m].[ReverseFullName]">
<Identifier>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="ReverseFullName" />
</Identifier>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1012" />
<ScalarOperator ScalarString="[Portal].[IPort].[Member].[EmployeeFlag] as [m].[EmployeeFlag]">
<Identifier>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="EmployeeFlag" />
</Identifier>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="47" EstimateCPU="0.940987" EstimateIO="21.2455" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="844738" LogicalOp="Clustered Index Scan" NodeId="8" Parallel="false" Partitioned="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="22.1865" TableCardinality="844738">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="MemberID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="ReverseFullName" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="EmployeeFlag" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="CurrentPCPProviderID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="844738" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<RunTimePartitionSummary>
<PartitionsAccessed PartitionCount="75">
<PartitionRange Start="1" End="75" />
</PartitionsAccessed>
</RunTimePartitionSummary>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="MemberID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="ReverseFullName" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="EmployeeFlag" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="CurrentPCPProviderID" />
</DefinedValue>
</DefinedValues>
<Object Database="[Portal]" Schema="[IPort]" Table="[Member]" Index="[INDX1Member]" Alias="[m]" TableReferenceId="1" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
</Hash>
</RelOp>
<RelOp AvgRowSize="186" EstimateCPU="99.5256" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2222270" LogicalOp="Inner Join" NodeId="15" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="391.306">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="BeginServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="EndServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckNumber" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberRegionCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderName" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimStatusCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimBilledAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaymentAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CopayAmt" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CoinInsuranceAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MaxFeeAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaidDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="SourceDataKey" />
</OutputList>
<MemoryFractions Input="0.0761435" Output="4.68956E-05" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Table="[OpenXML]" Column="ID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[Portal].[IPort].[Claim].[ClaimProviderID] as [clm].[ClaimProviderID]=[OpenXML].[ID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Table="[OpenXML]" Column="ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="18" EstimateCPU="3.36333" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10000" LogicalOp="Remote Scan" NodeId="16" Parallel="false" PhysicalOp="Remote Scan" EstimatedTotalSubtreeCost="3.36333">
<OutputList>
<ColumnReference Table="[OpenXML]" Column="ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="302" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<RemoteScan />
</RelOp>
<RelOp AvgRowSize="186" EstimateCPU="16.3752" EstimateIO="264.913" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="14827700" LogicalOp="Index Scan" NodeId="17" Parallel="false" Partitioned="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="281.288" TableCardinality="14851000">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="BeginServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="EndServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckNumber" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberRegionCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderName" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimStatusCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimBilledAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaymentAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CopayAmt" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CoinInsuranceAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MaxFeeAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaidDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="SourceDataKey" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="14827726" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<RunTimePartitionSummary>
<PartitionsAccessed PartitionCount="249">
<PartitionRange Start="1" End="249" />
</PartitionsAccessed>
</RunTimePartitionSummary>
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="BeginServiceDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="EndServiceDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckNumber" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberRegionCodeDescription" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderName" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimStatusCodeDescription" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimBilledAmount" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaymentAmount" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CopayAmt" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CoinInsuranceAmount" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MaxFeeAmount" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaidDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="SourceDataKey" />
</DefinedValue>
</DefinedValues>
<Object Database="[Portal]" Schema="[IPort]" Table="[Claim]" Index="[IX_EndServiceDate_Includes]" Alias="[clm]" TableReferenceId="1" IndexKind="NonClustered" />
<Predicate>
<ScalarOperator ScalarString="[Portal].[IPort].[Claim].[ClaimStatusCodeDescription] as [clm].[ClaimStatusCodeDescription]='PAID'">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimStatusCodeDescription" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'PAID'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Hash>
</RelOp>
</Hash>
</RelOp>
</ComputeScalar>
</RelOp>
</Sort>
</RelOp>
<RelOp AvgRowSize="222" EstimateCPU="184053" EstimateIO="848010" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2409710000" LogicalOp="Distinct Sort" NodeId="23" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="1036040">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="BeginServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="EndServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckNumber" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberRegionCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderName" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimStatusCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimBilledAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaymentAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CopayAmt" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CoinInsuranceAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MaxFeeAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaidDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="SourceDataKey" />
<ColumnReference Column="Expr1027" />
<ColumnReference Column="Expr1029" />
</OutputList>
<MemoryFractions Input="0.215237" Output="0.215347" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="4" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="true">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberRegionCodeDescription" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderName" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimBilledAmount" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaymentAmount" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CopayAmt" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CoinInsuranceAmount" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MaxFeeAmount" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckNumber" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="SourceDataKey" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="BeginServiceDate" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="EndServiceDate" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaidDate" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckDate" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Column="Expr1027" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Column="Expr1029" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="222" EstimateCPU="240.971" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2409710000" LogicalOp="Compute Scalar" NodeId="24" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="3972.09">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="BeginServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="EndServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckNumber" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberRegionCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderName" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimStatusCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimBilledAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaymentAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CopayAmt" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CoinInsuranceAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MaxFeeAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaidDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="SourceDataKey" />
<ColumnReference Column="Expr1027" />
<ColumnReference Column="Expr1029" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1029" />
<ScalarOperator ScalarString="isnull([Expr1028],(0))">
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1028" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="222" EstimateCPU="3306.47" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2409710000" LogicalOp="Right Outer Join" NodeId="25" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="3731.12">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="BeginServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="EndServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckNumber" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberRegionCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderName" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimStatusCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimBilledAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaymentAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CopayAmt" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CoinInsuranceAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MaxFeeAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaidDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="SourceDataKey" />
<ColumnReference Column="Expr1027" />
<ColumnReference Column="Expr1028" />
</OutputList>
<MemoryFractions Input="0.19895" Output="0.000101569" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="4" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="MemberID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[Portal].[IPort].[Member].[MemberID] as [m].[MemberID]=[Portal].[IPort].[Claim].[MemberID] as [clm].[MemberID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="MemberID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="58" EstimateCPU="7.13696" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1222420" LogicalOp="Inner Join" NodeId="26" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="32.7712">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="MemberID" />
<ColumnReference Column="Expr1027" />
<ColumnReference Column="Expr1028" />
</OutputList>
<MemoryFractions Input="0.215347" Output="0.0163973" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Table="[OpenXML]" Column="ID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="CurrentPCPProviderID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[OpenXML].[ID]=[Portal].[IPort].[Member].[CurrentPCPProviderID] as [m].[CurrentPCPProviderID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Table="[OpenXML]" Column="ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="CurrentPCPProviderID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="18" EstimateCPU="3.36333" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10000" LogicalOp="Remote Scan" NodeId="27" Parallel="false" PhysicalOp="Remote Scan" EstimatedTotalSubtreeCost="3.36333">
<OutputList>
<ColumnReference Table="[OpenXML]" Column="ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="302" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<RemoteScan />
</RelOp>
<RelOp AvgRowSize="67" EstimateCPU="0.0844738" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="844738" LogicalOp="Compute Scalar" NodeId="28" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="22.2709">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="MemberID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="CurrentPCPProviderID" />
<ColumnReference Column="Expr1027" />
<ColumnReference Column="Expr1028" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1027" />
<ScalarOperator ScalarString="[Portal].[IPort].[Member].[ReverseFullName] as [m].[ReverseFullName]">
<Identifier>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="ReverseFullName" />
</Identifier>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1028" />
<ScalarOperator ScalarString="[Portal].[IPort].[Member].[EmployeeFlag] as [m].[EmployeeFlag]">
<Identifier>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="EmployeeFlag" />
</Identifier>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="47" EstimateCPU="0.940987" EstimateIO="21.2455" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="844738" LogicalOp="Clustered Index Scan" NodeId="29" Parallel="false" Partitioned="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="22.1865" TableCardinality="844738">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="MemberID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="ReverseFullName" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="EmployeeFlag" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="CurrentPCPProviderID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="844738" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<RunTimePartitionSummary>
<PartitionsAccessed PartitionCount="75">
<PartitionRange Start="1" End="75" />
</PartitionsAccessed>
</RunTimePartitionSummary>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="MemberID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="ReverseFullName" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="EmployeeFlag" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="CurrentPCPProviderID" />
</DefinedValue>
</DefinedValues>
<Object Database="[Portal]" Schema="[IPort]" Table="[Member]" Index="[INDX1Member]" Alias="[m]" TableReferenceId="2" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
</Hash>
</RelOp>
<RelOp AvgRowSize="186" EstimateCPU="100.1" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2746890" LogicalOp="Inner Join" NodeId="36" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="391.88">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="BeginServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="EndServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckNumber" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberRegionCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderName" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimStatusCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimBilledAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaymentAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CopayAmt" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CoinInsuranceAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MaxFeeAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaidDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="SourceDataKey" />
</OutputList>
<MemoryFractions Input="0.0163973" Output="8.3712E-06" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="4" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Table="[OpenXML]" Column="ID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[Portal].[IPort].[Claim].[ProviderNPI] as [clm].[ProviderNPI]=[OpenXML].[ID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Table="[OpenXML]" Column="ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="18" EstimateCPU="3.36333" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10000" LogicalOp="Remote Scan" NodeId="37" Parallel="false" PhysicalOp="Remote Scan" EstimatedTotalSubtreeCost="3.36333">
<OutputList>
<ColumnReference Table="[OpenXML]" Column="ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="302" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<RemoteScan />
</RelOp>
<RelOp AvgRowSize="186" EstimateCPU="16.3752" EstimateIO="264.913" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="14827700" LogicalOp="Index Scan" NodeId="38" Parallel="false" Partitioned="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="281.288" TableCardinality="14851000">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="BeginServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="EndServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckNumber" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberRegionCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderName" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimStatusCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimBilledAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaymentAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CopayAmt" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CoinInsuranceAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MaxFeeAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaidDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="SourceDataKey" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="14827726" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<RunTimePartitionSummary>
<PartitionsAccessed PartitionCount="249">
<PartitionRange Start="1" End="249" />
</PartitionsAccessed>
</RunTimePartitionSummary>
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="BeginServiceDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="EndServiceDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckNumber" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberRegionCodeDescription" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderName" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimStatusCodeDescription" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimBilledAmount" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaymentAmount" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CopayAmt" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CoinInsuranceAmount" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MaxFeeAmount" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaidDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="SourceDataKey" />
</DefinedValue>
</DefinedValues>
<Object Database="[Portal]" Schema="[IPort]" Table="[Claim]" Index="[IX_EndServiceDate_Includes]" Alias="[clm]" TableReferenceId="2" IndexKind="NonClustered" />
<Predicate>
<ScalarOperator ScalarString="[Portal].[IPort].[Claim].[ClaimStatusCodeDescription] as [clm].[ClaimStatusCodeDescription]='PAID'">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimStatusCodeDescription" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'PAID'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Hash>
</RelOp>
</Hash>
</RelOp>
</ComputeScalar>
</RelOp>
</Sort>
</RelOp>
</Merge>
</RelOp>
<RelOp AvgRowSize="222" EstimateCPU="564644" EstimateIO="2387330" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="6783820000" LogicalOp="Distinct Sort" NodeId="44" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="2962310">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="BeginServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="EndServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckNumber" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberRegionCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderName" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimStatusCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimBilledAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaymentAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CopayAmt" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CoinInsuranceAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MaxFeeAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaidDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="SourceDataKey" />
<ColumnReference Column="Expr1043" />
<ColumnReference Column="Expr1045" />
</OutputList>
<MemoryFractions Input="0.605938" Output="0.606256" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="true">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberRegionCodeDescription" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderName" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimBilledAmount" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaymentAmount" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CopayAmt" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CoinInsuranceAmount" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MaxFeeAmount" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckNumber" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="SourceDataKey" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="BeginServiceDate" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="EndServiceDate" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaidDate" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckDate" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Column="Expr1043" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Column="Expr1045" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="222" EstimateCPU="678.382" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="6783820000" LogicalOp="Compute Scalar" NodeId="45" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="10344.3">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="BeginServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="EndServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckNumber" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberRegionCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderName" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimStatusCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimBilledAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaymentAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CopayAmt" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CoinInsuranceAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MaxFeeAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaidDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="SourceDataKey" />
<ColumnReference Column="Expr1043" />
<ColumnReference Column="Expr1045" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1045" />
<ScalarOperator ScalarString="isnull([Expr1044],(0))">
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1044" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="222" EstimateCPU="9199.78" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="6783820000" LogicalOp="Right Outer Join" NodeId="46" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="9665.96">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="BeginServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="EndServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckNumber" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberRegionCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderName" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimStatusCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimBilledAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaymentAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CopayAmt" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CoinInsuranceAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MaxFeeAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaidDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="SourceDataKey" />
<ColumnReference Column="Expr1043" />
<ColumnReference Column="Expr1044" />
</OutputList>
<MemoryFractions Input="0.283609" Output="0.000149663" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="MemberID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[Portal].[IPort].[Member].[MemberID] as [m].[MemberID]=[Portal].[IPort].[Claim].[MemberID] as [clm].[MemberID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="MemberID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="58" EstimateCPU="7.13696" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1222420" LogicalOp="Inner Join" NodeId="47" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="32.7712">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="MemberID" />
<ColumnReference Column="Expr1043" />
<ColumnReference Column="Expr1044" />
</OutputList>
<MemoryFractions Input="0.606256" Output="0.322647" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Table="[OpenXML]" Column="ID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="CurrentPCPProviderID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[OpenXML].[ID]=[Portal].[IPort].[Member].[CurrentPCPProviderID] as [m].[CurrentPCPProviderID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Table="[OpenXML]" Column="ID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="CurrentPCPProviderID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="18" EstimateCPU="3.36333" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10000" LogicalOp="Remote Scan" NodeId="48" Parallel="false" PhysicalOp="Remote Scan" EstimatedTotalSubtreeCost="3.36333">
<OutputList>
<ColumnReference Table="[OpenXML]" Column="ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="302" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<RemoteScan />
</RelOp>
<RelOp AvgRowSize="67" EstimateCPU="0.0844738" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="844738" LogicalOp="Compute Scalar" NodeId="49" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="22.2709">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="MemberID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="CurrentPCPProviderID" />
<ColumnReference Column="Expr1043" />
<ColumnReference Column="Expr1044" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1043" />
<ScalarOperator ScalarString="[Portal].[IPort].[Member].[ReverseFullName] as [m].[ReverseFullName]">
<Identifier>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="ReverseFullName" />
</Identifier>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1044" />
<ScalarOperator ScalarString="[Portal].[IPort].[Member].[EmployeeFlag] as [m].[EmployeeFlag]">
<Identifier>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="EmployeeFlag" />
</Identifier>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="47" EstimateCPU="0.940987" EstimateIO="21.2455" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="844738" LogicalOp="Clustered Index Scan" NodeId="50" Parallel="false" Partitioned="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="22.1865" TableCardinality="844738">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="MemberID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="ReverseFullName" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="EmployeeFlag" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="CurrentPCPProviderID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="844738" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<RunTimePartitionSummary>
<PartitionsAccessed PartitionCount="75">
<PartitionRange Start="1" End="75" />
</PartitionsAccessed>
</RunTimePartitionSummary>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="MemberID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="ReverseFullName" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="EmployeeFlag" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="CurrentPCPProviderID" />
</DefinedValue>
</DefinedValues>
<Object Database="[Portal]" Schema="[IPort]" Table="[Member]" Index="[INDX1Member]" Alias="[m]" TableReferenceId="3" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
</Hash>
</RelOp>
<RelOp AvgRowSize="186" EstimateCPU="127.484" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4852680" LogicalOp="Inner Join" NodeId="57" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="433.417">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="BeginServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="EndServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckNumber" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberRegionCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderName" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimStatusCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimBilledAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaymentAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CopayAmt" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CoinInsuranceAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MaxFeeAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaidDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="SourceDataKey" />
</OutputList>
<MemoryFractions Input="0.318462" Output="0.000168056" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="MemberID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[Portal].[IPort].[Member].[MemberID] as [m].[MemberID]=[Portal].[IPort].[Claim].[MemberID] as [clm].[MemberID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="MemberID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="22" EstimateCPU="7.13696" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1222420" LogicalOp="Inner Join" NodeId="58" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="17.5164">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="MemberID" />
</OutputList>
<MemoryFractions Input="0.322647" Output="0.00418478" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Hash>
<DefinedValues />
<HashKeysBuild>
<ColumnReference Table="[OpenXML]" Column="ID" />
</HashKeysBuild>
<HashKeysProbe>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="CurrentPCPProviderID" />
</HashKeysProbe>
<ProbeResidual>
<ScalarOperator ScalarString="[Portal].[IPort].[Member].[CurrentPCPProviderID] as [m].[CurrentPCPProviderID]=[OpenXML].[ID]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="CurrentPCPProviderID" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Table="[OpenXML]" Column="ID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</ProbeResidual>
<RelOp AvgRowSize="18" EstimateCPU="3.36333" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10000" LogicalOp="Remote Scan" NodeId="59" Parallel="false" PhysicalOp="Remote Scan" EstimatedTotalSubtreeCost="3.36333">
<OutputList>
<ColumnReference Table="[OpenXML]" Column="ID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="302" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<RemoteScan />
</RelOp>
<RelOp AvgRowSize="31" EstimateCPU="0.940987" EstimateIO="6.07512" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="844738" LogicalOp="Index Scan" NodeId="60" Parallel="false" Partitioned="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="7.0161" TableCardinality="844738">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="MemberID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="CurrentPCPProviderID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="844738" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<RunTimePartitionSummary>
<PartitionsAccessed PartitionCount="75">
<PartitionRange Start="1" End="75" />
</PartitionsAccessed>
</RunTimePartitionSummary>
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="MemberID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Member]" Alias="[m]" Column="CurrentPCPProviderID" />
</DefinedValue>
</DefinedValues>
<Object Database="[Portal]" Schema="[IPort]" Table="[Member]" Index="[ix_IPort_Member_CurrentPCPProviderID_Product]" Alias="[m]" TableReferenceId="4" IndexKind="NonClustered" />
</IndexScan>
</RelOp>
</Hash>
</RelOp>
<RelOp AvgRowSize="186" EstimateCPU="16.3752" EstimateIO="264.913" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="14827700" LogicalOp="Index Scan" NodeId="62" Parallel="false" Partitioned="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="281.288" TableCardinality="14851000">
<OutputList>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="BeginServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="EndServiceDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckNumber" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberRegionCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderName" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimStatusCodeDescription" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimBilledAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaymentAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CopayAmt" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CoinInsuranceAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MaxFeeAmount" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaidDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckDate" />
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="SourceDataKey" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<RunTimePartitionSummary>
<PartitionsAccessed PartitionCount="0" />
</RunTimePartitionSummary>
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ProviderNPI" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="BeginServiceDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="EndServiceDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckNumber" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MemberRegionCodeDescription" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimProviderName" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimStatusCodeDescription" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimBilledAmount" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaymentAmount" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CopayAmt" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CoinInsuranceAmount" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="MaxFeeAmount" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="PaidDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="CheckDate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="SourceDataKey" />
</DefinedValue>
</DefinedValues>
<Object Database="[Portal]" Schema="[IPort]" Table="[Claim]" Index="[IX_EndServiceDate_Includes]" Alias="[clm]" TableReferenceId="3" IndexKind="NonClustered" />
<Predicate>
<ScalarOperator ScalarString="[Portal].[IPort].[Claim].[ClaimStatusCodeDescription] as [clm].[ClaimStatusCodeDescription]='PAID'">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[Portal]" Schema="[IPort]" Table="[Claim]" Alias="[clm]" Column="ClaimStatusCodeDescription" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="'PAID'" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Hash>
</RelOp>
</Hash>
</RelOp>
</ComputeScalar>
</RelOp>
</Sort>
</RelOp>
</Merge>
</RelOp>
<ParameterList>
<ColumnReference Column="@hdoc" ParameterRuntimeValue="(21)" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
December 8, 2014 at 9:29 am
Does it make any difference if you rewrite the last part of your query like below? You haven't given us anything to work with, so obviously I haven't been able to test that it even returns the same results.
SELECT clm.* from claimCTE clm
INNER JOIN GroupNetworkCTE gn
ON clm.ClaimProviderId = gn.ID OR clm.ProviderNPI = gn.ID
LEFT JOIN pcpMemberCTE mem ON mem.MemberID = clm.MemberID
John
December 8, 2014 at 9:56 am
Ahh, yeah there was no need for the left join and adding that actually corrected the code as well as speeded up the process. The search that was taking 20 plus seconds actually now returns no records which is correct.
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply