SQL Server Performance Forum – Threads Archive

Why does this SQL slow down or hang?????

I’m trying to provide as much information as possible so here is goes: SQL 2005 SP1 + June Hotfixes, Win2003 SP1 with latest Hotfixes. Machine is AMD Opteron with 4GB mem.
I have some SQL I’m trying to run in a highly normalized table structure. Most of the time it runs ok (2 to 5 mintues), then sometimes it takes hours, and sometimes it runs over 15+ hours before I end up killing the process (seems to be hung with no indication). The entity table might contain 3 million records and the attribute table usually will contain about 15 million.
There isn’t any locking/blocking going on. The files are not growing since enough space is allocated. Is this a case of SQL Server tripping over itself? Are index pages being locked while it’s trying to update itself? If so, I can’t see it in the Activity monitor. Is it not able to update a table while referencing itself several times? I thought I got around this problem by changing the update to a select into #temptable, then using the temp table to update the attribute table, but it still happened. I finally added another filegroup and put all the nonclustered indexes on the new filegroup then it stopped (I’m still using the temp tables also). Can some tell me why this is happening? Questions? Comments? I’m just looking for a better understanding of SQL 2005. Query description: I’m trying to find duplicates based on an entity type and two attributes that match. There could be multiple matches so I want to set the new_eid to the first one I find. If there is a better way, please let me know. I’m dealing with large amounts of data at a time 500MB and speed is most important! CREATE TABLE ENTITY(
ID bigint NOT NULL,
TYPE varchar(100),
CONSTRAINT PK_STAGE_ENTITY PRIMARY KEY CLUSTERED(ID ASC)) CREATE TABLE ATTRIBUTE(
ID bigint NOT NULL,
EID bigint NOT NULL,
TYPE varchar(100),
VALUE varchar(255),
new_eid int NULL,
CONSTRAINT PK_STAGE_STRINGATTRIBUTE PRIMARY KEY CLUSTERED
(ID ASC)) CREATE NONCLUSTERED INDEX ix_se_type ON ENTITY (Type);
CREATE NONCLUSTERED INDEX ix_ss_eid ON ATTRIBUTE (EID ASC);
CREATE NONCLUSTERED INDEX ix_type_value ON ATTRIBUTE (TYPE ASC,VALUE ASC);
CREATE NONCLUSTERED INDEX ix_new_eid ON ATTRIBUTE (NEW_EID ASC); Update b set b.new_eid = a.eid
from attribute a
inner join entity se on a.eid = se.id
inner join attribute b on (a.type = b.type and a.value = b.value and a.id <> b.id and a.eid <> b.eid)
where se.type = ‘human’ and a.type = ‘name’ and exists
(select 1 from attribute y
inner join entity x on y.eid = x.id
inner join attribute z on (y.type = z.type and y.value = z.value and y.id <> z.id and y.eid <> z.eid)
where x.type = ‘human’ and y.type = ‘dob’ and y.eid = a.eid and z.eid = b.eid)
and a.id in (select min(a1.id) from attribute a1
inner join entity se1 on a1.eid = se1.id
inner join attribute b1 on (a1.type = b1.type and a1.value = b1.value and a1.id <> b1.id and a1.eid <> b1.eid)
where se1.type = ‘human’ and a1.type = ‘name’ and exists
(select 1 from attribute y1
inner join entity x1 on y1.eid = x1.id
inner join attribute z1 on (y1.type = z1.type and y1.value = z1.value and y1.id <> z1.id and y1.eid <> z1.eid)
where x1.type = ‘human’ and y1.type = ‘dob’ and y1.eid = a1.eid and z1.eid = b1.eid)
group by a1.value) Regards,
Ryan

It seems like there must be a simpler update statement for this. Can you post a few rows of sample data? Because I am slightly confused about what exactly you are defining as a duplicate. Specifically, why are the "exists" clauses there? What do they do?

Sample data: Insert into entity (1,’human’)
Insert into entity (2,’human’)
Insert into entity (3,’human’)
Insert into entity (4,’human’)
Insert into entity (5,’animal’) Insert into attribute (1,1,’name’,’John Smith’, NULL)
Insert into attribute (2,1,’dob’, ’01/01/1970′, NULL)
Insert into attribute (3,2,’name’, ‘Kevin Smith’, NULL)
Insert into attribute (4,2,’dob’, ’02/02/1970′, NULL)
Insert into attribute (5,3,’name’, ‘John Smith’, NULL)
Insert into attribute (6,3,’dob’, ’01/01/1970′, NULL)
Insert into attribute (7,4,’name’,’John Smith’, NULL)
Insert into attribute (8,4,’dob’,’01/01/1970′, NULL)
Insert into attriubte (9,4,’pob’,’USA’, NULL)
Insert into attribute (10,5,’name’,’Fido’,NULL) Does this make more sense? I’m trying to compress all the duplicates into one.
There are different entities and multiple attributes for each entity. Ryan

