SQL IF ELSE performance issue
<p>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).</p>

<p>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. </p>

<p>I have tried <code>OPTION (RECOMPILE)</code> 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:</p>

<pre><code>IF @Year &lt;&gt; 0 AND @Period &lt;&gt; 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 
</code></pre>

<p>If I exclude the ELSE statement it runs very fast:</p>

<pre><code>IF @Year &lt;&gt; 0 
   AND @Period &lt;&gt; 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 
</code></pre>