A search for reviews of places

Question

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

Answer 1

It is hard to give an accurate answer without seeing all the code (there are a few functions like GetSearchPlaceCount, and GetFriends, [PrivacyCheck], CheckPlacePermission ...), and without knowing what the indexes are.

Things that i have noticed:

1. In the first block, the average rating calculation and the number of likes calculation can be avoided altogether if you make a couple of computed columnns in the Places table (one for average rating and one for number of likes).

2. This join seems redundant to me (I think can just remove it):

   LEFT OUTER JOIN PlacePermissions AS PP
        ON P.PlaceId = PP.PlaceId
   

3. I notice in the like's you don't check if @searchTxt and @Location are empty, if those variables can indeed be empty, it would be good if you could spare the engine having to scan for like '%%'

4. Everywhere I look in the Stored proc, I see a relation between (UserId and a PlaceId), and everytime this relation is handled with a different query:

   • (1x) ... AND [dbo].[PrivacyCheck](@Userid, P.PlaceId, 33) = 1 ...
   • (1x) ... LEFT OUTER JOIN PlacePermissions AS PP ON P.PlaceId = PP.PlaceId ...
   • (1x) ... AND (@IsMyPlacesonly = 0 OR P.UserId = @userID) ...
   • (1x) ... AND dbo.CheckPlacePermission(@userID, P.PlaceId) ...
   • (1x) ... dbo.GetSearchPlaceCount(@userID, PlaceId) ...
   • (3x) ... P.PlaceId IN (SELECT PlaceId FROM PlaceVisited WHERE UserID = @userID ...

I have the feeling that you can create a temporary table (or a view) 'AvailablePlaces' with only the places which passes all the filters (including permissions and privacy check, including all the conditional statemens such as @IsUserHadNotBeenTo, @IsUserHadBeenTo, status=1, isVerified=1, PC.PlaceCategoryId = @cateID, etc), and then use this view just once instead of 6 times with conditional left joins

As an added bonus by grouping all the places into one object, you can make sure in one place that all the filters are applied consistently (privacy, permissions, isVerified, status etc: in the clauses in the bottom (PlaceVisited) that is taken for granted somehow). All these filters would also help the db engine to use the existing indexes better.

then you could replace

 FROM Places AS P

with

 FROM AvailablePlaces AS P 

And you would then be able to remove most of those Where clauses, the likes would remain though

Then you should try to see if you can replace the functions (specially dbo.GetSearchPlaceCount) with a query based on availablePlaces

And finally, as a general note, you need of course to look at your indexes to maximize the performance.