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