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