CODEWITHSUNDEEP
sql-serverperformanceif-statementstored-procedures
gcresse
gcresse

Reputation: 115

SQL IF ELSE performance issue

I have a stored procedure that can get data from 2 different sources depending on if the user requests data from a single closed period (archived into a data warehouse table) or from an open period (data from transaction tables).

If I pass parameters that limit the select to the data warehouse table (providing a year and period for a closed period) the procedure takes a very long time to return results unless I comment out the ELSE BEGIN… code. No data is coming from the ELSE portion of code but it is still slowing down the procedure. If I comment out the ELSE portion of code, it is very fast.

I have tried OPTION (RECOMPILE) and I’m using local variables to avoid parameter sniffing but it’s not helping. Is there any way to get around this? The following is an example of what I’m doing that runs slow:

IF @Year <> 0 AND @Period <> 0 AND (SELECT PerClosedTimestamp
                                    FROM Period
                                    WHERE 
                                        PerCompanyID = @CompanyID AND
                                        PerYear = @Year AND
                                        PerPeriod = @Period) IS NOT NULL
BEGIN
    SELECT   
        datawhse.column1, datawhse.column2, etc …
    FROM    
        datawhse        
END 
ELSE
BEGIN
    SELECT 
        trantable.column1, trantable.column2, etc…
    FROM    
        trantable       
END

If I exclude the ELSE statement it runs very fast:

IF @Year <> 0 
   AND @Period <> 0 
   AND (SELECT PerClosedTimestamp
        FROM Period
        WHERE PerCompanyID = @CompanyID 
          AND PerYear = @Year   
          AND PerPeriod = @Period) IS NOT NULL
BEGIN
    SELECT   datawhse.column1
            ,datawhse.column2, etc …
    FROM    datawhse        
END

Upvotes: 6

Views: 3767

Answers (3)

gcresse
gcresse

Reputation: 115

Thanks everyone for your suggestions. I ended up creating 2 separate functions to return data from either the data warehouse table or the transaction tables. I select from the functions within the IF THEN ELSE statement and that seems to have solved my problem.

Upvotes: 1

James K. Lowden
James K. Lowden

Reputation: 7837

As mentioned in the comments, the definitive answer to why is it slow is always to be found in the query plan.

At a guess, the appearance of trantable in the procedure is biasing the query optimizer in a way that disfavors datawhse. I'd be tempted to at least try UNION ALL instead of IF/THEN, something along the lines of 

SELECT   
    datawhse.column1, datawhse.column2, etc …
FROM    
    datawhse
WHERE @Year <> 0 AND @Period <> 0 AND (SELECT PerClosedTimestamp
                                FROM Period
                                WHERE 
                                    PerCompanyID = @CompanyID AND
                                    PerYear = @Year AND
                                    PerPeriod = @Period) IS NOT NULL
UNION ALL
SELECT 
    trantable.column1, trantable.column2, etc…
FROM    
    trantable       
WHERE @Year = 0 OR @Period = 0 OR (SELECT PerClosedTimestamp
                                FROM Period
                                WHERE 
                                    PerCompanyID = @CompanyID AND
                                    PerYear = @Year AND
                                    PerPeriod = @Period) IS NULL

It would be interesting to see how the query plans compare.

Upvotes: 2

Dance-Henry
Dance-Henry

Reputation: 953

Are @Year and @Period directly from the input of the stored procedure? like in your sproc definition, did you write in this following way?

create proc USP_name @Year int, @Period int as
begin
     ...
end

You can try using local variable, according to my experience in many cases like this, local variables help a lot.

create proc USP_name @Year int, @Period int as
begin
    declare @Year_local int, @Period_local int
    set @Year_local = @Year, @Period_local = @period

    if @Year_local <> 0 AND @Period_local <> 0 AND ...
    ....
end

Upvotes: 2

Related Questions