SQL IF ELSE performance issue

Question

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 

Answer 1

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