Skip to main content
3 of 4
deleted 638 characters in body

How can I modify this query for performance?

This is the stored procedure I'm using to list some places in my website. How can I optimize it to perform well? Which functions in query is taking much time for its execution?

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