Query on SQL Server stored procedure takes longer for specific data than for all data

Question

I have a query in a stored procedure that is retrieving data from several tables of my SQL Server database. From my application I call this stored procedure with some parameters to filter the data.

The strange thing is that, for all data returned for "X" week of year (about 2600 records) it takes about 30 seconds to retrieve the data. But if I add a specific "center" filter for the same week of year (about 1800 records), it takes about 3 minutes to get the data!

If I run just the query, it works well (30 seconds for all data and about 22 seconds for filtered data). The issue is when I run the query through the stored procedure!

How is this possible? Why does filtered data is taking about x6 times than getting all data? Why am I missing? Am I writing the stored procedure correctly? How can I do this in a more efficient way?

My stored procedure code is something like this:

ALTER PROCEDURE EventMonitoring
    @EventType AS CHAR(1),
    @Year AS INT,
    @Week AS INT,
    @CenterID AS CHAR(2),
    @AreaID AS INT
AS
    DECLARE @SQLCommand AS VARCHAR(MAX)
    DECLARE @MessageError AS VARCHAR(MAX)

    SET @SQLCommand = ' SELECT ....
                   ....
                   ....
                   ....
                   FROM   Event E
                   INNER JOIN ...
                   INNER JOIN ...
                   INNER JOIN ... 
                   WHERE E.EventYear = ' + CAST(@Year AS VARCHAR) +  
                   ' And E.EventWeek = ' + CAST(@Week AS VARCHAR) +
                   ' And E.EventType = ' + CAST(@EventType AS VARCHAR) 

    IF @Centro <> '-' --If application sends - as the parameter, it gets all centers
    BEGIN
        @SQLCommand = @SQLCommand + ' AND E.CenterID = ''' + @CenterID + '''' 
    END

    IF @Area <> 0 --If application sends 0 as the parameter, it gets all  areas
    BEGIN
        @SQLCommand = @SQLCommand + ' AND E.AreaID = ' + CAST(@AreaID AS VARCHAR) 
    END

    SET @SQLCommand = @SQLCommand + 'GROUP BY ....'

    BEGIN TRY
        EXEC(@SQLCommand)
    END TRY
    BEGIN CATCH
        ....
    END CATCH

Answer 1

No matter how this shakes out, I bet this can be solved by adding an additional column (CenterId) to an index.

When the difference comes down to 'when I add an additional column to the where clause, the query takes a whole lot longer', that indicates that the additional column is not covered by the index the query is able to use without that clause.

To fix that, find the existing index that query uses without the additional clause and add the additional column to the include() of that index, or make a new index (seems wasteful to make a whole new index to add one more int column though).

Find the index that is being used for Event with the fast query, and see if it includes CenterId. If you do not know how to find it, you basically need to check the execution plans. If you still can not find it, you can share your execution plans and we will help you find it. Share your execution plans using Paste The Plan @ brentozar.com here are the instructions: How to Use Paste the Plan.

I can only guess (vaguely) what the index is going to look like without knowing more about how Event joins to the rest of your query, but the index might look something like this, only missing CenterId:

create nonclustered index ix_Event_cover 
  on dbo.Event(EventYear,EventWeek,EventType)
    include (CenterId, AreaID, [JoinColumns], [SelectedColumns])

Answer 2

I faced a situation like this at my work and we solve just adding the SET ARITHABORT ON parameter inside the proc because the ADO connections.

EDIT

When a procedure generates the script in runtime sql engine don't see the real parameters to build a good plan for the query. It's call Parameter Sniffing. So try something like this:

ALTER PROCEDURE EventMonitoring
    @EventType AS CHAR(1),
    @Year AS INT,
    @Week AS INT,
    @CenterID AS CHAR(2),
    @AreaID AS INT
AS   
    DECLARE @MessageError AS VARCHAR(MAX)

    BEGIN TRY

    IF @Centro <> '-' --If application sends - as the parameter, it gets all centers
    BEGIN
        SELECT ....
                   ....
                   ....
                   ....
                   FROM   Event E
                   INNER JOIN ...
                   INNER JOIN ...
                   INNER JOIN ... 
                   WHERE E.EventYear = @Year 
                    And E.EventWeek = @Week  
                   And E.EventType =EventType  AND E.CenterID = @CenterID 
                   GROUP BY ....
    END

    IF @Area <> 0 --If application sends 0 as the parameter, it gets all  areas
    BEGIN

        SELECT ....
                   ....
                   ....
                   ....
                   FROM   Event E
                   INNER JOIN ...
                   INNER JOIN ...
                   INNER JOIN ... 
                   WHERE E.EventYear = @Year 
                    And E.EventWeek = @Week  
                   And E.EventType =EventType  AND E.AreaID = @AreaID 
                   GROUP BY ....
    END





    END TRY
    BEGIN CATCH
        ....
    END CATCH

Another try

After a quickly read in the article pointed by SqlZim try this last one shot with ARITHABORT ON and option(recompile)

    ALTER PROCEDURE EventMonitoring
        @EventType AS CHAR(1),
        @Year AS INT,
        @Week AS INT,
        @CenterID AS CHAR(2),
        @AreaID AS INT
    AS
SET ARITHABORT ON
        DECLARE @SQLCommand AS VARCHAR(MAX)
        DECLARE @MessageError AS VARCHAR(MAX)

        SET @SQLCommand = ' SELECT ....
                       ....
                       ....
                       ....
                       FROM   Event E
                       INNER JOIN ...
                       INNER JOIN ...
                       INNER JOIN ... 
                       WHERE E.EventYear = ' + CAST(@Year AS VARCHAR) +  
                       ' And E.EventWeek = ' + CAST(@Week AS VARCHAR) +
                       ' And E.EventType = ' + CAST(@EventType AS VARCHAR) 

        IF @Centro <> '-' --If application sends - as the parameter, it gets all centers
        BEGIN
            @SQLCommand = @SQLCommand + ' AND E.CenterID = ''' + @CenterID + '''' 
        END

        IF @Area <> 0 --If application sends 0 as the parameter, it gets all  areas
        BEGIN
            @SQLCommand = @SQLCommand + ' AND E.AreaID = ' + CAST(@AreaID AS VARCHAR) 
        END

        SET @SQLCommand = @SQLCommand + 'GROUP BY .... OPTION (RECOMPILE)'

        BEGIN TRY
            EXEC(@SQLCommand)
        END TRY
        BEGIN CATCH
            ....
        END CATCH