Query Runs Fast, Stored Procedure Slow - Multiple things tried

Question

I have a stored procedure on SQL Server 2014 and when running the query directly with the params hard coded, it runs instantly. When its run through the stored procedure it takes a long time or times out.

It appears the parameters in the select query where its getting the top 1

  , ISNULL((SELECT TOP 1 block_score FROM block_states BS WHERE BS.block_id = defined_blocks.id AND BS.user_name = @local_userName AND BS.orgId = @local_orgId AND BS.assessment_key = 0 ), 'NONE' )

that is causing the problems as I have hardcoded these params in the stored procedure and it runs almost instantly.

I have read a lot about parameter sniffing and as suggested in Query runs fast, but runs slow in stored procedure and have tried a few things; I have made the parameters local ones I have also tried adding the

OPTION(RECOMPILE)

and tried running

exec sp_updatestats

but all appeared to make either no or very little difference.

The stored procedure is as follows:

     @userName NVARCHAR(100), @orgId NVARCHAR(100),@ chartId INT
AS



    DECLARE @definedchartBlocks TABLE(
        chartId INT,
        sectId BIGINT,
        subsectId BIGINT,
        blockId BIGINT,
        blockScore NVARCHAR(10)

    )
        Declare  @local_userName NVARCHAR(100), @local_orgId NVARCHAR(100), @local_chartId INT
        select @local_userName=@userName, @local_orgId=@orgId,@local_chartId=@chartId /*attempt to speed up stp*/


    INSERT INTO @definedchartBlocks

    SELECT      
        defined_sects.chart_id
        , defined_subsects.sect_id
        , defined_blocks.subsect_id
        , defined_blocks.id AS blockId

        , ISNULL((SELECT TOP 1 block_score FROM block_states BS WHERE BS.block_id = defined_blocks.id AND BS.user_name = @local_userName AND BS.orgId = @local_orgId AND BS.assessment_key = 0 ), 'NONE' )
    FROM         
        defined_subsects 
        INNER JOIN
        defined_sects ON defined_subsects.sect_id = defined_sects.id 
        INNER JOIN
        defined_blocks ON defined_subsects.id = defined_blocks.subsect_id
    WHERE     
        (defined_sects.chart_id = @local_chartId)

OPTION(RECOMPILE)

    IF EXISTS (
    SELECT      
            MAX(definedchartBlocks.blockScore)
    FROM         
        @definedchartBlocks definedchartBlocks
    WHERE
        definedchartBlocks.blockScore = 'AMBER' OR definedchartBlocks.blockScore = 'RED' OR definedchartBlocks.blockScore = 'NONE' OR definedchartBlocks.blockScore = '' OR definedchartBlocks.blockScore IS NULL

    GROUP BY 
        definedchartBlocks.blockScore
    )
    BEGIN

        SELECT 0 AS chartCompleted
    END
    ELSE
    BEGIN
        SELECT 1 AS chartCompleted
    END

Are the `username` and `orgid` columns `NVARCHAR (100)` datatypes? — Siyual, Dec 13 '16 at 16:23
@Siyual - Actually, no they aren't they are varchar(50). I didn't think to check that, this is an inherited stored procedure. I will match them and then try it. — Bex, Dec 13 '16 at 16:25
@Siyual That Worked! Thankyou!! Put that as an answer so I can give you the credit you deserve! — Bex, Dec 13 '16 at 16:28

score 4 · Accepted Answer · answered Dec 13 '16 at 16:33

SQL Server does do a good job for the most part with implicit casts, but there are times that it can bog a query down. You mentioned that hard-coding the values returns instantly, but using a variable does not.

When using a variable, make sure that the datatype matches the column's datatype. I suspect that the issue is that your variables are NVARCHAR (100) whilst your columns are VARCHAR (50).

Another way to see if this might be an issue is by looking at the query plan - in such a case, you might see something similar to the following as a warning:

Type conversion in expression (CONVERT_IMPLICIT(...)) may affect "SeekPlan" in query plan choice

Query Runs Fast, Stored Procedure Slow - Multiple things tried

1 Answers1