Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2005
    Location
    london
    Posts
    9

    Unanswered: 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)

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Not all indexes are equal. Have you looked at the query plans for the select statements?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •