Here is the same example using large, permanent tables.
create table dbo.Parcel (
ParcelId int,
Shipper varchar(50),
Consignee varchar(50)
);
create clustered index cix_dboParcel_ParcelId on dbo.Parcel ( ParcelId asc );
create table dbo.ParcelDetail(
ParcelId int,
ScannedAtStation varchar(50),
ScannedAtTime datetime
);
create clustered index cix_dboParcelDetail_ParcelId on dbo.ParcelDetail ( ParcelId asc );
declare @counter int = 0;
set nocount on;
while @counter < 50000
begin
insert into dbo.Parcel values
(1*@counter, 'Romeo', 'Julia' ),
(2*@counter, 'Donald', 'Daisy' ),
(3*@counter, 'someone', 'nobody' )
insert into dbo.ParcelDetail values
( 1*@counter, 'Austria', '20120101' ),
( 1*@counter, 'Germany', '20120103' ),
( 1*@counter, 'UK', '20120110' ),
( 2*@counter, 'Spain', '20120101' ),
( 2*@counter, 'Portugal', '20120201' ),
( 3*@counter, 'Italy', '20120301' ),
( 3*@counter, 'Switzerland', '20120310' )
set @counter += 1;
end
select
p.ParcelId,
p.Shipper,
p.Consignee,
TransportRoute =
(
select *
from dbo.ParcelDetail detail
where detail.ParcelId = p.ParcelId
for xml auto
)
from dbo.Parcel p
where p.ParcelId between 10 and 20
select
p.ParcelId,
p.Shipper,
p.Consignee,
TransportRoute =
(
select *
from dbo.ParcelDetail detail
where detail.ParcelId = p.ParcelId
for xml auto
)
from dbo.Parcel p
where p.ParcelId between 1 and 100000
drop table dbo.Parcel;
drop table dbo.ParcelDetail;
When looking at the execution plan of the two selects we see that the index seek is done on both tables, but the subselect still causes the loop join which I want to eliminate.