is there a large difference in affected row count from run to run?
on each run, compared the estimated row count to the actual row count, look for large discrepancies is there disk activity when the update takes a long time?
to which files?
use fn_filestats or whatever its called what the is size of the tempdb while this is running? described the estimated execution plan
or better yet, since you are on 2005,
save the execution plan in xml, ie, .sqlplan paste the content in here, those with 2005 can look at the plan

There is disk activity mainly in the file I hold the indexes. Query plan: <?xml version="1.0" encoding="utf-16"?> <ShowPlanXML xmlns<img src=’/community/emoticons/emotion-12.gif’ alt=’:x’ />si="http://www.w3.org/2001/XMLSchema-instance" xmlns<img src=’/community/emoticons/emotion-12.gif’ alt=’:x’ />sd="http://www.w3.org/2001/XMLSchema" Version="1.0" Build="9.00.2153.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> <BatchSequence> <Batch> <Statements> <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" StatementSubTreeCost="25.0896" StatementText=" Update b set b.new_eid = a.eid#xD;#xA;from attribute a#xD;#xA;inner join entity se on a.eid = se.id#xD;#xA;inner join attribute b on (a.type = b.type and a.value = b.value and a.id <> b.id and a.eid <> b.eid)#xD;#xA;where se.type = ‘human’ and a.type = ‘name’ and exists#xD;#xA;(select 1 from attribute y#xD;#xA;inner join entity x on y.eid = x.id#xD;#xA;inner join attribute z on (y.type = z.type and y.value = z.value and y.id <> z.id and y.eid <> z.eid)#xD;#xA;where x.type = ‘human’ and y.type = ‘dob’ and y.eid = a.eid and z.eid = b.eid)#xD;#xA;and a.id in (select min(a1.id) from attribute a1#xD;#xA;inner join entity se1 on a1.eid = se1.id#xD;#xA;inner join attribute b1 on (a1.type = b1.type and a1.value = b1.value and a1.id <> b1.id and a1.eid <> b1.eid)#xD;#xA;where se1.type = ‘human’ and a1.type = ‘name’ and exists#xD;#xA;(select 1 from attribute y1#xD;#xA;inner join entity x1 on y1.eid = x1.id#xD;#xA;inner join attribute z1 on (y1.type = z1.type and y1.value = z1.value and y1.id <> z1.id and y1.eid <> z1.eid)#xD;#xA;where x1.type = ‘human’ and y1.type = ‘dob’ and y1.eid = a1.eid and z1.eid = b1.eid)#xD;#xA;group by a1.value)" StatementType="UPDATE"> <StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" /> <QueryPlan CachedPlanSize="120"> <RelOp AvgRowSize="9" EstimateCPU="2E-06" EstimateIO="0.02" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Update" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Update" EstimatedTotalSubtreeCost="25.0896"> <OutputList /> <Update> <Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[PK_STAGE_STRINGATTRIBUTE]" /> <Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[ix_new_eid]" /> <SetPredicate> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[new_eid] = [Expr1027]"> <ScalarExpressionList /> </ScalarOperator> </SetPredicate> <RelOp AvgRowSize="20" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="25.0696"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="ID" /> <ColumnReference Column="Expr1027" /> <ColumnReference Column="Expr1034" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1034" /> <ScalarOperator ScalarString="[Expr1034]"> <Identifier> <ColumnReference Column="Expr1034" /> </Identifier> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="20" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="25.0696"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="ID" /> <ColumnReference Column="Expr1027" /> <ColumnReference Column="Expr1034" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1034" /> <ScalarOperator ScalarString="CASE WHEN [Expr1028] THEN (1) ELSE (0) END"> <IF> <Condition> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1028" /> </Identifier> </ScalarOperator> </Condition> <Then> <ScalarOperator> <Const ConstValue="(1)" /> </ScalarOperator> </Then> <Else> <ScalarOperator> <Const ConstValue="(0)" /> </ScalarOperator> </Else> </IF> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="20" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="25.0696"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="ID" /> <ColumnReference Column="Expr1027" /> <ColumnReference Column="Expr1028" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1028" /> <ScalarOperator ScalarString="CASE WHEN [Stage].[dbo].[ATTRIBUTE].[new_eid] as .[new_eid] = [Expr1027] THEN (1) ELSE (0) END"> <IF> <Condition> <ScalarOperator> <Compare CompareOp="BINARY IS"> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="new_eid" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1027" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Condition> <Then> <ScalarOperator> <Const ConstValue="(1)" /> </ScalarOperator> </Then> <Else> <ScalarOperator> <Const ConstValue="(0)" /> </ScalarOperator> </Else> </IF> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="23" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Top" NodeId="5" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="25.0696"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="ID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="new_eid" /> <ColumnReference Column="Expr1027" /> </OutputList> <Top RowCount="true" IsPercent="false" WithTies="false"> <TopExpression> <ScalarOperator ScalarString="(0)"> <Const ConstValue="(0)" /> </ScalarOperator> </TopExpression> <RelOp AvgRowSize="23" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="6" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="25.0696"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="ID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="new_eid" /> <ColumnReference Column="Expr1027" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1027" /> <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Stage].[dbo].[ATTRIBUTE].[EID] as [a].[EID],0)"> <Convert DataType="int" Style="0" Implicit="true"> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[ a]" Column="EID" /> </Identifier> </ScalarOperator> </Convert> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="27" EstimateCPU="0.000100027" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Distinct Sort" NodeId="7" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="25.0696"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="ID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="new_eid" /> </OutputList> <MemoryFractions Input="0.5" Output="1" /> <Sort Distinct="true"> <OrderBy> <OrderByColumn Ascending="true"> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="ID" /> </OrderByColumn> </OrderBy> <RelOp AvgRowSize="27" EstimateCPU="4.49054E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Left Semi Join" NodeId="8" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="25.0582"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="ID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="new_eid" /> </OutputList> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="EID" /> </OuterReferences> <RelOp AvgRowSize="43" EstimateCPU="5.1566E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.07429" LogicalOp="Filter" NodeId="9" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="25.0417"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="ID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="new_eid" /> </OutputList> <Filter StartupExpression="false"> <RelOp AvgRowSize="43" EstimateCPU="4.49054E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.07429" LogicalOp="Inner Join" NodeId="10" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="25.0417"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="ID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="new_eid" /> </OutputList> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="ID" /> </OuterReferences> <RelOp AvgRowSize="31" EstimateCPU="4.49054E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.07429" LogicalOp="Inner Join" NodeId="11" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="25.0383"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="ID" /> </OutputList> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="ID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="VALUE" /> </OuterReferences> <RelOp AvgRowSize="44" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="12" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="25.035"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="ID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="VALUE" /> </OutputList> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /> </OuterReferences> <RelOp AvgRowSize="44" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="14" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="25.0317"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="ID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="VALUE" /> </OutputList> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Column="Expr1026" /> </OuterReferences> <RelOp AvgRowSize="15" EstimateCPU="0.000100015" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Distinct Sort" NodeId="15" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="25.0285"> <OutputList> <ColumnReference Column="Expr1026" /> </OutputList> <MemoryFractions Input="0.5" Output="0.5" /> <Sort Distinct="true"> <OrderBy> <OrderByColumn Ascending="true"> <ColumnReference Column="Expr1026" /> </OrderByColumn> </OrderBy> <RelOp AvgRowSize="15" EstimateCPU="1.1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="16" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="25.0171"> <OutputList> <ColumnReference Column="Expr1026" /> </OutputList> <StreamAggregate> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1026" /> <ScalarOperator ScalarString="MIN([Stage].[dbo].[ATTRIBUTE].[ID] as [a1].[ID])"> <Aggregate AggType="MIN" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="ID" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> </DefinedValues> <GroupBy> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="VALUE" /> </GroupBy> <RelOp AvgRowSize="36" EstimateCPU="0.000100036" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Sort" NodeId="17" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="25.0171"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="ID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="VALUE" /> </OutputList> <MemoryFractions Input="0.0047619" Output="0.5" /> <Sort Distinct="false"> <OrderBy> <OrderByColumn Ascending="true"> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="VALUE" /> </OrderByColumn> </OrderBy> <RelOp AvgRowSize="36" EstimateCPU="16.5087" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Right Semi Join" NodeId="18" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="25.0057"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="ID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="VALUE" /> </OutputList> <MemoryFractions Input="0" Output="0" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="EID" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="EID" /> </HashKeysProbe> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />robeResidual> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[EID] as [y1].[EID]=[Stage].[dbo].[ATTRIBUTE].[EID] as [a1].[EID] AND [Stage].[dbo].[ATTRIBUTE].[EID] as [z1].[EID]=[Stage].[dbo].[ATTRIBUTE].[EID] as [b1].[EID ]"> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="EID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="EID" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="EID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="EID" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </ProbeResidual> <RelOp AvgRowSize="23" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="19" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0164332"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="EID" /> </OutputList> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="EID" /> </OuterReferences> <RelOp AvgRowSize="23" EstimateCPU="4.8E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Filter" NodeId="20" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="0.0131459"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="EID" /> </OutputList> <Filter StartupExpression="false"> <RelOp AvgRowSize="23" EstimateCPU="4.18E-06" Estim ateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="21" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0131454"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="EID" /> </OutputList> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="ID" /> </OuterReferences> <RelOp AvgRowSize="23" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="22" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00985814"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="ID" /> </OutputList> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="ID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="VALUE" /> </OuterReferences> <RelOp AvgRowSize="44" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="23" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="ID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="VALUE" /> </OutputList> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="ID" /> </OuterReferences> <RelOp AvgRowSize="36" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Seek" NodeId="24" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="ID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="VALUE" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="ID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="VALUE" /> </DefinedValue> </DefinedValues> <Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[ix_type_value]" Alias="[y1]" TableReferenceId="-1" /> <SeekPredicates> <SeekPredicate> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />refix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="TYPE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="’dob’"> <Const ConstValue="’dob’" /> </ScalarOperator> </RangeExpressions> </Prefix> </SeekPredicate> </SeekPredicates> </IndexScan> </RelOp> <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="26" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="EID" /> </OutputList> <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="EID" /> </DefinedValue> </DefinedValues> <Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[PK_STAGE_STRINGATTRIBUTE]" Alias="[y1]" TableReferenceId="-1" /> <SeekPredicates> <SeekPredicate> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />refix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="ID" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[ID] as [y1].[ID]"> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="ID" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekPredicate> </SeekPredicates> </IndexScan> </RelOp> </NestedLoops> </RelOp> <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Seek" NodeId="30" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="ID" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="ID" /> </DefinedValue> </DefinedValues> <Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[ix_type_value]" Alias="[z1]" TableReferenceId="-1" /> <SeekPredicates> <SeekPredicate> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />refix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="TYPE" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="VALUE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="’dob’"> <Const ConstValue="’dob’" /> </ScalarOperator> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[VALUE] as [y1].[VALUE]"> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="VALUE" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekPredicate> </SeekPredicates> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />redicate> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[ID ] as [y1].[ID]<>[Stage].[dbo].[ATTRIBUTE].[ID] as [z1].[ID]"> <Compare CompareOp="NE"> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="ID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="ID" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </NestedLoops> </RelOp> <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="32" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="EID" /> </OutputList> <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="EID" /> </DefinedValue> </DefinedValues> <Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[PK_STAGE_STRINGATTRIBUTE]" Alias="[z1]" TableReferenceId="-1" /> <SeekPredicates> <SeekPredicate> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />refix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="ID" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[ID] as [z1].[ID]"> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="ID" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekPredicate> </SeekPredicates> </IndexScan> </RelOp> </NestedLoops> </RelOp> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />redicate> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[EID] as [y1].[EID]<>[Stage].[dbo].[ATTRIBUTE].[EID] as [z1].[EID]"> <Compare CompareOp="NE"> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="EID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z1]" Column="EID" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </Filter> </RelOp> <RelOp AvgRowSize="9" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Seek" NodeId="37" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831"> <OutputList /> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"> <DefinedValues /> <Object Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Index="[ix_se_type]" Alias="[x1]" TableReferenceId="-1" /> <SeekPredicates> <SeekPredicate> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />refix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[x1]" Column="TYPE" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[x1]" Column="ID" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="’human’"> <Const ConstValue="’human’" /> </ScalarOperator> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[EID] as [y1].[EID]"> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y1]" Column="EID" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekPredicate> </SeekPredicates> </IndexScan> </RelOp> </NestedLoops& gt; </RelOp> <RelOp AvgRowSize="60" EstimateCPU="6.25028" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2523200" LogicalOp="Inner Join" NodeId="38" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="8.48064"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="ID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="VALUE" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="EID" /> </OutputList> <MemoryFractions Input="0.776952" Output="0.995238" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="VALUE" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="VALUE" /> </HashKeysProbe> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />robeResidual> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[VALUE] as [a1].[VALUE]=[Stage].[dbo].[ATTRIBUTE].[VALUE] as [b1].[VALUE] AND [Stage].[dbo].[ATTRIBUTE].[ID] as [a1].[ID]<>[Stage].[dbo].[ATTRIBUTE].[ID] as [b1].[ID] AND [Stage].[dbo].[ATTRIBUTE].[EID] as [a1].[EID]<>[Stage].[dbo].[ATTRIBUTE].[EID] as [b1].[EID]"> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="VALUE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="VALUE" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="NE"> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="ID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="ID" /> </Identifier> </ScalarOperator& gt; </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="NE"> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="EID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="EID" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </ProbeResidual> <RelOp AvgRowSize="44" EstimateCPU="0.362119" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="17669.8" LogicalOp="Inner Join" NodeId="39" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1.31272"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="ID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="VALUE" /> </OutputList> <MemoryFractions Input="1" Output="0.223048" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[se1]" Column="ID" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="EID" /> </HashKeysProbe> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />robeResidual> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[EID] as [a1].[EID]=[Stage].[dbo].[ENTITY].[ID] as [se1].[ID]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="EID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[se1]" Column="ID" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </ProbeResidual> <RelOp AvgRowSize="15" EstimateCPU="0.0097853" EstimateIO="0.0231883" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="8753" LogicalOp="Index Seek" NodeId="40" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0329736"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[se1]" Column="ID" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[se1]" Column="ID" /> </DefinedValue> </DefinedValues> <Object Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Index="[ix_se_type]" Alias="[se1]" TableReferenceId="-1" /> <SeekPredicates> <SeekPredicate> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />refix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[se1]" Column="TYPE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="’human’"> <Const ConstValue="’human’" /> </ScalarOperator> </RangeExpressions> </Prefix> </SeekPredicate> </SeekPredicates> </IndexScan> </RelOp> <RelOp AvgRowSize="53" EstimateCPU="0.142678" EstimateIO="0.712755" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="27481" LogicalOp="Clustered Index Scan" NodeId="41" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.855433"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="ID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="VALUE" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="ID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="EID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="VALUE" /> </DefinedValue> </DefinedValues> <Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[PK_STAGE_STRINGATTRIBUTE]" Alias="[a1]" TableReferenceId="-1" /> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />redicate> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[TYPE] as [a1].[TYPE]=’name’"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a1]" Column="TYPE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="’name’" /> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </Hash> </RelOp> <RelOp AvgRowSize="53" EstimateCPU="0.142678" EstimateIO="0.712755" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="27481" LogicalOp="Clustered Index Scan" NodeId="43" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.855433"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="ID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="VALUE" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="ID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="EID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="VALUE" /> </DefinedValue> </DefinedValues> <Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[PK_STAGE_STRINGATTRIBUTE]" Alias="[b1]" TableReferenceId="-1" /> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />redicate> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[TYPE] as [b1].[TYPE]=’name’"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier&amp ;gt; <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[b1]" Column="TYPE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="’name’" /> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Sort> </RelOp> </StreamAggregate> </RelOp> </Sort> </RelOp> <RelOp AvgRowSize="53" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="49" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="ID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="TYPE" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="VALUE" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="ID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="TYPE" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="VALUE" /> </DefinedValue> </DefinedValues> <Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[PK_STAGE_STRINGATTRIBUTE]" Alias="[a]" TableReferenceId="-1" /> <SeekPredicates> <SeekPredicate> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />refix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="ID" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[Expr1026]"> <Iden tifier> <ColumnReference Column="Expr1026" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekPredicate> </SeekPredicates> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />redicate> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[TYPE] as [a].[TYPE]=’name’"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="TYPE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="’name’" /> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </NestedLoops> </RelOp> <RelOp AvgRowSize="9" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Seek" NodeId="51" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831"> <OutputList /> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"> <DefinedValues /> <Object Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Index="[ix_se_type]" Alias="[se]" TableReferenceId="-1" /> <SeekPredicates> <SeekPredicate> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />refix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[se]" Column="TYPE" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[se]" Column="ID" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="’human’"> <Const ConstValue="’human’" /> </ScalarOperator> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[EID] as [a].[EID]"> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekPredicate> </SeekPredicates> </IndexScan> </RelOp> </NestedLoops> </RelOp> <RelOp AvgRowSize="15" EstimateCPU="0.000158182" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.07429" LogicalOp="Index Seek" NodeId="52" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00328318"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table ="[ATTRIBUTE]" Alias="" Column="ID" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="ID" /> </DefinedValue> </DefinedValues> <Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[ix_type_value]" Alias="" TableReferenceId="-1" /> <SeekPredicates> <SeekPredicate> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />refix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="TYPE" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="VALUE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="’name’"> <Const ConstValue="’name’" /> </ScalarOperator> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[VALUE] as [a].[VALUE]"> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="VALUE" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekPredicate> </SeekPredicates> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />redicate> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[ID] as [a].[ID]<>[Stage].[dbo].[ATTRIBUTE].[ID] as .[ID]"> <Compare CompareOp="NE"> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="ID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="ID" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </NestedLoops> </RelOp> <RelOp AvgRowSize="19" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0.0742907" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="54" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00333661"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="new_eid" /> </OutputList> <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="EID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="new_eid" /> </DefinedValue> </DefinedValues> <Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[PK_STAGE_STRINGATTRIBUTE]" Alias="" TableReferenceId="-1" /> <SeekPredicates> <SeekPredicate> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />refix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="ID" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[ID] as .[ID]"> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="ID" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekPredicate> </SeekPredicates> </IndexScan> </RelOp> </NestedLoops> </RelOp> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />redicate> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[EID] as [a].[EID]<>[Stage].[dbo].[ATTRIBUTE].[EID] as .[EID]"> <Compare CompareOp="NE"> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="EID" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </Filter> </RelOp> <RelOp AvgRowSize="9" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0.0742907" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="61" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0165781"> <OutputList /> <NestedLoops Optimized="false"> <RelOp AvgRowSize="18" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0.0742907" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="62" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00333647"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[x]" Column="TYPE" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[x]" Column="TYPE" /> </DefinedValue> </DefinedValues> <Object Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Index="[PK_STAGE_ENT ITY]" Alias="[x]" TableReferenceId="-1" /> <SeekPredicates> <SeekPredicate> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />refix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[x]" Column="ID" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[EID] as [a].[EID]"> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekPredicate> </SeekPredicates> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />redicate> <ScalarOperator ScalarString="[Stage].[dbo].[ENTITY].[TYPE] as [x].[TYPE]=’human’"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ENTITY]" Alias="[x]" Column="TYPE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="’human’" /> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </IndexScan> </RelOp> <RelOp AvgRowSize="9" EstimateCPU="8.8E-07" EstimateIO="0" EstimateRebinds="0.0742907" EstimateRewinds="0" EstimateRows="1" LogicalOp="Filter" NodeId="63" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="0.0132366"> <OutputList /> <Filter StartupExpression="false"> <RelOp AvgRowSize="23" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0.0742907" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="64" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0132357"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="EID" /> </OutputList> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="ID" /> </OuterReferences> <RelOp AvgRowSize="23" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0.0742907" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="65" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00989455"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="ID" /> </OutputList> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="ID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="VALUE" /> </OuterReferences> <RelOp AvgRowSize="44" EstimateCPU=&quo t;4.8E-07" EstimateIO="0" EstimateRebinds="0.0742907" EstimateRewinds="0" EstimateRows="1" LogicalOp="Filter" NodeId="66" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="0.0065947"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="ID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="VALUE" /> </OutputList> <Filter StartupExpression="false"> <RelOp AvgRowSize="44" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0.0742907" EstimateRows="1" LogicalOp="Inner Join" NodeId="67" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00659418"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="ID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="EID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="VALUE" /> </OutputList> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="ID" /> </OuterReferences> <RelOp AvgRowSize="36" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0.0742907" EstimateRows="1" LogicalOp="Index Seek" NodeId="68" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00329485"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="ID" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="VALUE" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="ID" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="VALUE" /> </DefinedValue> </DefinedValues> <Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[ix_type_value]" Alias="[y]" TableReferenceId="-1" /> <SeekPredicates> <SeekPredicate> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />refix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="TYPE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="’dob’"> <Const ConstValue="’dob’" /> </ScalarOperator> </RangeExpressions> </Prefix> </SeekPredicate> </SeekPredicates> &am p;lt;/IndexScan> </RelOp> <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0.0742907" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="70" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00329485"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="EID" /> </OutputList> <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="EID" /> </DefinedValue> </DefinedValues> <Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[PK_STAGE_STRINGATTRIBUTE]" Alias="[y]" TableReferenceId="-1" /> <SeekPredicates> <SeekPredicate> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />refix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="ID" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[ID] as [y].[ID]"> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="ID" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekPredicate> </SeekPredicates> </IndexScan> </RelOp> </NestedLoops> </RelOp> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />redicate> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[EID] as [y].[EID]=[Stage].[dbo].[ATTRIBUTE].[EID] as [a].[EID]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="EID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[a]" Column="EID" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </Filter> </RelOp> <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0.0742907" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Seek" NodeId="75" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00329485"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]&quot ; Column="ID" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="ID" /> </DefinedValue> </DefinedValues> <Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[ix_type_value]" Alias="[z]" TableReferenceId="-1" /> <SeekPredicates> <SeekPredicate> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />refix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="TYPE" /> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="VALUE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="’dob’"> <Const ConstValue="’dob’" /> </ScalarOperator> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[VALUE] as [y].[VALUE]"> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="VALUE" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekPredicate> </SeekPredicates> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />redicate> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[ID] as [y].[ID]<>[Stage].[dbo].[ATTRIBUTE].[ID] as [z].[ID]"> <Compare CompareOp="NE"> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="ID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="ID" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </NestedLoops> </RelOp> <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0.0742907" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="77" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00333661"> <OutputList> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="EID" /> </OutputList> <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[Stage]& quot; Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="EID" /> </DefinedValue> </DefinedValues> <Object Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Index="[PK_STAGE_STRINGATTRIBUTE]" Alias="[z]" TableReferenceId="-1" /> <SeekPredicates> <SeekPredicate> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />refix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="ID" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[ID] as [z].[ID]"> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="ID" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekPredicate> </SeekPredicates> </IndexScan> </RelOp> </NestedLoops> </RelOp> &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />redicate> <ScalarOperator ScalarString="[Stage].[dbo].[ATTRIBUTE].[EID] as [y].[EID]<>[Stage].[dbo].[ATTRIBUTE].[EID] as [z].[EID] AND [Stage].[dbo].[ATTRIBUTE].[EID] as [z].[EID]=[Stage].[dbo].[ATTRIBUTE].[EID] as .[EID]"> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="NE"> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[y]" Column="EID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="EID" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="[z]" Column="EID" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[Stage]" Schema="[dbo]" Table="[ATTRIBUTE]" Alias="" Column="EID" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </Predicate> </Filter> </RelOp> </NestedLoops> </RelOp> </NestedLoops> </RelOp> </Sort> </RelOp> </ComputeScalar> </RelOp> </Top> </RelOp> </ComputeScalar> </RelOp> </ComputeScalar> </RelOp> </ComputeScalar> </RelOp> </Update> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> </BatchSequence> </ShowPlanXML>

