Skip to main content
edited tags; edited title; edited tags
Link
200_success
  • 144.2k
  • 22
  • 188
  • 473

How can I modify this query A search for performance?reviews of places

deleted 638 characters in body
Source Link
ALTER PROCEDURE [dbo].[PlaceListSelect] @cateID int,
    @searchTxt varchar(50),
    @userID int,
    @RowIndex int,
    @MaxRows int,
    @Location varchar(50),
    @IsMuslimFaceSpecial int,
    @IsUserHadNotBeenTo int,
    @IsUserHadBeenTo int,
    @IsUserFriendsHaveBeenTo int,
    @IsMyPlacesonly int
    AS
    
    BEGIN
                    DECLARE @EndRow int
                    SET @EndRow = @RowIndex + @MaxRows - 1
                    ;
                    WITH tbl
                    AS (SELECT
                                    P.*,
                                    (SELECT
                                                    SUM(Rating) / COUNT(Rating)
                                    FROM PlaceReview AS R
                                    WHERE R.PlaceId = P.PlaceId)
                                    AS Rating,
                                    pc.PlaceCategory,
                                    ISNULL(PP.DefaultLandingTab, 0) AS DefaultLandingTab,
                                    ISNULL((SELECT
                                                    City
                                    FROM dbo.Cities
                                    WHERE CityId = P.CityId), '') AS City,
                                    ISNULL((SELECT
                                                    Country
                                    FROM dbo.Countries
                                    WHERE CountryID = P.CountryID), '') AS Country,
                                    (SELECT
                                                    COUNT(likeid)
                                    FROM Likes
                                    WHERE KeyID = P.PlaceId
                                    AND liketypeid = 48)
                                    AS totalLikes,
                                    ROW_NUMBER() OVER (ORDER BY P.PlaceId ASC) AS RowRank,
                                    '' AS mapaddress
    
                    FROM Places AS P
                    INNER JOIN dbo.PlaceCategory AS pc
                                    ON p.PlaceCategoryId = pc.PlaceCategoryId
                    LEFT OUTER JOIN PlacePermissions AS PP
                                    ON P.PlaceId = PP.PlaceId
                    WHERE P.status = 1
                    AND pc.status = 1
                    AND P.IsVerified = 1
                    AND (PlaceName LIKE '%' + @searchTxt + '%'
                    OR PlaceCategory LIKE '%' + @searchTxt + '%'
                    OR P.[Description] LIKE '%' + @searchTxt + '%')
    
                    AND (PC.PlaceCategoryId = @cateID
                    OR @cateID = 0)
    
                    AND [dbo].[PrivacyCheck](@Userid, P.PlaceId, 33) = 1
                    AND dbo.CheckPlacePermission(@userID, P.PlaceId) = 1
                    AND (P.IsSpecial = 1
                    OR @IsMuslimFaceSpecial = 0)
                    AND (P.UserId = @userID
                    OR @IsMyPlacesonly = 0)
                    AND (P.PlaceId IN (SELECT
                                    PlaceId
                    FROM PlaceVisited
                    WHERE UserID = @userID)
                    OR @IsUserHadBeenTo = 0)
                    AND (P.PlaceId IN ((SELECT
                                    PlaceId
                    FROM PlaceVisited pv
                    INNER JOIN userFriends uf
                                    ON pv.UserID = uf.UserID
                    WHERE uf.FriendID = @userID)
                    UNION
                    (SELECT
                                    PlaceId
                    FROM PlaceVisited pv
                    INNER JOIN userFriends uf
                                    ON pv.UserID = uf.FriendID
                    WHERE uf.UserID = @userID))
                    OR @IsUserFriendsHaveBeenTo = 0)
                    AND (P.PlaceId NOT IN (SELECT
                                    UserID
                    FROM PlaceVisited
                    WHERE UserID = @userID)
                    OR @IsUserHadNotBeenTo = 0))
    
                    SELECT
                                    *
                    FROM tbl
                    WHERE RowRank BETWEEN @RowIndex AND @EndRow
                    AND ([Address] LIKE '%' + @Location + '%'
                    OR City LIKE '%' + @Location + '%')
    
                    ORDER BY dbo.GetSearchPlaceCount(@userID, PlaceId) DESC, totalLikes DESC;
    
    END
