Function Performance Question

can anyone explain to me why the code excerpt 1 performs 60 reads on my DB, and code excerpt 2 performs 140000 ?


I know that specifically the statements are doing different things but they are both inserting into tables based on input parameters.

All relevant fields are indexed so I wouldn't have thought this was the issue?

Does the number of joins really make such a difference to performance?


code excerpt 1 (60 reads)
INSERT INTO @table_var
SELECT dbo.Organisation.OrganisationName,
dbo.Organisation.DepartmentName,
dbo.Address.BuildingNumber,
dbo.BuildingName.BuildingName,
dbo.SubBuildingName.SubBuildingName,
Thoroughfare_1.ThoroughfareName AS DependentThoroughfareName,
ThoroughfareDescriptor_1.ThoroughfareDescriptor AS DependentThoroughfareDescriptor,
dbo.Thoroughfare.ThoroughfareName,
dbo.ThoroughfareDescriptor.ThoroughfareDescriptor,
dbo.Locality.DoubleDependentLocality,
dbo.Locality.DependentLocality,
dbo.Locality.PostTown,
dbo.Address.Outcode,
dbo.Address.Incode,
dbo.Address.ConcatenationIndicator
FROM dbo.Address INNER JOIN
dbo.BuildingName ON dbo.Address.BuildingNameKey = dbo.BuildingName.BuildingNameKey INNER JOIN
dbo.Locality ON dbo.Address.LocalityKey = dbo.Locality.LocalityKey INNER JOIN
dbo.Organisation ON dbo.Address.OrganisationKey = dbo.Organisation.OrganisationKey AND
dbo.Address.PostcodeType = dbo.Organisation.PostcodeType INNER JOIN
dbo.SubBuildingName ON dbo.Address.SubBuildingNameKey = dbo.SubBuildingName.SubBuildingNameKey INNER JOIN
dbo.Thoroughfare ON dbo.Address.ThoroughfareKey = dbo.Thoroughfare.ThoroughfareKey INNER JOIN
dbo.ThoroughfareDescriptor ON dbo.Address.ThoroughfareDescriptorKey = dbo.ThoroughfareDescriptor.ThoroughfareDescriptorK ey INNER JOIN
dbo.Thoroughfare Thoroughfare_1 ON dbo.Address.DependentThoroughfareKey = Thoroughfare_1.ThoroughfareKey INNER JOIN
dbo.ThoroughfareDescriptor ThoroughfareDescriptor_1 ON
dbo.Address.DependentThoroughfareDescriptorKey = ThoroughfareDescriptor_1.ThoroughfareDescriptorKey
WHERE (dbo.Address.AddressKey = @addresskey) AND
(dbo.Address.OrganisationKey = @organisationkey) AND
(dbo.Address.PostcodeType = @postcodetype)




code excerpt 2:


INSERT INTO @table_var_out
SELECT dbo.Organisation.OrganisationName, dbo.Address.OrganisationKey, dbo.Address.AddressKey, dbo.Address.PostcodeType
FROM dbo.Address INNER JOIN
dbo.Organisation ON dbo.Address.OrganisationKey = dbo.Organisation.OrganisationKey AND
dbo.Address.PostcodeType = dbo.Organisation.PostcodeType
WHERE (dbo.Address.Outcode = @outcode) AND (dbo.Address.Incode = @incode)