Created
December 15, 2011 20:48
-
-
Save anonymous/1482801 to your computer and use it in GitHub Desktop.
Plan xml - test
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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.1" Build="10.0.5500.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> | |
<BatchSequence> | |
<Batch> | |
<Statements> | |
<StmtSimple StatementCompId="4" StatementEstRows="2" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="4.88842" StatementText="with b as
(
 select distinct
 coalesce(a.STUDY_NUMBER,b.STUDY_NUMBER, c.study_number) as StudyNumber
 from
 (
 SELECT SUBSTRING(PAPROJNUMBER, 1, 5) AS STUDY_NUMBER
 FROM PSQL3.PROD.dbo.PA01201 AS PA01201_1
 where PAPROJNUMBER>'0'
 and PAPROJNUMBER not like '%[a-z]%'
 ) a
 full outer join
 (
 SELECT SUBSTRING(PAPROJNUMBER, 1, 5) AS STUDY_NUMBER
 FROM PSQL3.MO1.dbo.PA01201 AS PA01201_1
 where PAPROJNUMBER>'0'
 and PAPROJNUMBER not like '%[a-z]%'
 ) b
 on a.STUDY_NUMBER=b.STUDY_NUMBER
 full outer join 
 (
 SELECT STUDY_NUMBER
 FROM appdb.ptwdb.dbo.tblARCompletedStudies
 where STUDY_NUMBER>'0'
 ) c
 on a.STUDY_NUMBER=c.study_number
 and b.STUDY_NUMBER=c.study_number
)

select
 snStudyNumber as ProjectNumber,
 c.clName as CompanyName,
 q.quQuoteNumber as QuoteNumber,
 q.quQuoteID as QuoteID,
 q.quDateWon as DateWon,
 s.snPTWCompletionDate as PTWCompletionDate
from CDB.cdb.Quotes q
 inner join CDB.cdb.LineItems l
 on q.PK_quID=l.FK_quID
 inner join CDB.cdb.StudyNumbers s
 on l.FK_snID=s.PK_snId
 inner join cdb.cdb.Clients c
 on q.FK_clID=c.PK_clID
 left outer join ardb.projects p
 on s.snStudyNumber=p.prProjectNumber
 left outer join b
 on s.snStudyNumber=b.studynumber
where q.quDateWon>''
 and s.snPTWCompletionDate >''
 and p.PK_prID is null
 and l.liCreationDate>'12/31/06'
 and b.studynumber is null
union all

select CAST(ProjectNumber AS int) as ProjectNumber,
 null as CompanyName,
 null as QuoteNumber,
 null as QuoteID,
 null as DateWon,
 null as PTWCompletionDate
from history.ProjectsToProcess a
 left outer join ardb.projects p
 on a.ProjectNumber=p.prProjectNumber
where p.PK_prID is null