ALTER PROCEDURE [dbo].[PlaceListSelect] @cateID int, @searchTxt varchar(50), @userID int, @RowIndex int, @MaxRows int, @Location varchar(50), @IsMuslimFaceSpecial int, @IsUserHadNotBeenTo int, @IsUserHadBeenTo int, @IsUserFriendsHaveBeenTo int, @IsMyPlacesonly int
AS
BEGIN
                DECLARE @EndRow AS int;
                SET @EndRow = @RowIndex + @MaxRows - 1;
                WITH tbl
                AS (SELECT
                                P.*,
                                (SELECT
                                                SUM(R.Rating) / COUNT(R.Rating)
                                FROM PlaceReview AS R
                                WHERE R.PlaceId = P.PlaceId
                                AND R.Rating != 0)
                                AS Rating,
                                pc.PlaceCategory,
                                ISNULL(PP.DefaultLandingTab, 0) AS DefaultLandingTab,
                                ISNULL(Cty.City, '') AS City,
                                ISNULL(Ctry.Country, '') AS Country,
                                (SELECT
                                                COUNT(likeid)
                                FROM Likes
                                WHERE KeyID = P.PlaceId
                                AND liketypeid = 48)
                                AS totalLikes,
                                ROW_NUMBER() OVER (ORDER BY P.PlaceId ASC) AS RowRank,
                                '' AS mapaddress
                FROM Places AS P
                INNER JOIN dbo.PlaceCategory AS pc
                                ON p.PlaceCategoryId = pc.PlaceCategoryId
                LEFT OUTER JOIN PlacePermissions AS PP
                                ON P.PlaceId = PP.PlaceId
                LEFT OUTER JOIN dbo.Cities AS Cty
                                ON P.CityId = Cty.CityId
                LEFT OUTER JOIN dbo.Countries Ctry
                                ON P.CountryID = Ctry.CountryID
                WHERE P.status = 1
                AND pc.status = 1
                AND P.IsVerified = 1
                AND (PlaceName LIKE '%' + @searchTxt + '%'
                OR PlaceCategory LIKE '%' + @searchTxt + '%'
                OR P.[Description] LIKE '%' + @searchTxt + '%'
                )
                AND ([Address] LIKE '%' + @Location + '%'
                OR City LIKE '%' + @Location + '%')
                AND (@cateID = 0
                OR PC.PlaceCategoryId = @cateID)
                AND [dbo].[PrivacyCheck](@Userid, P.PlaceId, 33) = 1
                AND dbo.CheckPlacePermission(@userID, P.PlaceId) = 1
                AND (@IsMuslimFaceSpecial = 0
                OR P.IsSpecial = 1)
                AND (@IsMyPlacesonly = 0
                OR P.UserId = @userID)
                AND (@IsUserHadBeenTo = 0
                OR P.PlaceId IN (SELECT
                                PlaceId
                FROM PlaceVisited
                WHERE UserID = @userID)
                )
                AND (@IsUserFriendsHaveBeenTo = 0
                OR P.PlaceId IN (SELECT
                                PlaceId
                FROM PlaceVisited AS pv
                INNER JOIN dbo.GetFriends(@userID)
                AS uf
                                ON pv.UserID = uf.UserID)
                )
                AND (@IsUserHadNotBeenTo = 0
                OR P.PlaceId NOT IN (SELECT
                                PlaceId
                FROM PlaceVisited
                WHERE UserID = @userID)
                ))
                SELECT
                                *
                FROM tbl
                WHERE RowRank BETWEEN @RowIndex AND @EndRow
                ORDER BY dbo.GetSearchPlaceCount(@userID, PlaceId) DESC, totalLikes DESC;
END

Is there any performance improvement if I change PC.PlaceCategoryId = @cateID OR @cateID = 0 to @cateID = 0 OR PC.PlaceCategoryId = @cateID

Execution plan can be downloaded from my OneDrive Account

