locked
Query improvement RRS feed

  • Question

  • We have come across a client app that is having timeout problems with a stored procedure.  Below is the stored procedure and below it are the table schemas.  I have a feeling the timeouts are a result of having a subselect inside the WHERE clause.  Any help would be appreciated. The problem occurs when @SearchText is sent with a value.

    ALTER PROCEDURE [dbo].[kd_selActualMealsClient]
    	@PatientID		int,
    	@MealID			int = 0,
    	@VerifiedOnly	bit = 0,
    	@SearchText		varchar(200) = null,
    	@SortAlpha		bit = 0
    
    AS
    	BEGIN
    		-- SET NOCOUNT ON added to prevent extra result sets from
    		-- interfering with SELECT statements.
    		SET NOCOUNT ON;
    
    		IF @SearchText IS NOT NULL
    			BEGIN
    				SET @SearchText = '%' + @SearchText + '%';
    			END
    
    SELECT M.MealID
    		,M.MealNumber
    		,M.MealName
    		,M.MealDate
    		,M.Instructions
    		,M.Verified
    		,M.Caregiver
    		,CAST(M.MealNumber As varchar(8)) + ' - ' + M.MealName AS MealDisplay
    	FROM dbo.tblActualMeals AS M
    	WHERE (M.PatientID = @PatientID)
    	AND (CASE WHEN @MealID = 0 THEN 'T'
    				WHEN @MealID > 0 AND M.MealID = @MealID THEN 'T'
    				ELSE 'F'
    				END = 'T')
    	AND (CASE WHEN @VerifiedOnly = 0 THEN 'T'
    				WHEN @VerifiedOnly = 1 AND M.Verified = 1 THEN 'T'
    				ELSE 'F'
    				END = 'T')
    	AND (CASE WHEN @SearchText IS NULL THEN 'T'
    				WHEN @SearchText IS NOT NULL 
    				AND (M.MealName LIKE @SearchText 
    				OR M.MealID IN(SELECT A.MealID
    									FROM dbo.tblActualFoods AS A INNER JOIN
    										dbo.tblFoods AS F ON A.FoodID = F.FoodID
    								WHERE (A.MealID = M.MealID)
    									AND (F.FoodItem LIKE @SearchText))) THEN 'T'
    				ELSE 'F'
    				END = 'T')
    ORDER BY M.MealNumber;
    
    
    
    --Table schemas below
    
    CREATE TABLE [dbo].[tblActualMeals](
    	[MealID] [int] IDENTITY(1,1) NOT NULL,
    	[PatientID] [int] NULL,
    	[MealName] [nvarchar](75) NULL,
    	[MealDate] [smalldatetime] NULL,
    	[Instructions] [nvarchar](4000) NULL,
    	[Verified] [bit] NOT NULL,
    	[MealNumber] [int] NULL,
    	[Caregiver] [bit] NOT NULL,
     CONSTRAINT [PK_tblActualMeals] PRIMARY KEY NONCLUSTERED 
    (
    	[MealID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]
    
    
    CREATE TABLE [dbo].[tblActualFoods](
    	[MealID] [int] NOT NULL,
    	[FoodID] [int] NOT NULL,
    	[ActGrams] [real] NOT NULL,
    	[Picked] [bit] NOT NULL,
     CONSTRAINT [PK_tblActualFoods] PRIMARY KEY CLUSTERED 
    (
    	[MealID] ASC,
    	[FoodID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]
    
    
    CREATE TABLE [dbo].[tblFoods](
    	[FoodID] [int] IDENTITY(1,1) NOT NULL,
    	[CategoryID] [int] NULL,
    	[FoodItem] [nvarchar](55) NULL,
    	[FoodGrams] [real] NULL,
    	[FoodPro] [real] NULL,
    	[FoodFat] [real] NULL,
    	[FoodCarb] [real] NULL,
    	[Liquid] [bit] NOT NULL,
    	[OrgID] [int] NOT NULL,
    	[LastUpdated] [smalldatetime] NULL,
    	[IVCarb] [real] NOT NULL,
    	[Fiber] [real] NOT NULL,
    	[FiberUnknown] [bit] NOT NULL,
     CONSTRAINT [PK_tblFoods] PRIMARY KEY NONCLUSTERED 
    (
    	[FoodID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]
    
    


    Tuesday, December 17, 2019 3:47 PM

Answers

  • ALTER PROCEDURE [dbo].[kd_selActualMealsClient]
    	@PatientID		int,
    	@MealID			int = 0,
    	@VerifiedOnly	bit = 0,
    	@SearchText		varchar(200) = null,
    	@SortAlpha		bit = 0
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    	IF @SearchText IS NOT NULL
    	BEGIN
    		SET @SearchText = '%' + @SearchText + '%';
    	END
    
    	DECLARE @MealIDs TABLE (
    		MealID int
    	);
    	INSERT INTO @MealIDs
    	SELECT A.MealID
    	FROM dbo.tblActualMeals AS M
    	INNER JOIN dbo.tblActualFoods AS A ON M.MealID = A.MealID
    	INNER JOIN dbo.tblFoods AS F ON A.FoodID = F.FoodID
    	WHERE F.FoodItem LIKE (@SearchText);	-- May need an index on the column dbo.tblFoods.FoodItem
    
    	SELECT M.MealID
    		,M.MealNumber
    		,M.MealName
    		,M.MealDate
    		,M.Instructions
    		,M.Verified
    		,M.Caregiver
    		,CAST(M.MealNumber As varchar(8)) + ' - ' + M.MealName AS MealDisplay
    	FROM dbo.tblActualMeals AS M
    	WHERE (M.PatientID = @PatientID)
    	AND (
    		CASE 
    			WHEN @MealID = 0 THEN 'T'
    			WHEN @MealID > 0 AND M.MealID = @MealID THEN 'T'
    			ELSE 'F'
    		END = 'T'
    	)
    	AND (
    		CASE 
    			WHEN @VerifiedOnly = 0 THEN 'T'
    			WHEN @VerifiedOnly = 1 AND M.Verified = 1 THEN 'T'
    			ELSE 'F'
    		END = 'T'
    	)
    	AND (
    		CASE 
    			WHEN @SearchText IS NULL THEN 'T'
    			WHEN @SearchText IS NOT NULL AND (M.MealName LIKE (@SearchText) OR M.MealID IN (SELECT MealID FROM @MealIDs)) THEN 'T'
    			ELSE 'F'
    		END = 'T'
    	)
    	ORDER BY M.MealNumber;
    END


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by David Chase89 Tuesday, December 17, 2019 6:06 PM
    Tuesday, December 17, 2019 4:17 PM

All replies

  • ALTER PROCEDURE [dbo].[kd_selActualMealsClient]
    	@PatientID		int,
    	@MealID			int = 0,
    	@VerifiedOnly	bit = 0,
    	@SearchText		varchar(200) = null,
    	@SortAlpha		bit = 0
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    	IF @SearchText IS NOT NULL
    	BEGIN
    		SET @SearchText = '%' + @SearchText + '%';
    	END
    
    	DECLARE @MealIDs TABLE (
    		MealID int
    	);
    	INSERT INTO @MealIDs
    	SELECT A.MealID
    	FROM dbo.tblActualMeals AS M
    	INNER JOIN dbo.tblActualFoods AS A ON M.MealID = A.MealID
    	INNER JOIN dbo.tblFoods AS F ON A.FoodID = F.FoodID
    	WHERE F.FoodItem LIKE (@SearchText);	-- May need an index on the column dbo.tblFoods.FoodItem
    
    	SELECT M.MealID
    		,M.MealNumber
    		,M.MealName
    		,M.MealDate
    		,M.Instructions
    		,M.Verified
    		,M.Caregiver
    		,CAST(M.MealNumber As varchar(8)) + ' - ' + M.MealName AS MealDisplay
    	FROM dbo.tblActualMeals AS M
    	WHERE (M.PatientID = @PatientID)
    	AND (
    		CASE 
    			WHEN @MealID = 0 THEN 'T'
    			WHEN @MealID > 0 AND M.MealID = @MealID THEN 'T'
    			ELSE 'F'
    		END = 'T'
    	)
    	AND (
    		CASE 
    			WHEN @VerifiedOnly = 0 THEN 'T'
    			WHEN @VerifiedOnly = 1 AND M.Verified = 1 THEN 'T'
    			ELSE 'F'
    		END = 'T'
    	)
    	AND (
    		CASE 
    			WHEN @SearchText IS NULL THEN 'T'
    			WHEN @SearchText IS NOT NULL AND (M.MealName LIKE (@SearchText) OR M.MealID IN (SELECT MealID FROM @MealIDs)) THEN 'T'
    			ELSE 'F'
    		END = 'T'
    	)
    	ORDER BY M.MealNumber;
    END


    A Fan of SSIS, SSRS and SSAS

    • Marked as answer by David Chase89 Tuesday, December 17, 2019 6:06 PM
    Tuesday, December 17, 2019 4:17 PM
  • Maybe you can replace the conditions with these:

    . . .

    AND (@MealID = 0 OR M.MealID = @MealID )

    AND (@VerifiedOnly = 0 OR M.Verified = 1)

    AND (@SearchText IS NULL OR M.MealName LIKE @SearchText

       OR EXISTS (SELECT * FROM dbo.tblActualFoods AS A

                            INNER JOIN dbo.tblFoods AS F ON A.FoodID = F.FoodID

                            WHERE A.MealID = M.MealID AND F.FoodItem LIKE @SearchText ) )

     

    Also consider adding ‘OPTION (RECOMPILE)’ to this query.

    To avoid additional conversions, make sure that @SearchText, F.FoodItem and M.MealName are all Unicode (e.g. ‘nvarchar’) or non-Unicode (e.g. ‘varchar’). It seems that you should change the type of @SearchText to nvarchar(200).

    • Edited by Viorel_MVP Tuesday, December 17, 2019 5:43 PM
    Tuesday, December 17, 2019 5:33 PM
  • Try adding this  at the end of the query:

    OPTION (RECOMPILE)

    This will permit the optimizer to handle the variables as constants which permits for a much more aggresive optimization.

    Keep in mind that you need to move the semicolon at the end of the ORDER BY clause. That is:

    ORDER BY M.MealNumber
    OPTION (RECOMPILE);


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, December 17, 2019 11:08 PM