There was a problem with the SQL in the first post. I have updated it so it will create the tables correctly. Also, here is output from sp_lock while the slow-down is occurring. 13 is the db and 2 is tempdb. My spid is 59. Instead of doing an update, I changed the sql to select into a temp table then I use that to update the table. I thought I had the problem resolved, but it’s back again: 51400DB SGRANT
541000DB SGRANT
551000DB SGRANT
56111151510180TAB ISGRANT
561300DB SGRANT
591300DB SGRANT
591355750582PAG3:8 ISGRANT
59136386253180TAB SGRANT
59200HBT[BULK_OPERATION] IXGRANT
5913535752290TAB ISGRANT
5923792004510PAG1:275 XGRANT
59200MD1(169a23c3:0:0) Sch-SGRANT
592990RID1:275:1 XGRANT
592990RID1:275:2 XGRANT
591321050585350TAB ISGRANT
591355750582KEY(ae00ca69d10c) SGRANT
59200DB[BULKOP_BACKUP_LOG] NULLGRANT
5923792004510TAB XGRANT
591321050585352KEY(3c00e927312a) SGRANT
59136226252610TAB SGRANT
5913535752293KEY(c203e4e0095c) SGRANT
591355750580TAB ISGRANT
59200DB[BULKOP_BACKUP_DB] NULLGRANT
601000DB SGRANT
611000DB SGRANT
631300DB SGRANT
641000DB SGRANT
65400DB SGRANT
671300DB SGRANT
681000DB SGRANT
691300DB SGRANT
701000DB SGRANT
711300DB SGRANT