ALTER PROCEDURE [dbo].[PlaceListSelect] @cateID int,
    @searchTxt varchar(50),
    @userID int,
    @RowIndex int,
    @MaxRows int,
    @Location varchar(50),
    @IsMuslimFaceSpecial int,
    @IsUserHadNotBeenTo int,
    @IsUserHadBeenTo int,
    @IsUserFriendsHaveBeenTo int,
    @IsMyPlacesonly int
    AS
    
    BEGIN
                    DECLARE @EndRow int
                    SET @EndRow = @RowIndex + @MaxRows - 1
                    ;
                    WITH tbl
                    AS (SELECT
                                    P.*,
                                    (SELECT
                                                    SUM(Rating) / COUNT(Rating)
                                    FROM PlaceReview AS R
                                    WHERE R.PlaceId = P.PlaceId)
                                    AS Rating,
                                    pc.PlaceCategory,
                                    ISNULL(PP.DefaultLandingTab, 0) AS DefaultLandingTab,
                                    ISNULL((SELECT
                                                    City
                                    FROM dbo.Cities
                                    WHERE CityId = P.CityId), '') AS City,
                                    ISNULL((SELECT
                                                    Country
                                    FROM dbo.Countries
                                    WHERE CountryID = P.CountryID), '') AS Country,
                                    (SELECT
                                                    COUNT(likeid)
                                    FROM Likes
                                    WHERE KeyID = P.PlaceId
                                    AND liketypeid = 48)
                                    AS totalLikes,
                                    ROW_NUMBER() OVER (ORDER BY P.PlaceId ASC) AS RowRank,
                                    '' AS mapaddress
    
                    FROM Places AS P
                    INNER JOIN dbo.PlaceCategory AS pc
                                    ON p.PlaceCategoryId = pc.PlaceCategoryId
                    LEFT OUTER JOIN PlacePermissions AS PP
                                    ON P.PlaceId = PP.PlaceId
                    WHERE P.status = 1
                    AND pc.status = 1
                    AND P.IsVerified = 1
                    AND (PlaceName LIKE '%' + @searchTxt + '%'
                    OR PlaceCategory LIKE '%' + @searchTxt + '%'
                    OR P.[Description] LIKE '%' + @searchTxt + '%')
    
                    AND (PC.PlaceCategoryId = @cateID
                    OR @cateID = 0)
    
                    AND [dbo].[PrivacyCheck](@Userid, P.PlaceId, 33) = 1
                    AND dbo.CheckPlacePermission(@userID, P.PlaceId) = 1
                    AND (P.IsSpecial = 1
                    OR @IsMuslimFaceSpecial = 0)
                    AND (P.UserId = @userID
                    OR @IsMyPlacesonly = 0)
                    AND (P.PlaceId IN (SELECT
                                    PlaceId
                    FROM PlaceVisited
                    WHERE UserID = @userID)
                    OR @IsUserHadBeenTo = 0)
                    AND (P.PlaceId IN ((SELECT
                                    PlaceId
                    FROM PlaceVisited pv
                    INNER JOIN userFriends uf
                                    ON pv.UserID = uf.UserID
                    WHERE uf.FriendID = @userID)
                    UNION
                    (SELECT
                                    PlaceId
                    FROM PlaceVisited pv
                    INNER JOIN userFriends uf
                                    ON pv.UserID = uf.FriendID
                    WHERE uf.UserID = @userID))
                    OR @IsUserFriendsHaveBeenTo = 0)
                    AND (P.PlaceId NOT IN (SELECT
                                    UserID
                    FROM PlaceVisited
                    WHERE UserID = @userID)
                    OR @IsUserHadNotBeenTo = 0))
    
                    SELECT
                                    *
                    FROM tbl
                    WHERE RowRank BETWEEN @RowIndex AND @EndRow
                    AND ([Address] LIKE '%' + @Location + '%'
                    OR City LIKE '%' + @Location + '%')
    
                    ORDER BY dbo.GetSearchPlaceCount(@userID, PlaceId) DESC, totalLikes DESC;
    
    END

Is there any performance improvement if I change PC.PlaceCategoryId = @cateID OR @cateID = 0 to @cateID = 0 OR PC.PlaceCategoryId = @cateID

