Reputation: 2508
Consider the following situation:
Here is a code sample:
--1. There is an xml that contains data @XmlData
declare @XmlData xml = '
<Root>
<Item rid="522822E251CA11D18F1400A02427D15E" />
</Root>'
--2. The @XmlData is extracted into a relational table (@ItemList)
declare @ItemList table (ItemRid char(32) primary key);
insert into @ItemList(ItemRid)
select Tab.rid
from ( select Item.Rid.value('@rid','char(32)') rid
from @XmlData.nodes('(/Root)[1]/Item') Item(Rid)) Tab
group by Tab.rid
--3. A table variable is defined (@table)
declare @Table TABLE
(
Rid char(32) primary key
,Rid1 char(32)
,Rid2 char(32)
)
--4. The data extracted from 2 db tables (tblCdbA0 and tblCdbG2) into @Table using exists clause
insert into @Table(Rid,Rid1,Rid2)
select A0.A0RID, A0.T4RID, A0.T6RID
from tblCdbA0 A0 with (nolock)
where
exists (select null
from tblCdbG2 G2 with(nolock)
inner join @ItemList Items
on Items.ItemRid = G2.G0RID
where A0.A0RID=G2.A0RID)
The table tblCdbG2 contains 63582 rows. Let's look what actual execution plan of the last statement shows us:
The execution plan shows that number of data rows that are extracted from tblCdbG2 table equal to 807 rows instead of 63582.
In my view, the number of rows that are extracted from tblCdbG2 must be 63582. After inner join with @ItemList is applied the number of rows must be 807.
What is the reason to show already filtered rows number before inner join?
Update:
I have slightly modified the existing query and the plan shows the same value 807.
The query:
select G2.A0RID
from tblCdbG2 G2 with(nolock)
inner join @ItemList Items
on Items.ItemRid = G2.G0RID
The plan:
Upvotes: 2
Views: 282
Reputation: 18410
The nested loop to left is pulling every row from @ItemList. For each row from @Item list it is using an index (Clustered Index Seek) to find just the matching row(s) in tblCdbG2. It does not first extract all 60+K rows.
Upvotes: 2