Friday, March 9, 2012

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)Not all indexes are equal. Have you looked at the query plans for the select statements?

No comments:

Post a Comment