order by ProjectNumber desc




 
" StatementType="SELECT" QueryHash="0xCAADF2ECF4AE4C21" QueryPlanHash="0x74E27E550A2F55FE"> | |
<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="13616" CachedPlanSize="456" CompileTime="539" CompileCPU="524" CompileMemory="4576"> | |
<RelOp AvgRowSize="4083" EstimateCPU="0.000107203" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2" LogicalOp="Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="4.88842"> | |
<OutputList> | |
<ColumnReference Column="Union1031" /> | |
<ColumnReference Column="Union1032" /> | |
<ColumnReference Column="Union1033" /> | |
<ColumnReference Column="Union1034" /> | |
<ColumnReference Column="Union1035" /> | |
<ColumnReference Column="Union1036" /> | |
</OutputList> | |
<MemoryFractions Input="0.00336134" Output="1" /> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="10" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<Sort Distinct="false"> | |
<OrderBy> | |
<OrderByColumn Ascending="false"> | |
<ColumnReference Column="Union1031" /> | |
</OrderByColumn> | |
</OrderBy> | |
<RelOp AvgRowSize="4083" EstimateCPU="2E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2" LogicalOp="Concatenation" NodeId="1" Parallel="false" PhysicalOp="Concatenation" EstimatedTotalSubtreeCost="4.87705"> | |
<OutputList> | |
<ColumnReference Column="Union1031" /> | |
<ColumnReference Column="Union1032" /> | |
<ColumnReference Column="Union1033" /> | |
<ColumnReference Column="Union1034" /> | |
<ColumnReference Column="Union1035" /> | |
<ColumnReference Column="Union1036" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="10" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<Concat> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Column="Union1031" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
<ColumnReference Column="Expr1025" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Column="Union1032" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Clients]" Alias="[c]" Column="clName" /> | |
<ColumnReference Column="Expr1026" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Column="Union1033" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteNumber" /> | |
<ColumnReference Column="Expr1027" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Column="Union1034" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteID" /> | |
<ColumnReference Column="Expr1028" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Column="Union1035" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quDateWon" /> | |
<ColumnReference Column="Expr1029" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Column="Union1036" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snPTWCompletionDate" /> | |
<ColumnReference Column="Expr1030" /> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="4083" EstimateCPU="0.000290699" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Filter" NodeId="2" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="4.85721"> | |
<OutputList> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quDateWon" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snPTWCompletionDate" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Clients]" Alias="[c]" Column="clName" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="10" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<Filter StartupExpression="false"> | |
<RelOp AvgRowSize="4087" EstimateCPU="0.0817408" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="605.622" LogicalOp="Right Outer Join" NodeId="3" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="4.85692"> | |
<OutputList> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quDateWon" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snPTWCompletionDate" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Clients]" Alias="[c]" Column="clName" /> | |
<ColumnReference Column="Expr1020" /> | |
</OutputList> | |
<MemoryFractions Input="0.112142" Output="0.111765" /> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="4573" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<Hash> | |
<DefinedValues /> | |
<HashKeysBuild> | |
<ColumnReference Column="Expr1123" /> | |
</HashKeysBuild> | |
<HashKeysProbe> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
</HashKeysProbe> | |
<ProbeResidual> | |
<ScalarOperator ScalarString="[CDB].[cdb].[StudyNumbers].[snStudyNumber] as [s].[snStudyNumber]=[Expr1123]"> | |
<Compare CompareOp="EQ"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1123" /> | |
</Identifier> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</ProbeResidual> | |
<RelOp AvgRowSize="17" EstimateCPU="0.000357343" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3573.43" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="2.88578"> | |
<OutputList> | |
<ColumnReference Column="Expr1020" /> | |
<ColumnReference Column="Expr1123" /> | |
</OutputList> | |
<ComputeScalar> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Column="Expr1123" /> | |
<ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1020],0)"> | |
<Convert DataType="int" Style="0" Implicit="true"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1020" /> | |
</Identifier> | |
</ScalarOperator> | |
</Convert> | |
</ScalarOperator> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="13" EstimateCPU="0.113918" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3573.43" LogicalOp="Aggregate" NodeId="5" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="2.88542"> | |
<OutputList> | |
<ColumnReference Column="Expr1020" /> | |
</OutputList> | |
<MemoryFractions Input="0" Output="0" /> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="6737" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<Hash> | |
<DefinedValues /> | |
<HashKeysBuild> | |
<ColumnReference Column="Expr1020" /> | |
</HashKeysBuild> | |
<BuildResidual> | |
<ScalarOperator ScalarString="[Expr1020] = [Expr1020]"> | |
<Compare CompareOp="IS"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1020" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1020" /> | |
</Identifier> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</BuildResidual> | |
<RelOp AvgRowSize="13" EstimateCPU="0.000660626" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="6606.26" LogicalOp="Compute Scalar" NodeId="6" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="2.7715"> | |
<OutputList> | |
<ColumnReference Column="Expr1020" /> | |
</OutputList> | |
<ComputeScalar> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Column="Expr1020" /> | |
<ScalarOperator ScalarString="CASE WHEN [Expr1012] IS NOT NULL THEN [Expr1012] ELSE CASE WHEN [Expr1015] IS NOT NULL THEN [Expr1015] ELSE [Expr1019] END END"> | |
<IF> | |
<Condition> | |
<ScalarOperator> | |
<Compare CompareOp="IS NOT"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1012" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="NULL" /> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</Condition> | |
<Then> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1012" /> | |
</Identifier> | |
</ScalarOperator> | |
</Then> | |
<Else> | |
<ScalarOperator> | |
<IF> | |
<Condition> | |
<ScalarOperator> | |
<Compare CompareOp="IS NOT"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1015" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="NULL" /> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</Condition> | |
<Then> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1015" /> | |
</Identifier> | |
</ScalarOperator> | |
</Then> | |
<Else> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1019" /> | |
</Identifier> | |
</ScalarOperator> | |
</Else> | |
</IF> | |
</ScalarOperator> | |
</Else> | |
</IF> | |
</ScalarOperator> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="21" EstimateCPU="0.0864089" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="6606.26" LogicalOp="Full Outer Join" NodeId="7" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="2.77084"> | |
<OutputList> | |
<ColumnReference Column="Expr1012" /> | |
<ColumnReference Column="Expr1015" /> | |
<ColumnReference Column="Expr1019" /> | |
</OutputList> | |
<MemoryFractions Input="0" Output="0" /> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="6812" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<Hash> | |
<DefinedValues /> | |
<HashKeysBuild> | |
<ColumnReference Server="[appdb]" Database="[ptwdb]" Schema="[dbo]" Table="[tblARCompletedStudies]" Column="STUDY_NUMBER" /> | |
</HashKeysBuild> | |
<HashKeysProbe> | |
<ColumnReference Column="Expr1012" /> | |
</HashKeysProbe> | |
<ProbeResidual> | |
<ScalarOperator ScalarString="[Expr1012]=[appdb].[ptwdb].[dbo].[tblARCompletedStudies].[STUDY_NUMBER] AND [Expr1015]=[appdb].[ptwdb].[dbo].[tblARCompletedStudies].[STUDY_NUMBER]"> | |
<Logical Operation="AND"> | |
<ScalarOperator> | |
<Compare CompareOp="EQ"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1012" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Server="[appdb]" Database="[ptwdb]" Schema="[dbo]" Table="[tblARCompletedStudies]" Column="STUDY_NUMBER" /> | |
</Identifier> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Compare CompareOp="EQ"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1015" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Server="[appdb]" Database="[ptwdb]" Schema="[dbo]" Table="[tblARCompletedStudies]" Column="STUDY_NUMBER" /> | |
</Identifier> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</Logical> | |
</ScalarOperator> | |
</ProbeResidual> | |
<RelOp AvgRowSize="17" EstimateCPU="0.597333" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1762" LogicalOp="Compute Scalar" NodeId="8" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.597333"> | |
<OutputList> | |
<ColumnReference Server="[appdb]" Database="[ptwdb]" Schema="[dbo]" Table="[tblARCompletedStudies]" Column="STUDY_NUMBER" /> | |
<ColumnReference Column="Expr1019" /> | |
</OutputList> | |
<ComputeScalar> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Server="[appdb]" Database="[ptwdb]" Schema="[dbo]" Table="[tblARCompletedStudies]" Column="STUDY_NUMBER" /> | |
<ScalarOperator ScalarString="[appdb].[ptwdb].[dbo].[tblARCompletedStudies].[STUDY_NUMBER]"> | |
<Identifier> | |
<ColumnReference Server="[appdb]" Database="[ptwdb]" Schema="[dbo]" Table="[tblARCompletedStudies]" Column="STUDY_NUMBER" /> | |
</Identifier> | |
</ScalarOperator> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Column="Expr1019" /> | |
<ScalarOperator ScalarString="[Expr1019]"> | |
<Identifier> | |
<ColumnReference Column="Expr1019" /> | |
</Identifier> | |
</ScalarOperator> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="17" EstimateCPU="0.597333" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1762" LogicalOp="Remote Query" NodeId="9" Parallel="false" PhysicalOp="Remote Query" EstimatedTotalSubtreeCost="0.597333"> | |
<OutputList> | |
<ColumnReference Server="[appdb]" Database="[ptwdb]" Schema="[dbo]" Table="[tblARCompletedStudies]" Column="STUDY_NUMBER" /> | |
<ColumnReference Column="Expr1019" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1762" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<RemoteQuery RemoteSource="appdb" RemoteQuery="SELECT "Tbl1018"."STUDY_NUMBER" "Col1084","Tbl1018"."STUDY_NUMBER" "Expr1019" FROM "ptwdb"."dbo"."tblARCompletedStudies" "Tbl1018" WHERE "Tbl1018"."STUDY_NUMBER">'0'" /> | |
</RelOp> | |
</ComputeScalar> | |
</RelOp> | |
<RelOp AvgRowSize="17" EstimateCPU="0.0752689" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4852.89" LogicalOp="Full Outer Join" NodeId="15" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="2.08709"> | |
<OutputList> | |
<ColumnReference Column="Expr1012" /> | |
<ColumnReference Column="Expr1015" /> | |
</OutputList> | |
<MemoryFractions Input="0" Output="0" /> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="5050" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<Hash> | |
<DefinedValues /> | |
<HashKeysBuild> | |
<ColumnReference Column="Expr1043" /> | |
</HashKeysBuild> | |
<HashKeysProbe> | |
<ColumnReference Column="Expr1044" /> | |
</HashKeysProbe> | |
<ProbeResidual> | |
<ScalarOperator ScalarString="[Expr1044]=[Expr1043]"> | |
<Compare CompareOp="EQ"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1044" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1043" /> | |
</Identifier> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</ProbeResidual> | |
<RelOp AvgRowSize="17" EstimateCPU="0.000130296" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1302.96" LogicalOp="Compute Scalar" NodeId="16" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.444579"> | |
<OutputList> | |
<ColumnReference Column="Expr1015" /> | |
<ColumnReference Column="Expr1043" /> | |
</OutputList> | |
<ComputeScalar> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Column="Expr1043" /> | |
<ScalarOperator ScalarString="substring([PSQL3].[MO1].[dbo].[PA01201].[PAPROJNUMBER] as [PA01201_1].[PAPROJNUMBER],(1),(5))"> | |
<Intrinsic FunctionName="substring"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Server="[PSQL3]" Database="[MO1]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="(1)" /> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="(5)" /> | |
</ScalarOperator> | |
</Intrinsic> | |
</ScalarOperator> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="28" EstimateCPU="0.000130296" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1302.96" LogicalOp="Compute Scalar" NodeId="17" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.444449"> | |
<OutputList> | |
<ColumnReference Server="[PSQL3]" Database="[MO1]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
<ColumnReference Column="Expr1015" /> | |
</OutputList> | |
<ComputeScalar> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Column="Expr1015" /> | |
<ScalarOperator ScalarString="substring([PSQL3].[MO1].[dbo].[PA01201].[PAPROJNUMBER] as [PA01201_1].[PAPROJNUMBER],(1),(5))"> | |
<Intrinsic FunctionName="substring"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Server="[PSQL3]" Database="[MO1]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="(1)" /> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="(5)" /> | |
</ScalarOperator> | |
</Intrinsic> | |
</ScalarOperator> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="22" EstimateCPU="0.444319" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1302.96" LogicalOp="Compute Scalar" NodeId="18" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.444319"> | |
<OutputList> | |
<ColumnReference Server="[PSQL3]" Database="[MO1]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
</OutputList> | |
<ComputeScalar> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Server="[PSQL3]" Database="[MO1]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
<ScalarOperator ScalarString="[PSQL3].[MO1].[dbo].[PA01201].[PAPROJNUMBER] as [PA01201_1].[PAPROJNUMBER]"> | |
<Identifier> | |
<ColumnReference Server="[PSQL3]" Database="[MO1]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
</Identifier> | |
</ScalarOperator> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="22" EstimateCPU="0.444319" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1302.96" LogicalOp="Remote Query" NodeId="19" Parallel="false" PhysicalOp="Remote Query" EstimatedTotalSubtreeCost="0.444319"> | |
<OutputList> | |
<ColumnReference Server="[PSQL3]" Database="[MO1]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1412" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<RemoteQuery RemoteSource="PSQL3" RemoteQuery="SELECT "Tbl1014"."PAPROJNUMBER" "Col1092" FROM "MO1"."dbo"."PA01201" "Tbl1014" WHERE "Tbl1014"."PAPROJNUMBER">'0' AND NOT "Tbl1014"."PAPROJNUMBER" like '%[a-z]%' COLLATE SQL_Latin1_General_CP1_CI_AS" /> | |
</RelOp> | |
</ComputeScalar> | |
</RelOp> | |
</ComputeScalar> | |
</RelOp> | |
</ComputeScalar> | |
</RelOp> | |
<RelOp AvgRowSize="17" EstimateCPU="0.000466892" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4668.92" LogicalOp="Compute Scalar" NodeId="29" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.56724"> | |
<OutputList> | |
<ColumnReference Column="Expr1012" /> | |
<ColumnReference Column="Expr1044" /> | |
</OutputList> | |
<ComputeScalar> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Column="Expr1044" /> | |
<ScalarOperator ScalarString="substring([PSQL3].[PROD].[dbo].[PA01201].[PAPROJNUMBER] as [PA01201_1].[PAPROJNUMBER],(1),(5))"> | |
<Intrinsic FunctionName="substring"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Server="[PSQL3]" Database="[PROD]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="(1)" /> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="(5)" /> | |
</ScalarOperator> | |
</Intrinsic> | |
</ScalarOperator> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="28" EstimateCPU="0.000466892" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4668.92" LogicalOp="Compute Scalar" NodeId="30" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.56678"> | |
<OutputList> | |
<ColumnReference Server="[PSQL3]" Database="[PROD]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
<ColumnReference Column="Expr1012" /> | |
</OutputList> | |
<ComputeScalar> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Column="Expr1012" /> | |
<ScalarOperator ScalarString="substring([PSQL3].[PROD].[dbo].[PA01201].[PAPROJNUMBER] as [PA01201_1].[PAPROJNUMBER],(1),(5))"> | |
<Intrinsic FunctionName="substring"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Server="[PSQL3]" Database="[PROD]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="(1)" /> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="(5)" /> | |
</ScalarOperator> | |
</Intrinsic> | |
</ScalarOperator> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="22" EstimateCPU="1.56631" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4668.92" LogicalOp="Compute Scalar" NodeId="31" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.56631"> | |
<OutputList> | |
<ColumnReference Server="[PSQL3]" Database="[PROD]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
</OutputList> | |
<ComputeScalar> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Server="[PSQL3]" Database="[PROD]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
<ScalarOperator ScalarString="[PSQL3].[PROD].[dbo].[PA01201].[PAPROJNUMBER] as [PA01201_1].[PAPROJNUMBER]"> | |
<Identifier> | |
<ColumnReference Server="[PSQL3]" Database="[PROD]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
</Identifier> | |
</ScalarOperator> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="22" EstimateCPU="1.56631" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4668.92" LogicalOp="Remote Query" NodeId="32" Parallel="false" PhysicalOp="Remote Query" EstimatedTotalSubtreeCost="1.56631"> | |
<OutputList> | |
<ColumnReference Server="[PSQL3]" Database="[PROD]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="5047" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<RemoteQuery RemoteSource="PSQL3" RemoteQuery="SELECT "Tbl1011"."PAPROJNUMBER" "Col1111" FROM "PROD"."dbo"."PA01201" "Tbl1011" WHERE "Tbl1011"."PAPROJNUMBER">'0' AND NOT "Tbl1011"."PAPROJNUMBER" like '%[a-z]%' COLLATE SQL_Latin1_General_CP1_CI_AS" /> | |
</RelOp> | |
</ComputeScalar> | |
</RelOp> | |
</ComputeScalar> | |
</RelOp> | |
</ComputeScalar> | |
</RelOp> | |
</Hash> | |
</RelOp> | |
</Hash> | |
</RelOp> | |
</ComputeScalar> | |
</RelOp> | |
</Hash> | |
</RelOp> | |
</ComputeScalar> | |
</RelOp> | |
<RelOp AvgRowSize="4083" EstimateCPU="0.00521205" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="605.622" LogicalOp="Filter" NodeId="51" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="1.8894"> | |
<OutputList> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quDateWon" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snPTWCompletionDate" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Clients]" Alias="[c]" Column="clName" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="4573" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<Filter StartupExpression="false"> | |
<RelOp AvgRowSize="4087" EstimateCPU="0.165911" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10858.4" LogicalOp="Right Outer Join" NodeId="52" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1.88419"> | |
<OutputList> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quDateWon" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snPTWCompletionDate" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Clients]" Alias="[c]" Column="clName" /> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="PK_prID" /> | |
</OutputList> | |
<MemoryFractions Input="0.425801" Output="0.42437" /> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="14446" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<Hash> | |
<DefinedValues /> | |
<HashKeysBuild> | |
<ColumnReference Column="Expr1038" /> | |
</HashKeysBuild> | |
<HashKeysProbe> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
</HashKeysProbe> | |
<ProbeResidual> | |
<ScalarOperator ScalarString="[CDB].[cdb].[StudyNumbers].[snStudyNumber] as [s].[snStudyNumber]=[Expr1038]"> | |
<Compare CompareOp="EQ"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1038" /> | |
</Identifier> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</ProbeResidual> | |
<RelOp AvgRowSize="15" EstimateCPU="0.0003931" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3931" LogicalOp="Compute Scalar" NodeId="53" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0146659"> | |
<OutputList> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="PK_prID" /> | |
<ColumnReference Column="Expr1038" /> | |
</OutputList> | |
<ComputeScalar> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Column="Expr1038" /> | |
<ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[ARDB].[ardb].[Projects].[prProjectNumber] as [p].[prProjectNumber],0)"> | |
<Convert DataType="int" Style="0" Implicit="true"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="prProjectNumber" /> | |
</Identifier> | |
</ScalarOperator> | |
</Convert> | |
</ScalarOperator> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="20" EstimateCPU="0.0044811" EstimateIO="0.00979167" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3931" LogicalOp="Index Scan" NodeId="54" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.0142728" TableCardinality="3931"> | |
<OutputList> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="PK_prID" /> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="prProjectNumber" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="3931" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="PK_prID" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="prProjectNumber" /> | |
</DefinedValue> | |
</DefinedValues> | |
<Object Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Index="[IDX_UniqueProjects]" Alias="[p]" TableReferenceId="1" IndexKind="NonClustered" /> | |
</IndexScan> | |
</RelOp> | |
</ComputeScalar> | |
</RelOp> | |
<RelOp AvgRowSize="4083" EstimateCPU="0.0353356" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10858.4" LogicalOp="Inner Join" NodeId="58" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="1.70361"> | |
<OutputList> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quDateWon" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snPTWCompletionDate" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Clients]" Alias="[c]" Column="clName" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="14446" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<Merge ManyToMany="false"> | |
<InnerSideJoinColumns> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="FK_clID" /> | |
</InnerSideJoinColumns> | |
<OuterSideJoinColumns> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Clients]" Alias="[c]" Column="PK_clID" /> | |
</OuterSideJoinColumns> | |
<Residual> | |
<ScalarOperator ScalarString="[CDB].[cdb].[Quotes].[FK_clID] as [q].[FK_clID]=[CDB].[cdb].[Clients].[PK_clID] as [c].[PK_clID]"> | |
<Compare CompareOp="EQ"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="FK_clID" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Clients]" Alias="[c]" Column="PK_clID" /> | |
</Identifier> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</Residual> | |
<RelOp AvgRowSize="4039" EstimateCPU="0.0031787" EstimateIO="0.0179398" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2747" LogicalOp="Clustered Index Scan" NodeId="59" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0211185" TableCardinality="2747"> | |
<OutputList> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Clients]" Alias="[c]" Column="PK_clID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Clients]" Alias="[c]" Column="clName" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="2714" ActualEndOfScans="0" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Clients]" Alias="[c]" Column="PK_clID" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Clients]" Alias="[c]" Column="clName" /> | |
</DefinedValue> | |
</DefinedValues> | |
<Object Database="[CDB]" Schema="[cdb]" Table="[Clients]" Index="[PK_Clients]" Alias="[c]" IndexKind="Clustered" /> | |
</IndexScan> | |
</RelOp> | |
<RelOp AvgRowSize="61" EstimateCPU="0.670827" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10895.7" LogicalOp="Sort" NodeId="60" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="1.64715"> | |
<OutputList> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="FK_clID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quDateWon" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snPTWCompletionDate" /> | |
</OutputList> | |
<MemoryFractions Input="0.228571" Output="0.460504" /> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="14446" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<Sort Distinct="false"> | |
<OrderBy> | |
<OrderByColumn Ascending="true"> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="FK_clID" /> | |
</OrderByColumn> | |
</OrderBy> | |
<RelOp AvgRowSize="61" EstimateCPU="0.285442" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10895.7" LogicalOp="Inner Join" NodeId="61" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.96506"> | |
<OutputList> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="FK_clID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quDateWon" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snPTWCompletionDate" /> | |
</OutputList> | |
<MemoryFractions Input="0.327025" Output="0.164706" /> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="14446" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<Hash> | |
<DefinedValues /> | |
<HashKeysBuild> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="PK_snID" /> | |
</HashKeysBuild> | |
<HashKeysProbe> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[LineItems]" Alias="[l]" Column="FK_snID" /> | |
</HashKeysProbe> | |
<ProbeResidual> | |
<ScalarOperator ScalarString="[CDB].[cdb].[LineItems].[FK_snID] as [l].[FK_snID]=[CDB].[cdb].[StudyNumbers].[PK_snID] as [s].[PK_snID]"> | |
<Compare CompareOp="EQ"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[LineItems]" Alias="[l]" Column="FK_snID" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="PK_snID" /> | |
</Identifier> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</ProbeResidual> | |
<RelOp AvgRowSize="23" EstimateCPU="0.0088954" EstimateIO="0.0520139" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="6876.98" LogicalOp="Clustered Index Scan" NodeId="62" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0609093" TableCardinality="7944"> | |
<OutputList> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="PK_snID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snPTWCompletionDate" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="6264" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="PK_snID" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snPTWCompletionDate" /> | |
</DefinedValue> | |
</DefinedValues> | |
<Object Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Index="[PK_StudyNumbers]" Alias="[s]" IndexKind="Clustered" /> | |
<Predicate> | |
<ScalarOperator ScalarString="[CDB].[cdb].[StudyNumbers].[snPTWCompletionDate] as [s].[snPTWCompletionDate]>'1900-01-01 00:00:00.000'"> | |
<Compare CompareOp="GT"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snPTWCompletionDate" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="'1900-01-01 00:00:00.000'" /> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</Predicate> | |
</IndexScan> | |
</RelOp> | |
<RelOp AvgRowSize="53" EstimateCPU="0.342681" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="16000.3" LogicalOp="Inner Join" NodeId="63" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.614893"> | |
<OutputList> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="FK_clID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quDateWon" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[LineItems]" Alias="[l]" Column="FK_snID" /> | |
</OutputList> | |
<MemoryFractions Input="0.133479" Output="0.0672269" /> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="16970" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<Hash> | |
<DefinedValues /> | |
<HashKeysBuild> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="PK_quID" /> | |
</HashKeysBuild> | |
<HashKeysProbe> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[LineItems]" Alias="[l]" Column="FK_quID" /> | |
</HashKeysProbe> | |
<ProbeResidual> | |
<ScalarOperator ScalarString="[CDB].[cdb].[Quotes].[PK_quID] as [q].[PK_quID]=[CDB].[cdb].[LineItems].[FK_quID] as [l].[FK_quID]"> | |
<Compare CompareOp="EQ"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="PK_quID" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[LineItems]" Alias="[l]" Column="FK_quID" /> | |
</Identifier> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</ProbeResidual> | |
<RelOp AvgRowSize="53" EstimateCPU="0.0067977" EstimateIO="0.131273" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3033.05" LogicalOp="Clustered Index Scan" NodeId="64" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.138071" TableCardinality="6037"> | |
<OutputList> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="PK_quID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="FK_clID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quDateWon" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="3025" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="PK_quID" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="FK_clID" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteID" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteNumber" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quDateWon" /> | |
</DefinedValue> | |
</DefinedValues> | |
<Object Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Index="[PK_Quotes]" Alias="[q]" IndexKind="Clustered" /> | |
<Predicate> | |
<ScalarOperator ScalarString="[CDB].[cdb].[Quotes].[quDateWon] as [q].[quDateWon]>'1900-01-01 00:00:00.000'"> | |
<Compare CompareOp="GT"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quDateWon" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="'1900-01-01 00:00:00.000'" /> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</Predicate> | |
</IndexScan> | |
</RelOp> | |
<RelOp AvgRowSize="15" EstimateCPU="0.0310777" EstimateIO="0.100162" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="28109.8" LogicalOp="Index Seek" NodeId="65" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.13124" TableCardinality="30417"> | |
<OutputList> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[LineItems]" Alias="[l]" Column="FK_quID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[LineItems]" Alias="[l]" Column="FK_snID" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="28129" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[LineItems]" Alias="[l]" Column="FK_quID" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[LineItems]" Alias="[l]" Column="FK_snID" /> | |
</DefinedValue> | |
</DefinedValues> | |
<Object Database="[CDB]" Schema="[cdb]" Table="[LineItems]" Index="[IDX_liCreationDate_Includes]" Alias="[l]" IndexKind="NonClustered" /> | |
<SeekPredicates> | |
<SeekPredicateNew> | |
<SeekKeys> | |
<StartRange ScanType="GT"> | |
<RangeColumns> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[LineItems]" Alias="[l]" Column="liCreationDate" /> | |
</RangeColumns> | |
<RangeExpressions> | |
<ScalarOperator ScalarString="'2006-12-31 00:00:00.000'"> | |
<Const ConstValue="'2006-12-31 00:00:00.000'" /> | |
</ScalarOperator> | |
</RangeExpressions> | |
</StartRange> | |
</SeekKeys> | |
</SeekPredicateNew> | |
</SeekPredicates> | |
</IndexScan> | |
</RelOp> | |
</Hash> | |
</RelOp> | |
</Hash> | |
</RelOp> | |
</Sort> | |
</RelOp> | |
</Merge> | |
</RelOp> | |
</Hash> | |
</RelOp> | |
<Predicate> | |
<ScalarOperator ScalarString="[ARDB].[ardb].[Projects].[PK_prID] as [p].[PK_prID] IS NULL"> | |
<Compare CompareOp="IS"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="PK_prID" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="NULL" /> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</Predicate> | |
</Filter> | |
</RelOp> | |
</Hash> | |
</RelOp> | |
<Predicate> | |
<ScalarOperator ScalarString="[Expr1020] IS NULL"> | |
<Compare CompareOp="IS"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1020" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="NULL" /> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</Predicate> | |
</Filter> | |
</RelOp> | |
<RelOp AvgRowSize="4083" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="73" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0198407"> | |
<OutputList> | |
<ColumnReference Column="Expr1025" /> | |
<ColumnReference Column="Expr1026" /> | |
<ColumnReference Column="Expr1027" /> | |
<ColumnReference Column="Expr1028" /> | |
<ColumnReference Column="Expr1029" /> | |
<ColumnReference Column="Expr1030" /> | |
</OutputList> | |
<ComputeScalar> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Column="Expr1026" /> | |
<ScalarOperator ScalarString="NULL"> | |
<Const ConstValue="NULL" /> | |
</ScalarOperator> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Column="Expr1027" /> | |
<ScalarOperator ScalarString="NULL"> | |
<Const ConstValue="NULL" /> | |
</ScalarOperator> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Column="Expr1028" /> | |
<ScalarOperator ScalarString="NULL"> | |
<Const ConstValue="NULL" /> | |
</ScalarOperator> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Column="Expr1029" /> | |
<ScalarOperator ScalarString="NULL"> | |
<Const ConstValue="NULL" /> | |
</ScalarOperator> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Column="Expr1030" /> | |
<ScalarOperator ScalarString="NULL"> | |
<Const ConstValue="NULL" /> | |
</ScalarOperator> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="11" EstimateCPU="4.8E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Filter" NodeId="74" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="0.0198406"> | |
<OutputList> | |
<ColumnReference Column="Expr1025" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<Filter StartupExpression="false"> | |
<RelOp AvgRowSize="15" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Left Outer Join" NodeId="75" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0198401"> | |
<OutputList> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="PK_prID" /> | |
<ColumnReference Column="Expr1025" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<NestedLoops Optimized="false"> | |
<OuterReferences> | |
<ColumnReference Database="[ARDB]" Schema="[history]" Table="[ProjectsToProcess]" Alias="[a]" Column="ProjectNumber" /> | |
</OuterReferences> | |
<RelOp AvgRowSize="19" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="76" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0032832"> | |
<OutputList> | |
<ColumnReference Database="[ARDB]" Schema="[history]" Table="[ProjectsToProcess]" Alias="[a]" Column="ProjectNumber" /> | |
<ColumnReference Column="Expr1025" /> | |
</OutputList> | |
<ComputeScalar> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Column="Expr1025" /> | |
<ScalarOperator ScalarString="CONVERT(int,[ARDB].[history].[ProjectsToProcess].[ProjectNumber] as [a].[ProjectNumber],0)"> | |
<Convert DataType="int" Style="0" Implicit="false"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[ARDB]" Schema="[history]" Table="[ProjectsToProcess]" Alias="[a]" Column="ProjectNumber" /> | |
</Identifier> | |
</ScalarOperator> | |
</Convert> | |
</ScalarOperator> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Scan" NodeId="77" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0"> | |
<OutputList> | |
<ColumnReference Database="[ARDB]" Schema="[history]" Table="[ProjectsToProcess]" Alias="[a]" Column="ProjectNumber" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Database="[ARDB]" Schema="[history]" Table="[ProjectsToProcess]" Alias="[a]" Column="ProjectNumber" /> | |
</DefinedValue> | |
</DefinedValues> | |
<Object Database="[ARDB]" Schema="[history]" Table="[ProjectsToProcess]" Index="[IDX_ProjectNumber]" Alias="[a]" IndexKind="NonClustered" /> | |
</IndexScan> | |
</RelOp> | |
</ComputeScalar> | |
</RelOp> | |
<RelOp AvgRowSize="20" EstimateCPU="0.0044811" EstimateIO="0.00979167" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Scan" NodeId="81" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.0142728" TableCardinality="3931"> | |
<OutputList> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="PK_prID" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" /> | |
</RunTimeInformation> | |
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="PK_prID" /> | |
</DefinedValue> | |
</DefinedValues> | |
<Object Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Index="[IDX_UniqueProjects]" Alias="[p]" TableReferenceId="2" IndexKind="NonClustered" /> | |
<Predicate> | |
<ScalarOperator ScalarString="[ARDB].[history].[ProjectsToProcess].[ProjectNumber] as [a].[ProjectNumber]=CONVERT_IMPLICIT(float(53),[ARDB].[ardb].[Projects].[prProjectNumber] as [p].[prProjectNumber],0)"> | |
<Compare CompareOp="EQ"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[ARDB]" Schema="[history]" Table="[ProjectsToProcess]" Alias="[a]" Column="ProjectNumber" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Convert DataType="float" Scale="0" Style="0" Implicit="true"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="prProjectNumber" /> | |
</Identifier> | |
</ScalarOperator> | |
</Convert> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</Predicate> | |
</IndexScan> | |
</RelOp> | |
</NestedLoops> | |
</RelOp> | |
<Predicate> | |
<ScalarOperator ScalarString="[ARDB].[ardb].[Projects].[PK_prID] as [p].[PK_prID] IS NULL"> | |
<Compare CompareOp="IS"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="PK_prID" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="NULL" /> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</Predicate> | |
</Filter> | |
</RelOp> | |
</ComputeScalar> | |
</RelOp> | |
</Concat> | |
</RelOp> | |
</Sort> | |
</RelOp> | |
</QueryPlan> | |
</StmtSimple> | |
</Statements> | |
</Batch> | |
</BatchSequence> | |
</ShowPlanXML> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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.1" Build="10.0.5500.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> | |
<BatchSequence> | |
<Batch> | |
<Statements> | |
<StmtSimple StatementCompId="5" StatementEstRows="2" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" StatementSubTreeCost="4.14627" StatementText="with b as
(
 select distinct
 coalesce(a.STUDY_NUMBER,b.STUDY_NUMBER, c.study_number) as StudyNumber
 from
 (
 SELECT SUBSTRING(PAPROJNUMBER, 1, 5) AS STUDY_NUMBER
 FROM PSQL3.PROD.dbo.PA01201 AS PA01201_1
 where PAPROJNUMBER>'0'
 and PAPROJNUMBER not like '%[a-z]%'
 ) a
 full outer join
 (
 SELECT SUBSTRING(PAPROJNUMBER, 1, 5) AS STUDY_NUMBER
 FROM PSQL3.MO1.dbo.PA01201 AS PA01201_1
 where PAPROJNUMBER>'0'
 and PAPROJNUMBER not like '%[a-z]%'
 ) b
 on a.STUDY_NUMBER=b.STUDY_NUMBER
 full outer join 
 (
 SELECT STUDY_NUMBER
 FROM appdb.ptwdb.dbo.tblARCompletedStudies
 where STUDY_NUMBER>'0'
 ) c
 on a.STUDY_NUMBER=c.study_number
 and b.STUDY_NUMBER=c.study_number
)