ALTER PROCEDURE [dbo].[PlaceListSelect] @cateID int, @searchTxt varchar(50), @userID int, @RowIndex int, @MaxRows int, @Location varchar(50), @IsMuslimFaceSpecial int, @IsUserHadNotBeenTo int, @IsUserHadBeenTo int, @IsUserFriendsHaveBeenTo int, @IsMyPlacesonly int
AS
BEGIN
                DECLARE @EndRow AS int;
                SET @EndRow = @RowIndex + @MaxRows - 1;
                WITH tbl
                AS (SELECT
                                P.*,
                                (SELECT
                                                SUM(R.Rating) / COUNT(R.Rating)
                                FROM PlaceReview AS R
                                WHERE R.PlaceId = P.PlaceId
                                AND R.Rating != 0)
                                AS Rating,
                                pc.PlaceCategory,
                                ISNULL(PP.DefaultLandingTab, 0) AS DefaultLandingTab,
                                ISNULL(Cty.City, '') AS City,
                                ISNULL(Ctry.Country, '') AS Country,
                                (SELECT
                                                COUNT(likeid)
                                FROM Likes
                                WHERE KeyID = P.PlaceId
                                AND liketypeid = 48)
                                AS totalLikes,
                                ROW_NUMBER() OVER (ORDER BY P.PlaceId ASC) AS RowRank,
                                '' AS mapaddress
                FROM Places AS P
                INNER JOIN dbo.PlaceCategory AS pc
                                ON p.PlaceCategoryId = pc.PlaceCategoryId
                LEFT OUTER JOIN PlacePermissions AS PP
                                ON P.PlaceId = PP.PlaceId
                LEFT OUTER JOIN dbo.Cities AS Cty
                                ON P.CityId = Cty.CityId
                LEFT OUTER JOIN dbo.Countries Ctry
                                ON P.CountryID = Ctry.CountryID
                WHERE P.status = 1
                AND pc.status = 1
                AND P.IsVerified = 1
                AND (PlaceName LIKE '%' + @searchTxt + '%'
                OR PlaceCategory LIKE '%' + @searchTxt + '%'
                OR P.[Description] LIKE '%' + @searchTxt + '%'
                )
                AND ([Address] LIKE '%' + @Location + '%'
                OR City LIKE '%' + @Location + '%')
                AND (@cateID = 0
                OR PC.PlaceCategoryId = @cateID)
                AND [dbo].[PrivacyCheck](@Userid, P.PlaceId, 33) = 1
                AND dbo.CheckPlacePermission(@userID, P.PlaceId) = 1
                AND (@IsMuslimFaceSpecial = 0
                OR P.IsSpecial = 1)
                AND (@IsMyPlacesonly = 0
                OR P.UserId = @userID)
                AND (@IsUserHadBeenTo = 0
                OR P.PlaceId IN (SELECT
                                PlaceId
                FROM PlaceVisited
                WHERE UserID = @userID)
                )
                AND (@IsUserFriendsHaveBeenTo = 0
                OR P.PlaceId IN (SELECT
                                PlaceId
                FROM PlaceVisited AS pv
                INNER JOIN dbo.GetFriends(@userID)
                AS uf
                                ON pv.UserID = uf.UserID)
                )
                AND (@IsUserHadNotBeenTo = 0
                OR P.PlaceId NOT IN (SELECT
                                PlaceId
                FROM PlaceVisited
                WHERE UserID = @userID)
                ))
                SELECT
                                *
                FROM tbl
                WHERE RowRank BETWEEN @RowIndex AND @EndRow
                ORDER BY dbo.GetSearchPlaceCount(@userID, PlaceId) DESC, totalLikes DESC;
END

Is there any performance improvement if I change PC.PlaceCategoryId = @cateID OR @cateID = 0 to @cateID = 0 OR PC.PlaceCategoryId = @cateID

Execution plan can be downloaded from my OneDrive Account

added 149 characters in body
Source Link

Is there any performance improvement if I change PC.PlaceCategoryId = @cateID OR @cateID = 0 to @cateID = 0 OR PC.PlaceCategoryId = @cateID

Is there any performance improvement if I change PC.PlaceCategoryId = @cateID OR @cateID = 0 to @cateID = 0 OR PC.PlaceCategoryId = @cateID

Source Link
Loading