Hi - This is an interesting problem. I came up with this statement (which is not exactly equivalent to yours, but is more general, so it might or might not be useful): <pre id="code"> select * from entity e1 where exists ( select * from entity e2 where e1.type = e2.type and e2.id < e1.id and not exists ( ( select type, value from attribute where eid = e1.id except select type, value from attribute where eid = e2.id ) union ( select type, value from attribute where eid = e2.id except select type, value from attribute where eid = e1.id ) ) ) </pre id="code"> This will find a "duplicate entity" defined as an entity for which another exists, having exactly the same attributes. This differs from yours in that I guess you look at just the name and DOB do define a duplicate, not the set of all attributes. But I thought you might find this useful, and it was fun to figure it out <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />. If one were also to add this foreign key relation with cascade: <pre id="code"> ALTER TABLE [dbo].[ATTRIBUTE] WITH CHECK ADD CONSTRAINT [FK_ATTRIBUTE_ENTITY] FOREIGN KEY([EID]) REFERENCES [dbo].[ENTITY] ([ID]) ON DELETE CASCADE GO ALTER TABLE [dbo].[ATTRIBUTE] CHECK CONSTRAINT [FK_ATTRIBUTE_ENTITY] GO </pre id="code"> Then one could delete from ENTITY with the criteria above, and have the duplicate Attribute rows also removed, automatically. Also, I think the query above could be modified to locate entities for which there is another entity that has a superset of the same attributes (by which I mean, for example, one entity has three attributes and another has those three plus two more, as in the John Smith of your example). Don’t know if this method would perform better for you or not <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Thanks Merrill!
I’m going to compare times with your SQL and what I wrote and let you know how it turns out. I’ll just put the type = ‘name’, etc in your SQL to do the proper matching. After updating the new_eid, I turn around and update the new_eid of the other attribute records that match eids. Then I update the eids of some other tables, then I set eid = new_eid on the attribute. I don’t delete any records or entities in these staging tables. It’s a lot of data and it takes time so I do an select distinct to filter out the dupes on updates and inserts. My new problem is sql gets "stuck". I’m performing a SELECT INTO into a temp table (granted I’m using bad practice and not creating the temp table first) and it never completes. No locking/blocking. I posted the sp_lock above. It’s been stuck since 8:42 this morning and I have no clue why? Like a challenge? It’s still sitting here grinding. Ryan