select
 snStudyNumber as ProjectNumber,
 c.clName as CompanyName,
 q.quQuoteNumber as QuoteNumber,
 q.quQuoteID as QuoteID,
 q.quDateWon as DateWon,
 s.snPTWCompletionDate as PTWCompletionDate
from CDB.cdb.Quotes q
 inner join CDB.cdb.LineItems l
 on q.PK_quID=l.FK_quID
 inner join CDB.cdb.StudyNumbers s
 on l.FK_snID=s.PK_snId
 inner join cdb.cdb.Clients c
 on q.FK_clID=c.PK_clID
 left outer join ardb.projects p
 on s.snStudyNumber=p.prProjectNumber
 left outer join b
 on s.snStudyNumber=b.studynumber
where q.quDateWon>''
 and s.snPTWCompletionDate >''
 and p.PK_prID is null
 and l.liCreationDate>'12/31/06'
 and b.studynumber is null
union all

select CAST(ProjectNumber AS int) as ProjectNumber,
 null as CompanyName,
 null as QuoteNumber,
 null as QuoteID,
 null as DateWon,
 null as PTWCompletionDate
from history.ProjectsToProcess a
 left outer join ardb.projects p
 on a.ProjectNumber=p.prProjectNumber
where p.PK_prID is null

order by ProjectNumber desc




 
" StatementType="SELECT" QueryHash="0x5565A98F38769335" QueryPlanHash="0xE67A6709F24B01C3"> | |
<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="12592" CachedPlanSize="432" CompileTime="444" CompileCPU="441" CompileMemory="4560"> | |
<RelOp AvgRowSize="4083" EstimateCPU="0.000107203" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2" LogicalOp="Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="4.14627"> | |
<OutputList> | |
<ColumnReference Column="Union1031" /> | |
<ColumnReference Column="Union1032" /> | |
<ColumnReference Column="Union1033" /> | |
<ColumnReference Column="Union1034" /> | |
<ColumnReference Column="Union1035" /> | |
<ColumnReference Column="Union1036" /> | |
</OutputList> | |
<MemoryFractions Input="0.00443459" Output="1" /> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<Sort Distinct="false"> | |
<OrderBy> | |
<OrderByColumn Ascending="false"> | |
<ColumnReference Column="Union1031" /> | |
</OrderByColumn> | |
</OrderBy> | |
<RelOp AvgRowSize="4083" EstimateCPU="2E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2" LogicalOp="Concatenation" NodeId="1" Parallel="false" PhysicalOp="Concatenation" EstimatedTotalSubtreeCost="4.1349"> | |
<OutputList> | |
<ColumnReference Column="Union1031" /> | |
<ColumnReference Column="Union1032" /> | |
<ColumnReference Column="Union1033" /> | |
<ColumnReference Column="Union1034" /> | |
<ColumnReference Column="Union1035" /> | |
<ColumnReference Column="Union1036" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<Concat> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Column="Union1031" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
<ColumnReference Column="Expr1025" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Column="Union1032" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Clients]" Alias="[c]" Column="clName" /> | |
<ColumnReference Column="Expr1026" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Column="Union1033" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteNumber" /> | |
<ColumnReference Column="Expr1027" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Column="Union1034" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteID" /> | |
<ColumnReference Column="Expr1028" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Column="Union1035" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quDateWon" /> | |
<ColumnReference Column="Expr1029" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Column="Union1036" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snPTWCompletionDate" /> | |
<ColumnReference Column="Expr1030" /> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="4083" EstimateCPU="4.8E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Filter" NodeId="2" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="4.11432"> | |
<OutputList> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quDateWon" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snPTWCompletionDate" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Clients]" Alias="[c]" Column="clName" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<Filter StartupExpression="false"> | |
<RelOp AvgRowSize="4087" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Left Outer Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="4.11432"> | |
<OutputList> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quDateWon" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snPTWCompletionDate" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Clients]" Alias="[c]" Column="clName" /> | |
<ColumnReference Column="Expr1020" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="3632" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<NestedLoops Optimized="false"> | |
<OuterReferences> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
</OuterReferences> | |
<RelOp AvgRowSize="4083" EstimateCPU="0.00373294" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Filter" NodeId="4" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="1.31674"> | |
<OutputList> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quDateWon" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snPTWCompletionDate" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Clients]" Alias="[c]" Column="clName" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="3632" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<Filter StartupExpression="false"> | |
<RelOp AvgRowSize="4087" EstimateCPU="0.142349" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="7776.96" LogicalOp="Right Outer Join" NodeId="5" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1.31301"> | |
<OutputList> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quDateWon" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snPTWCompletionDate" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Clients]" Alias="[c]" Column="clName" /> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="PK_prID" /> | |
</OutputList> | |
<MemoryFractions Input="0.501129" Output="0.492239" /> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="12087" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<Hash> | |
<DefinedValues /> | |
<HashKeysBuild> | |
<ColumnReference Column="Expr1038" /> | |
</HashKeysBuild> | |
<HashKeysProbe> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
</HashKeysProbe> | |
<ProbeResidual> | |
<ScalarOperator ScalarString="[CDB].[cdb].[StudyNumbers].[snStudyNumber] as [s].[snStudyNumber]=[Expr1038]"> | |
<Compare CompareOp="EQ"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1038" /> | |
</Identifier> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</ProbeResidual> | |
<RelOp AvgRowSize="15" EstimateCPU="0.0003928" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3928" LogicalOp="Compute Scalar" NodeId="6" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.015403"> | |
<OutputList> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="PK_prID" /> | |
<ColumnReference Column="Expr1038" /> | |
</OutputList> | |
<ComputeScalar> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Column="Expr1038" /> | |
<ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[ARDB].[ardb].[Projects].[prProjectNumber] as [p].[prProjectNumber],0)"> | |
<Convert DataType="int" Style="0" Implicit="true"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="prProjectNumber" /> | |
</Identifier> | |
</ScalarOperator> | |
</Convert> | |
</ScalarOperator> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="20" EstimateCPU="0.0044778" EstimateIO="0.0105324" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3928" LogicalOp="Index Scan" NodeId="7" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.0150102" TableCardinality="3928"> | |
<OutputList> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="PK_prID" /> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="prProjectNumber" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="3928" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="PK_prID" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="prProjectNumber" /> | |
</DefinedValue> | |
</DefinedValues> | |
<Object Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Index="[IDX_UniqueProjects]" Alias="[p]" TableReferenceId="1" IndexKind="NonClustered" /> | |
</IndexScan> | |
</RelOp> | |
</ComputeScalar> | |
</RelOp> | |
<RelOp AvgRowSize="4083" EstimateCPU="0.0284902" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="7776.96" LogicalOp="Inner Join" NodeId="11" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="1.15525"> | |
<OutputList> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quDateWon" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snPTWCompletionDate" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Clients]" Alias="[c]" Column="clName" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="12087" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<Merge ManyToMany="false"> | |
<InnerSideJoinColumns> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="FK_clID" /> | |
</InnerSideJoinColumns> | |
<OuterSideJoinColumns> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Clients]" Alias="[c]" Column="PK_clID" /> | |
</OuterSideJoinColumns> | |
<Residual> | |
<ScalarOperator ScalarString="[CDB].[cdb].[Quotes].[FK_clID] as [q].[FK_clID]=[CDB].[cdb].[Clients].[PK_clID] as [c].[PK_clID]"> | |
<Compare CompareOp="EQ"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="FK_clID" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Clients]" Alias="[c]" Column="PK_clID" /> | |
</Identifier> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</Residual> | |
<RelOp AvgRowSize="4039" EstimateCPU="0.0031787" EstimateIO="0.0186806" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2747" LogicalOp="Clustered Index Scan" NodeId="12" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0218593" TableCardinality="2747"> | |
<OutputList> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Clients]" Alias="[c]" Column="PK_clID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Clients]" Alias="[c]" Column="clName" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="2714" ActualEndOfScans="0" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Clients]" Alias="[c]" Column="PK_clID" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Clients]" Alias="[c]" Column="clName" /> | |
</DefinedValue> | |
</DefinedValues> | |
<Object Database="[CDB]" Schema="[cdb]" Table="[Clients]" Index="[PK_Clients]" Alias="[c]" IndexKind="Clustered" /> | |
</IndexScan> | |
</RelOp> | |
<RelOp AvgRowSize="61" EstimateCPU="0.322805" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="7782.77" LogicalOp="Sort" NodeId="13" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="1.1049"> | |
<OutputList> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="FK_clID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quDateWon" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snPTWCompletionDate" /> | |
</OutputList> | |
<MemoryFractions Input="0.215078" Output="0.490022" /> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="12087" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<Sort Distinct="false"> | |
<OrderBy> | |
<OrderByColumn Ascending="true"> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="FK_clID" /> | |
</OrderByColumn> | |
</OrderBy> | |
<RelOp AvgRowSize="61" EstimateCPU="0.23772" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="7782.77" LogicalOp="Inner Join" NodeId="14" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.770835"> | |
<OutputList> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="FK_clID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quDateWon" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snPTWCompletionDate" /> | |
</OutputList> | |
<MemoryFractions Input="0.331951" Output="0.186253" /> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="12087" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<Hash> | |
<DefinedValues /> | |
<HashKeysBuild> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="PK_snId" /> | |
</HashKeysBuild> | |
<HashKeysProbe> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[LineItems]" Alias="[l]" Column="FK_snID" /> | |
</HashKeysProbe> | |
<ProbeResidual> | |
<ScalarOperator ScalarString="[CDB].[cdb].[LineItems].[FK_snID] as [l].[FK_snID]=[CDB].[cdb].[StudyNumbers].[PK_snId] as [s].[PK_snId]"> | |
<Compare CompareOp="EQ"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[LineItems]" Alias="[l]" Column="FK_snID" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="PK_snId" /> | |
</Identifier> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</ProbeResidual> | |
<RelOp AvgRowSize="23" EstimateCPU="0.0088965" EstimateIO="0.0505324" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="6264" LogicalOp="Clustered Index Scan" NodeId="15" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0594289" TableCardinality="7945"> | |
<OutputList> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="PK_snId" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snPTWCompletionDate" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="6264" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="PK_snId" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snPTWCompletionDate" /> | |
</DefinedValue> | |
</DefinedValues> | |
<Object Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Index="[PK_StudyNumbers]" Alias="[s]" IndexKind="Clustered" /> | |
<Predicate> | |
<ScalarOperator ScalarString="[CDB].[cdb].[StudyNumbers].[snPTWCompletionDate] as [s].[snPTWCompletionDate]>'1900-01-01 00:00:00.000'"> | |
<Compare CompareOp="GT"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snPTWCompletionDate" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="'1900-01-01 00:00:00.000'" /> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</Predicate> | |
</IndexScan> | |
</RelOp> | |
<RelOp AvgRowSize="53" EstimateCPU="0.276083" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="11281.8" LogicalOp="Inner Join" NodeId="16" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="0.46987"> | |
<OutputList> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="FK_clID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quDateWon" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[LineItems]" Alias="[l]" Column="FK_snID" /> | |
</OutputList> | |
<MemoryFractions Input="0.158072" Output="0.0886918" /> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="12949" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<Hash> | |
<DefinedValues /> | |
<HashKeysBuild> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="PK_quID" /> | |
</HashKeysBuild> | |
<HashKeysProbe> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[LineItems]" Alias="[l]" Column="FK_quID" /> | |
</HashKeysProbe> | |
<ProbeResidual> | |
<ScalarOperator ScalarString="[CDB].[cdb].[Quotes].[PK_quID] as [q].[PK_quID]=[CDB].[cdb].[LineItems].[FK_quID] as [l].[FK_quID]"> | |
<Compare CompareOp="EQ"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="PK_quID" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[LineItems]" Alias="[l]" Column="FK_quID" /> | |
</Identifier> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</ProbeResidual> | |
<RelOp AvgRowSize="53" EstimateCPU="0.0067988" EstimateIO="0.109792" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3024" LogicalOp="Clustered Index Scan" NodeId="17" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.11659" TableCardinality="6038"> | |
<OutputList> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="PK_quID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="FK_clID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteNumber" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quDateWon" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="3024" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="PK_quID" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="FK_clID" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteID" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quQuoteNumber" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quDateWon" /> | |
</DefinedValue> | |
</DefinedValues> | |
<Object Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Index="[PK_Quotes]" Alias="[q]" IndexKind="Clustered" /> | |
<Predicate> | |
<ScalarOperator ScalarString="[CDB].[cdb].[Quotes].[quDateWon] as [q].[quDateWon]>'1900-01-01 00:00:00.000'"> | |
<Compare CompareOp="GT"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[Quotes]" Alias="[q]" Column="quDateWon" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="'1900-01-01 00:00:00.000'" /> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</Predicate> | |
</IndexScan> | |
</RelOp> | |
<RelOp AvgRowSize="15" EstimateCPU="0.0207999" EstimateIO="0.0534954" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="18766.3" LogicalOp="Index Seek" NodeId="18" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0742953" TableCardinality="20943"> | |
<OutputList> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[LineItems]" Alias="[l]" Column="FK_quID" /> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[LineItems]" Alias="[l]" Column="FK_snID" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="18781" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[LineItems]" Alias="[l]" Column="FK_quID" /> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[LineItems]" Alias="[l]" Column="FK_snID" /> | |
</DefinedValue> | |
</DefinedValues> | |
<Object Database="[CDB]" Schema="[cdb]" Table="[LineItems]" Index="[IDX_liCreationDate_Includes]" Alias="[l]" IndexKind="NonClustered" /> | |
<SeekPredicates> | |
<SeekPredicateNew> | |
<SeekKeys> | |
<StartRange ScanType="GT"> | |
<RangeColumns> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[LineItems]" Alias="[l]" Column="liCreationDate" /> | |
</RangeColumns> | |
<RangeExpressions> | |
<ScalarOperator ScalarString="'2006-12-31 00:00:00.000'"> | |
<Const ConstValue="'2006-12-31 00:00:00.000'" /> | |
</ScalarOperator> | |
</RangeExpressions> | |
</StartRange> | |
</SeekKeys> | |
</SeekPredicateNew> | |
</SeekPredicates> | |
</IndexScan> | |
</RelOp> | |
</Hash> | |
</RelOp> | |
</Hash> | |
</RelOp> | |
</Sort> | |
</RelOp> | |
</Merge> | |
</RelOp> | |
</Hash> | |
</RelOp> | |
<Predicate> | |
<ScalarOperator ScalarString="[ARDB].[ardb].[Projects].[PK_prID] as [p].[PK_prID] IS NULL"> | |
<Compare CompareOp="IS"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="PK_prID" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="NULL" /> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</Predicate> | |
</Filter> | |
</RelOp> | |
<RelOp AvgRowSize="13" EstimateCPU="0.0109793" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Distinct Sort" NodeId="24" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="2.79758"> | |
<OutputList> | |
<ColumnReference Column="Expr1020" /> | |
</OutputList> | |
<MemoryFractions Input="0.0133038" Output="0.0133038" /> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRebinds="2905" ActualRewinds="727" ActualRows="3631" ActualEndOfScans="3632" ActualExecutions="3632" /> | |
</RunTimeInformation> | |
<Sort Distinct="true"> | |
<OrderBy> | |
<OrderByColumn Ascending="true"> | |
<ColumnReference Column="Expr1020" /> | |
</OrderByColumn> | |
</OrderBy> | |
<RelOp AvgRowSize="13" EstimateCPU="0.00383163" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="732.769" LogicalOp="Filter" NodeId="25" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="2.77533"> | |
<OutputList> | |
<ColumnReference Column="Expr1020" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="3019" ActualEndOfScans="2928" ActualExecutions="2928" /> | |
</RunTimeInformation> | |
<Filter StartupExpression="false"> | |
<RelOp AvgRowSize="13" EstimateCPU="0.000660626" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="6606.26" LogicalOp="Compute Scalar" NodeId="26" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="2.7715"> | |
<OutputList> | |
<ColumnReference Column="Expr1020" /> | |
</OutputList> | |
<ComputeScalar> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Column="Expr1020" /> | |
<ScalarOperator ScalarString="CASE WHEN [Expr1012] IS NOT NULL THEN [Expr1012] ELSE CASE WHEN [Expr1015] IS NOT NULL THEN [Expr1015] ELSE [Expr1019] END END"> | |
<IF> | |
<Condition> | |
<ScalarOperator> | |
<Compare CompareOp="IS NOT"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1012" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="NULL" /> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</Condition> | |
<Then> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1012" /> | |
</Identifier> | |
</ScalarOperator> | |
</Then> | |
<Else> | |
<ScalarOperator> | |
<IF> | |
<Condition> | |
<ScalarOperator> | |
<Compare CompareOp="IS NOT"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1015" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="NULL" /> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</Condition> | |
<Then> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1015" /> | |
</Identifier> | |
</ScalarOperator> | |
</Then> | |
<Else> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1019" /> | |
</Identifier> | |
</ScalarOperator> | |
</Else> | |
</IF> | |
</ScalarOperator> | |
</Else> | |
</IF> | |
</ScalarOperator> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="21" EstimateCPU="0.0864089" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="6606.26" LogicalOp="Full Outer Join" NodeId="27" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="2.77084"> | |
<OutputList> | |
<ColumnReference Column="Expr1012" /> | |
<ColumnReference Column="Expr1015" /> | |
<ColumnReference Column="Expr1019" /> | |
</OutputList> | |
<MemoryFractions Input="0" Output="0" /> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="19945536" ActualEndOfScans="2928" ActualExecutions="2928" /> | |
</RunTimeInformation> | |
<Hash> | |
<DefinedValues /> | |
<HashKeysBuild> | |
<ColumnReference Server="[appdb]" Database="[ptwdb]" Schema="[dbo]" Table="[tblARCompletedStudies]" Column="STUDY_NUMBER" /> | |
</HashKeysBuild> | |
<HashKeysProbe> | |
<ColumnReference Column="Expr1012" /> | |
</HashKeysProbe> | |
<ProbeResidual> | |
<ScalarOperator ScalarString="[Expr1012]=[appdb].[ptwdb].[dbo].[tblARCompletedStudies].[STUDY_NUMBER] AND [Expr1015]=[appdb].[ptwdb].[dbo].[tblARCompletedStudies].[STUDY_NUMBER]"> | |
<Logical Operation="AND"> | |
<ScalarOperator> | |
<Compare CompareOp="EQ"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1012" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Server="[appdb]" Database="[ptwdb]" Schema="[dbo]" Table="[tblARCompletedStudies]" Column="STUDY_NUMBER" /> | |
</Identifier> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Compare CompareOp="EQ"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1015" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Server="[appdb]" Database="[ptwdb]" Schema="[dbo]" Table="[tblARCompletedStudies]" Column="STUDY_NUMBER" /> | |
</Identifier> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</Logical> | |
</ScalarOperator> | |
</ProbeResidual> | |
<RelOp AvgRowSize="17" EstimateCPU="0.597333" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1762" LogicalOp="Compute Scalar" NodeId="28" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.597333"> | |
<OutputList> | |
<ColumnReference Server="[appdb]" Database="[ptwdb]" Schema="[dbo]" Table="[tblARCompletedStudies]" Column="STUDY_NUMBER" /> | |
<ColumnReference Column="Expr1019" /> | |
</OutputList> | |
<ComputeScalar> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Server="[appdb]" Database="[ptwdb]" Schema="[dbo]" Table="[tblARCompletedStudies]" Column="STUDY_NUMBER" /> | |
<ScalarOperator ScalarString="[appdb].[ptwdb].[dbo].[tblARCompletedStudies].[STUDY_NUMBER]"> | |
<Identifier> | |
<ColumnReference Server="[appdb]" Database="[ptwdb]" Schema="[dbo]" Table="[tblARCompletedStudies]" Column="STUDY_NUMBER" /> | |
</Identifier> | |
</ScalarOperator> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Column="Expr1019" /> | |
<ScalarOperator ScalarString="[Expr1019]"> | |
<Identifier> | |
<ColumnReference Column="Expr1019" /> | |
</Identifier> | |
</ScalarOperator> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="17" EstimateCPU="0.597333" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1762" LogicalOp="Remote Query" NodeId="29" Parallel="false" PhysicalOp="Remote Query" EstimatedTotalSubtreeCost="0.597333"> | |
<OutputList> | |
<ColumnReference Server="[appdb]" Database="[ptwdb]" Schema="[dbo]" Table="[tblARCompletedStudies]" Column="STUDY_NUMBER" /> | |
<ColumnReference Column="Expr1019" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRebinds="2928" ActualRewinds="0" ActualRows="5159136" ActualEndOfScans="2928" ActualExecutions="2928" /> | |
</RunTimeInformation> | |
<RemoteQuery RemoteSource="appdb" RemoteQuery="SELECT "Tbl1018"."STUDY_NUMBER" "Col1124","Tbl1018"."STUDY_NUMBER" "Expr1019" FROM "ptwdb"."dbo"."tblARCompletedStudies" "Tbl1018" WHERE "Tbl1018"."STUDY_NUMBER">'0'" /> | |
</RelOp> | |
</ComputeScalar> | |
</RelOp> | |
<RelOp AvgRowSize="17" EstimateCPU="0.0752689" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4852.89" LogicalOp="Full Outer Join" NodeId="35" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="2.08709"> | |
<OutputList> | |
<ColumnReference Column="Expr1012" /> | |
<ColumnReference Column="Expr1015" /> | |
</OutputList> | |
<MemoryFractions Input="0" Output="0" /> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="14786400" ActualEndOfScans="2928" ActualExecutions="2928" /> | |
</RunTimeInformation> | |
<Hash> | |
<DefinedValues /> | |
<HashKeysBuild> | |
<ColumnReference Column="Expr1043" /> | |
</HashKeysBuild> | |
<HashKeysProbe> | |
<ColumnReference Column="Expr1044" /> | |
</HashKeysProbe> | |
<ProbeResidual> | |
<ScalarOperator ScalarString="[Expr1044]=[Expr1043]"> | |
<Compare CompareOp="EQ"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1044" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1043" /> | |
</Identifier> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</ProbeResidual> | |
<RelOp AvgRowSize="17" EstimateCPU="0.000130296" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1302.96" LogicalOp="Compute Scalar" NodeId="36" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.444579"> | |
<OutputList> | |
<ColumnReference Column="Expr1015" /> | |
<ColumnReference Column="Expr1043" /> | |
</OutputList> | |
<ComputeScalar> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Column="Expr1043" /> | |
<ScalarOperator ScalarString="substring([PSQL3].[MO1].[dbo].[PA01201].[PAPROJNUMBER] as [PA01201_1].[PAPROJNUMBER],(1),(5))"> | |
<Intrinsic FunctionName="substring"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Server="[PSQL3]" Database="[MO1]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="(1)" /> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="(5)" /> | |
</ScalarOperator> | |
</Intrinsic> | |
</ScalarOperator> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="28" EstimateCPU="0.000130296" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1302.96" LogicalOp="Compute Scalar" NodeId="37" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.444449"> | |
<OutputList> | |
<ColumnReference Server="[PSQL3]" Database="[MO1]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
<ColumnReference Column="Expr1015" /> | |
</OutputList> | |
<ComputeScalar> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Column="Expr1015" /> | |
<ScalarOperator ScalarString="substring([PSQL3].[MO1].[dbo].[PA01201].[PAPROJNUMBER] as [PA01201_1].[PAPROJNUMBER],(1),(5))"> | |
<Intrinsic FunctionName="substring"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Server="[PSQL3]" Database="[MO1]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="(1)" /> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="(5)" /> | |
</ScalarOperator> | |
</Intrinsic> | |
</ScalarOperator> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="22" EstimateCPU="0.444319" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1302.96" LogicalOp="Compute Scalar" NodeId="38" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.444319"> | |
<OutputList> | |
<ColumnReference Server="[PSQL3]" Database="[MO1]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
</OutputList> | |
<ComputeScalar> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Server="[PSQL3]" Database="[MO1]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
<ScalarOperator ScalarString="[PSQL3].[MO1].[dbo].[PA01201].[PAPROJNUMBER] as [PA01201_1].[PAPROJNUMBER]"> | |
<Identifier> | |
<ColumnReference Server="[PSQL3]" Database="[MO1]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
</Identifier> | |
</ScalarOperator> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="22" EstimateCPU="0.444319" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1302.96" LogicalOp="Remote Query" NodeId="39" Parallel="false" PhysicalOp="Remote Query" EstimatedTotalSubtreeCost="0.444319"> | |
<OutputList> | |
<ColumnReference Server="[PSQL3]" Database="[MO1]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRebinds="2928" ActualRewinds="0" ActualRows="4134336" ActualEndOfScans="2928" ActualExecutions="2928" /> | |
</RunTimeInformation> | |
<RemoteQuery RemoteSource="PSQL3" RemoteQuery="SELECT "Tbl1014"."PAPROJNUMBER" "Col1132" FROM "MO1"."dbo"."PA01201" "Tbl1014" WHERE "Tbl1014"."PAPROJNUMBER">'0' AND NOT "Tbl1014"."PAPROJNUMBER" like '%[a-z]%' COLLATE SQL_Latin1_General_CP1_CI_AS" /> | |
</RelOp> | |
</ComputeScalar> | |
</RelOp> | |
</ComputeScalar> | |
</RelOp> | |
</ComputeScalar> | |
</RelOp> | |
<RelOp AvgRowSize="17" EstimateCPU="0.000466892" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4668.92" LogicalOp="Compute Scalar" NodeId="49" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.56724"> | |
<OutputList> | |
<ColumnReference Column="Expr1012" /> | |
<ColumnReference Column="Expr1044" /> | |
</OutputList> | |
<ComputeScalar> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Column="Expr1044" /> | |
<ScalarOperator ScalarString="substring([PSQL3].[PROD].[dbo].[PA01201].[PAPROJNUMBER] as [PA01201_1].[PAPROJNUMBER],(1),(5))"> | |
<Intrinsic FunctionName="substring"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Server="[PSQL3]" Database="[PROD]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="(1)" /> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="(5)" /> | |
</ScalarOperator> | |
</Intrinsic> | |
</ScalarOperator> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="28" EstimateCPU="0.000466892" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4668.92" LogicalOp="Compute Scalar" NodeId="50" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.56678"> | |
<OutputList> | |
<ColumnReference Server="[PSQL3]" Database="[PROD]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
<ColumnReference Column="Expr1012" /> | |
</OutputList> | |
<ComputeScalar> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Column="Expr1012" /> | |
<ScalarOperator ScalarString="substring([PSQL3].[PROD].[dbo].[PA01201].[PAPROJNUMBER] as [PA01201_1].[PAPROJNUMBER],(1),(5))"> | |
<Intrinsic FunctionName="substring"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Server="[PSQL3]" Database="[PROD]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="(1)" /> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="(5)" /> | |
</ScalarOperator> | |
</Intrinsic> | |
</ScalarOperator> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="22" EstimateCPU="1.56631" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4668.92" LogicalOp="Compute Scalar" NodeId="51" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.56631"> | |
<OutputList> | |
<ColumnReference Server="[PSQL3]" Database="[PROD]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
</OutputList> | |
<ComputeScalar> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Server="[PSQL3]" Database="[PROD]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
<ScalarOperator ScalarString="[PSQL3].[PROD].[dbo].[PA01201].[PAPROJNUMBER] as [PA01201_1].[PAPROJNUMBER]"> | |
<Identifier> | |
<ColumnReference Server="[PSQL3]" Database="[PROD]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
</Identifier> | |
</ScalarOperator> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="22" EstimateCPU="1.56631" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4668.92" LogicalOp="Remote Query" NodeId="52" Parallel="false" PhysicalOp="Remote Query" EstimatedTotalSubtreeCost="1.56631"> | |
<OutputList> | |
<ColumnReference Server="[PSQL3]" Database="[PROD]" Schema="[dbo]" Table="[PA01201]" Alias="[PA01201_1]" Column="PAPROJNUMBER" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRebinds="2928" ActualRewinds="0" ActualRows="14777616" ActualEndOfScans="2928" ActualExecutions="2928" /> | |
</RunTimeInformation> | |
<RemoteQuery RemoteSource="PSQL3" RemoteQuery="SELECT "Tbl1011"."PAPROJNUMBER" "Col1151" FROM "PROD"."dbo"."PA01201" "Tbl1011" WHERE "Tbl1011"."PAPROJNUMBER">'0' AND NOT "Tbl1011"."PAPROJNUMBER" like '%[a-z]%' COLLATE SQL_Latin1_General_CP1_CI_AS" /> | |
</RelOp> | |
</ComputeScalar> | |
</RelOp> | |
</ComputeScalar> | |
</RelOp> | |
</ComputeScalar> | |
</RelOp> | |
</Hash> | |
</RelOp> | |
</Hash> | |
</RelOp> | |
</ComputeScalar> | |
</RelOp> | |
<Predicate> | |
<ScalarOperator ScalarString="[CDB].[cdb].[StudyNumbers].[snStudyNumber] as [s].[snStudyNumber]=CONVERT_IMPLICIT(int,[Expr1020],0)"> | |
<Compare CompareOp="EQ"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[CDB]" Schema="[cdb]" Table="[StudyNumbers]" Alias="[s]" Column="snStudyNumber" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Convert DataType="int" Style="0" Implicit="true"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1020" /> | |
</Identifier> | |
</ScalarOperator> | |
</Convert> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</Predicate> | |
</Filter> | |
</RelOp> | |
</Sort> | |
</RelOp> | |
</NestedLoops> | |
</RelOp> | |
<Predicate> | |
<ScalarOperator ScalarString="[Expr1020] IS NULL"> | |
<Compare CompareOp="IS"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Column="Expr1020" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="NULL" /> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</Predicate> | |
</Filter> | |
</RelOp> | |
<RelOp AvgRowSize="4083" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="69" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0205764"> | |
<OutputList> | |
<ColumnReference Column="Expr1025" /> | |
<ColumnReference Column="Expr1026" /> | |
<ColumnReference Column="Expr1027" /> | |
<ColumnReference Column="Expr1028" /> | |
<ColumnReference Column="Expr1029" /> | |
<ColumnReference Column="Expr1030" /> | |
</OutputList> | |
<ComputeScalar> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Column="Expr1026" /> | |
<ScalarOperator ScalarString="NULL"> | |
<Const ConstValue="NULL" /> | |
</ScalarOperator> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Column="Expr1027" /> | |
<ScalarOperator ScalarString="NULL"> | |
<Const ConstValue="NULL" /> | |
</ScalarOperator> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Column="Expr1028" /> | |
<ScalarOperator ScalarString="NULL"> | |
<Const ConstValue="NULL" /> | |
</ScalarOperator> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Column="Expr1029" /> | |
<ScalarOperator ScalarString="NULL"> | |
<Const ConstValue="NULL" /> | |
</ScalarOperator> | |
</DefinedValue> | |
<DefinedValue> | |
<ColumnReference Column="Expr1030" /> | |
<ScalarOperator ScalarString="NULL"> | |
<Const ConstValue="NULL" /> | |
</ScalarOperator> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="11" EstimateCPU="4.8E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Filter" NodeId="70" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="0.0205763"> | |
<OutputList> | |
<ColumnReference Column="Expr1025" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<Filter StartupExpression="false"> | |
<RelOp AvgRowSize="15" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Left Outer Join" NodeId="71" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0205758"> | |
<OutputList> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="PK_prID" /> | |
<ColumnReference Column="Expr1025" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<NestedLoops Optimized="false"> | |
<OuterReferences> | |
<ColumnReference Database="[ARDB]" Schema="[history]" Table="[ProjectsToProcess]" Alias="[a]" Column="ProjectNumber" /> | |
</OuterReferences> | |
<RelOp AvgRowSize="19" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="72" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0032832"> | |
<OutputList> | |
<ColumnReference Database="[ARDB]" Schema="[history]" Table="[ProjectsToProcess]" Alias="[a]" Column="ProjectNumber" /> | |
<ColumnReference Column="Expr1025" /> | |
</OutputList> | |
<ComputeScalar> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Column="Expr1025" /> | |
<ScalarOperator ScalarString="CONVERT(int,[ARDB].[history].[ProjectsToProcess].[ProjectNumber] as [a].[ProjectNumber],0)"> | |
<Convert DataType="int" Style="0" Implicit="false"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[ARDB]" Schema="[history]" Table="[ProjectsToProcess]" Alias="[a]" Column="ProjectNumber" /> | |
</Identifier> | |
</ScalarOperator> | |
</Convert> | |
</ScalarOperator> | |
</DefinedValue> | |
</DefinedValues> | |
<RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Scan" NodeId="73" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0"> | |
<OutputList> | |
<ColumnReference Database="[ARDB]" Schema="[history]" Table="[ProjectsToProcess]" Alias="[a]" Column="ProjectNumber" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" /> | |
</RunTimeInformation> | |
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Database="[ARDB]" Schema="[history]" Table="[ProjectsToProcess]" Alias="[a]" Column="ProjectNumber" /> | |
</DefinedValue> | |
</DefinedValues> | |
<Object Database="[ARDB]" Schema="[history]" Table="[ProjectsToProcess]" Index="[IDX_ProjectNumber]" Alias="[a]" IndexKind="NonClustered" /> | |
</IndexScan> | |
</RelOp> | |
</ComputeScalar> | |
</RelOp> | |
<RelOp AvgRowSize="20" EstimateCPU="0.0044778" EstimateIO="0.0105324" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Scan" NodeId="77" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.0150102" TableCardinality="3928"> | |
<OutputList> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="PK_prID" /> | |
</OutputList> | |
<RunTimeInformation> | |
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" /> | |
</RunTimeInformation> | |
<IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> | |
<DefinedValues> | |
<DefinedValue> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="PK_prID" /> | |
</DefinedValue> | |
</DefinedValues> | |
<Object Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Index="[IDX_UniqueProjects]" Alias="[p]" TableReferenceId="2" IndexKind="NonClustered" /> | |
<Predicate> | |
<ScalarOperator ScalarString="[ARDB].[history].[ProjectsToProcess].[ProjectNumber] as [a].[ProjectNumber]=CONVERT_IMPLICIT(float(53),[ARDB].[ardb].[Projects].[prProjectNumber] as [p].[prProjectNumber],0)"> | |
<Compare CompareOp="EQ"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[ARDB]" Schema="[history]" Table="[ProjectsToProcess]" Alias="[a]" Column="ProjectNumber" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Convert DataType="float" Scale="0" Style="0" Implicit="true"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="prProjectNumber" /> | |
</Identifier> | |
</ScalarOperator> | |
</Convert> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</Predicate> | |
</IndexScan> | |
</RelOp> | |
</NestedLoops> | |
</RelOp> | |
<Predicate> | |
<ScalarOperator ScalarString="[ARDB].[ardb].[Projects].[PK_prID] as [p].[PK_prID] IS NULL"> | |
<Compare CompareOp="IS"> | |
<ScalarOperator> | |
<Identifier> | |
<ColumnReference Database="[ARDB]" Schema="[ardb]" Table="[Projects]" Alias="[p]" Column="PK_prID" /> | |
</Identifier> | |
</ScalarOperator> | |
<ScalarOperator> | |
<Const ConstValue="NULL" /> | |
</ScalarOperator> | |
</Compare> | |
</ScalarOperator> | |
</Predicate> | |
</Filter> | |
</RelOp> | |
</ComputeScalar> | |
</RelOp> | |
</Concat> | |
</RelOp> | |
</Sort> | |
</RelOp> | |
</QueryPlan> | |
</StmtSimple> | |
</Statements> | |
</Batch> | |
</BatchSequence> | |
</ShowPlanXML> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment