SQL Server Performance

joins

Discussion in 'T-SQL Performance Tuning for Developers' started by bfarr23, Feb 26, 2004.

  1. bfarr23 New Member

    --Build the INSERT and FROM
    SET @SQLFrom = ' FROM dbo.Property P with (nolock) INNER JOIN dbo.Listing L with (nolock) ON P.PropertyID = L.ListingID'

    IF @LinkbackID = '' BEGIN --when doing linkback, we don't care abouthte PTA/LTA
    SET @SQLFrom = @SQLFrom + ' INNER JOIN dbo.Property_Type_Application PTA with (nolock) ON P.PropertyTypeID = PTA.PropertyTypeID AND PTA.ApplicationID = @ApplicationID'
    SET @SQLFrom = @SQLFrom + ' INNER JOIN dbo.Listing_Type_Application LTA with (nolock) ON L.ListingTypeID = LTA.ListingTypeID AND LTA.ApplicationID = @ApplicationID'
    END

    IF @ReqPropertyType = 1 SET @SQLFrom = @SQLFrom + ' INNER JOIN dbo.Property_Type PT with (nolock) ON P.PropertyTypeID = PT.PropertyTypeID'
    IF @ReqAddress = 1 SET @SQLFrom = @SQLFrom + ' INNER JOIN dbo.Address A with (nolock) ON A.PropertyID = P.PropertyID'
    IF @ReqLand = 1 SET @SQLFrom = @SQLFrom + ' INNER JOIN dbo.Land LA with (nolock) ON LA.PropertyID = P.PropertyID'
    IF @ReqBuilding = 1 SET @SQLFrom = @SQLFrom + ' INNER JOIN dbo.Building B with (nolock) ON B.LandID = LA.LandID'
    IF @ReqBuildingAge = 1 SET @SQLFrom = @SQLFrom + ' INNER JOIN dbo.Building_Age BA with (nolock) ON B.BuildingAgeID = BA.BuildingAgeID AND BA.CultureID = @CultureID'
    IF @ReqListingIndividual = 1 SET @SQLFrom = @SQLFrom + ' INNER JOIN dbo.Listing_Individual LI with (nolock) ON LI.ListingID = P.PropertyID'
    IF @ReqIndividual = 1 SET @SQLFrom = @SQLFrom + ' INNER JOIN dbo.Individual I with (nolock) ON LI.IndividualID = I.IndividualID'
    IF @ReqOrganization = 1 SET @SQLFrom = @SQLFrom + ' INNER JOIN dbo.Individual_Organization IO with (nolock) ON LI.IndividualID = IO.IndividualID INNER JOIN db:confused:rganization O with (nolock) ON O.OrganizationID = IO.OrganizationID INNER JOIN db:confused:rganization_Locale OL with (nolock) ON O.OrganizationID = OL.OrganizationID AND OL.CultureID = @CultureID'


    IF @ReqAreaSearch = 1 SET @SQLFrom = @SQLFrom + ' LEFT JOIN dbo.Area_Search ASL with (nolock) ON ASL.ListingAreaID = P.AreaID'


    IF @ReqPropertyTypeSearch = 1 SET @SQLFrom = @SQLFrom + ' INNER JOIN dbo.Property_Type_Search PTS with (nolock) ON PTS.LIstingPropertyTypeID = P.PropertyTypeID'
    IF @ReqPropertyFeatures = 1 SET @SQLFrom = @SQLFrom + ' INNER JOIN dbo.Property_Feature PF with (nolock) ON PF.PropertyID = P.PropertyID INNER JOIN dbo.Feature_Group FG with (nolock) ON PF.FeatureID = FG.FeatureID'


    - the LEFT JOIN there. Does that mean all rows are returned from the P. table?(property table)

Share This Page