Stored procedure optimization
- by George Zacharia
Hi, i have a stored procedure which takes lot of time to execure .Can any one suggest a better approch so that the same result set is achived.
ALTER PROCEDURE [dbo].[spFavoriteRecipesGET]
@USERID INT, @PAGENUMBER INT, @PAGESIZE INT, @SORTDIRECTION VARCHAR(4), @SORTORDER VARCHAR(4),@FILTERBY INT
AS
BEGIN
DECLARE
@ROW_START INT
DECLARE
@ROW_END INT
SET
@ROW_START = (@PageNumber-1)* @PageSize+1
SET
@ROW_END = @PageNumber*@PageSize
DECLARE
@RecipeCount INT
DECLARE
@RESULT_SET_TABLE
TABLE
(
Id INT NOT NULL IDENTITY(1,1),
FavoriteRecipeId INT,
RecipeId INT,
DateAdded DATETIME,
Title NVARCHAR(255),
UrlFriendlyTitle NVARCHAR(250),
[Description] NVARCHAR(MAX),
AverageRatingId FLOAT,
SubmittedById INT,
SubmittedBy VARCHAR(250),
RecipeStateId INT,
RecipeRatingId INT,
ReviewCount INT,
TweaksCount INT,
PhotoCount INT,
ImageName NVARCHAR(50)
)
INSERT INTO @RESULT_SET_TABLE
SELECT
FavoriteRecipes.FavoriteRecipeId,
Recipes.RecipeId,
FavoriteRecipes.DateAdded,
Recipes.Title,
Recipes.UrlFriendlyTitle,
Recipes.[Description],
Recipes.AverageRatingId,
Recipes.SubmittedById,
COALESCE(users.DisplayName,users.UserName,Recipes.SubmittedBy) As SubmittedBy,
Recipes.RecipeStateId,
RecipeReviews.RecipeRatingId,
COUNT(RecipeReviews.Review),
COUNT(RecipeTweaks.Tweak),
COUNT(Photos.PhotoId),
dbo.udfGetRecipePhoto(Recipes.RecipeId) AS ImageName
FROM
FavoriteRecipes
INNER JOIN Recipes ON FavoriteRecipes.RecipeId=Recipes.RecipeId AND Recipes.RecipeStateId <> 3
LEFT OUTER JOIN RecipeReviews ON RecipeReviews.RecipeId=Recipes.RecipeId AND RecipeReviews.ReviewedById=@UserId
AND RecipeReviews.RecipeRatingId= (
SELECT MAX(RecipeReviews.RecipeRatingId)
FROM RecipeReviews
WHERE RecipeReviews.ReviewedById=@UserId
AND RecipeReviews.RecipeId=FavoriteRecipes.RecipeId
)
OR RecipeReviews.RecipeRatingId IS NULL
LEFT OUTER JOIN RecipeTweaks ON RecipeTweaks.RecipeId = Recipes.RecipeId AND RecipeTweaks.TweakedById= @UserId
LEFT OUTER JOIN Photos ON Photos.RecipeId = Recipes.RecipeId
AND Photos.UploadedById = @UserId AND Photos.RecipeId = FavoriteRecipes.RecipeId
AND Photos.PhotoTypeId = 1
LEFT OUTER JOIN users ON Recipes.SubmittedById = users.UserId
WHERE
FavoriteRecipes.UserId=@UserId
GROUP BY
FavoriteRecipes.FavoriteRecipeId,
Recipes.RecipeId,
FavoriteRecipes.DateAdded,
Recipes.Title,
Recipes.UrlFriendlyTitle,
Recipes.[Description],
Recipes.AverageRatingId,
Recipes.SubmittedById,
Recipes.SubmittedBy,
Recipes.RecipeStateId,
RecipeReviews.RecipeRatingId,
users.DisplayName,
users.UserName,
Recipes.SubmittedBy;
WITH SortResults
AS
(
SELECT
ROW_NUMBER() OVER (
ORDER BY CASE WHEN @SORTDIRECTION = 't' AND @SORTORDER='a' THEN TITLE END ASC,
CASE WHEN @SORTDIRECTION = 't' AND @SORTORDER='d' THEN TITLE END DESC,
CASE WHEN @SORTDIRECTION = 'r' AND @SORTORDER='a' THEN AverageRatingId END ASC,
CASE WHEN @SORTDIRECTION = 'r' AND @SORTORDER='d' THEN AverageRatingId END DESC,
CASE WHEN @SORTDIRECTION = 'mr' AND @SORTORDER='a' THEN RecipeRatingId END ASC,
CASE WHEN @SORTDIRECTION = 'mr' AND @SORTORDER='d' THEN RecipeRatingId END DESC,
CASE WHEN @SORTDIRECTION = 'd' AND @SORTORDER='a' THEN DateAdded END ASC,
CASE WHEN @SORTDIRECTION = 'd' AND @SORTORDER='d' THEN DateAdded END DESC
) RowNumber,
FavoriteRecipeId,
RecipeId,
DateAdded,
Title,
UrlFriendlyTitle,
[Description],
AverageRatingId,
SubmittedById,
SubmittedBy,
RecipeStateId,
RecipeRatingId,
ReviewCount,
TweaksCount,
PhotoCount,
ImageName
FROM
@RESULT_SET_TABLE
WHERE
((@FILTERBY = 1 AND SubmittedById= @USERID)
OR ( @FILTERBY = 2 AND (SubmittedById <> @USERID OR SubmittedById IS NULL))
OR ( @FILTERBY <> 1 AND @FILTERBY <> 2))
)
SELECT
RowNumber,
FavoriteRecipeId,
RecipeId,
DateAdded,
Title,
UrlFriendlyTitle,
[Description],
AverageRatingId,
SubmittedById,
SubmittedBy,
RecipeStateId,
RecipeRatingId,
ReviewCount,
TweaksCount,
PhotoCount,
ImageName
FROM
SortResults
WHERE
RowNumber BETWEEN @ROW_START AND @ROW_END
print @ROW_START
print @ROW_END
SELECT
@RecipeCount=dbo.udfGetFavRecipesCount(@UserId)
SELECT
@RecipeCount AS RecipeCount
SELECT COUNT(Id) as FilterCount FROM @RESULT_SET_TABLE
WHERE
((@FILTERBY = 1 AND SubmittedById= @USERID)
OR (@FILTERBY = 2 AND (SubmittedById <> @USERID OR SubmittedById IS NULL))
OR (@FILTERBY <> 1 AND @FILTERBY <> 2))
END
