0
\$\begingroup\$

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

\$\endgroup\$
  • \$\begingroup\$ Might you get better answers (about performance) if you added the query plan to your questions, and add the DDL which shows what the database indexes are? \$\endgroup\$ – ChrisW Mar 27 '14 at 10:09
  • 2
    \$\begingroup\$ For a real answer, provide DDL, sample data (INSERT statements), sample queries and actual execution plans (not estimated). On a quick glance, LIKE with a starting % is nonsargable (can't use indexes) for obvious reasons - do you really need that leading %? Try putting WHERE clause conditions relating solely to one JOIN table into the JOIN clauses (i.e. P.IsVerified = 1). Don't use scalar functions in your JOIN or WHERE clauses, performance is awful (PrivacyCheck, CheckPlacePermission, etc.) even before we consider sargability or the lack thereof. And use parameters, not concatenation :). \$\endgroup\$ – Anti-weakpasswords Mar 28 '14 at 4:20
  • \$\begingroup\$ @Anti-weakpasswords I didn't understand this Try putting WHERE clause conditions relating solely to one JOIN table into the JOIN clauses (i.e. P.IsVerified = 1) \$\endgroup\$ – Subin Jacob Mar 28 '14 at 5:08
  • \$\begingroup\$ @Anti-weakpasswords Actula execution pla attached \$\endgroup\$ – Subin Jacob Mar 28 '14 at 5:21
  • 2
    \$\begingroup\$ @SubinJacob: Instead of SELECT Col1 FROM TblA INNER JOIN TblB ON TblA.Col1 = TblB.Col1 WHERE TblB.Col2 = 1, try SELECT Col1 FROM TblA INNER JOIN TblB ON TblA.Col1 = TblB.Col1 AND TblB.Col2 = 1 \$\endgroup\$ – Anti-weakpasswords Mar 28 '14 at 5:38
2
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ By adding view can I improve performance? It just improve the code readability I suppose? Will SqlServer Cache results if views were used? \$\endgroup\$ – Subin Jacob Mar 28 '14 at 10:39
  • \$\begingroup\$ Also can i use computed columns for like count? likes are defined in another table and msdn documentation says A computed column is computed from an expression that can use other columns in the same table. \$\endgroup\$ – Subin Jacob Mar 28 '14 at 10:47
  • \$\begingroup\$ Also this quote confusing me Views create the appearance of a table, but the DBMS must still translate queries against the view into queries against the underlying source tables. If the view is defined by a complex, multi-table query then simple queries on the views may take considerable time. See this link c-sharpcorner.com/Blogs/10575/… \$\endgroup\$ – Subin Jacob Mar 28 '14 at 10:54
  • \$\begingroup\$ Mainly readability, but by adding all the checks (verified, status, permissions, etc) I think the engine can make a better query and possible decrease the resultset, that would improve performance. It will not cache the view. You can try to make a materialized view (see stackoverflow.com/questions/3986366/…) if you want it cached \$\endgroup\$ – Leopoldo Salvo Mar 28 '14 at 11:01
  • \$\begingroup\$ You can use a User Defined function to get values from another table, or even better an indexed view, see here: stackoverflow.com/questions/13488822/… \$\endgroup\$ – Leopoldo Salvo Mar 28 '14 at 11:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.