Here’s a swing at locating the "superset" duplicates — that is, if I have the logic right! be sure to double-check my thinking! — select *
from entity e1
where exists ( select *
from entity e2
where e1.type = e2.type
and ( — There is an entity with a lower id having the same attributes exactly
( e2.id < e1.id
and not exists (
( select type, value from attribute where eid = e1.id
except
select type, value from attribute where eid = e2.id )
union
( select type, value from attribute where eid = e2.id
except
select type, value from attribute where eid = e1.id )
)
)
or ( — There is another entity someplace with a superset including the same attributes
e2.id <> e1.id
and not exists
( select type, value from attribute where eid = e1.id
except
select type, value from attribute where eid = e2.id )
and exists
( select type, value from attribute where eid = e2.id
except
select type, value from attribute where eid = e1.id )
)
)
)

Can you pull up Perf Mon, or sysprocesses, and see what it’s doing? In my experience, SQL Server never "hangs" or "gets in its own way" — it’s practically always a situation where the query is just taking a long, long, LONG time, or else you would see an error message. If I had to bet, I’d say its working away doing something, and perhaps the process it has generated to satisfy the query is just very intensive. For example, we have seen some Select statements on a server here take several hours and perform billions of reads due to an indexing problem. During the time these were running (successfully, but VERY slowly) the server was just chugging along, 25% of it’s 4CPU capacity working away, table-scanning a whole table it had in RAM over and over and over.

Right. It’s not hanging, it’s tripping over itself. It is doing something, just not sure what. The sql statement will run fine (I’m loading data into the database) over and over again, then all of a sudden, I get this where 10 hours later, it’s not finished on something that should have only taken about 20 mintues. How do I prevent this from happening?
I’ll review the sql you sent also! Thanks!

It almost seems like it all of a sudden it uses the wrong execution plan or something of that nature. 9 times out of 10 it will run fine.

i can not look at this in detail now,
but try the following index change
CREATE NONCLUSTERED INDEX ix_type_value ON ATTRIBUTE (TYPE ASC,VALUE ASC, EID)
WITH DROP_EXISTING
also does this run only at night?
the other 2 indexes on ATTRIBUTE are not involved,
if the row count is high, and nothing else is running
drop those 2 then recreate later

How about this possibility: you are getting a query plan that uses nested loops due either to ‘bad’ statisics or perhaps a previous run is cached that really DID affect a small number of rows and SHOULD have used nested loops? I have seen queries go off into la-la land looping kajillions of times when they really should have been doing hash or merge activity. Maybe you can throw this statement into a sproc and force it to RECOMPILE on each execution. Make sure your stats are up to date. If you KNOW how it should do it’s work most efficiently (because you KNOW your data and data distributions) you can use query optimizer hints to force join/order, etc. Others have mentioned indexing and that should be evaluated too. Also, I didn’t see any NOLOCK table hints in your query either. Consider their appropriate use here. SQLGuru SQLGuru

Ok. Seems I had experimented with an index and change the type to be (EID, Type, Value) on the Attribute table when the left-most column should have been Type. It should have been like Joechang said above (Type, Value, EID). It may have been a different SQL that was hanging, but when Type was not first, it forgot type and value were in the index and decided to tablescan. Thank you to everyone that replied. Now another question: Should I drop the index on EID and just keep it in the composite index? Or should I keep it if some other queries reference it? Would it be bad to have EID in it’s own index and in another? Opinions? Ryan

]]>