Union or Use Flow Control Logic to Determine which table to report on

Question

I'm working with a 3rd pary app where I can't alter the tables. We built custom matching "Monthly" tables with an additional datetime column "AsOfDate" where we dump the data at the end of the month and flag those data with the date of last day of the month.

I want to be able to create a single Stored Procedure (Application is designed to require a view or stored proc as the source of all reports.) and use a parameter which will either use the current data table (Parameter may be NULL or = Today's Date) or use the month end table and filter by the End of the month date. This way, I have one report where the user can either use current or data from a particular month end period.

Which would you prefer (And why) Sorry, this is not fully coded

Solution #1 Union Query

Create Proc Balance_Report (@AsOfDate)
AS

Select Column1
From
    (Select GetDate() as AsOfDate
       , Column1 
     From Current.Balance
    Union 
    Select AsOfDate
       , Column1 From MonthEnd.Balance
    ) AS All_Balances
Where All_Balances.AsOfDate = @AsOfDate

Solution #2 Use If Statement to select table

Create Proc Balance_Report (@AsOfDate)
AS

If @AsOfDate IS NULL or @AsOfDate = GetDate()
   Select GetDate() as AsOfDate
       , Column1 
     From Current.Balance
Else
    Select AsOfDate
       , Column1 From MonthEnd.Balance
    Where AsOfDate = @AsOfDate

Again, this is not fully coded and is sort of db agnostic (But it is SQL Server 2005).

Answer 1

I prefer the non-union solution. Selecting from a single table will always be faster than doing a union and selecting a single table's data from the union.

Answer 2

How you have things set up, the second method will probably be faster. If you were to use a partitioned view then you could set up constraints in such a way that the optimized would know to ignore one or more of the tables in the select and you would get the same performance. This would also let you keep all of your logic in one statement rather than having to keep two statements in sync. That may or may not be an issue for you based on how complex the SELECT statement is.

One thing to remember though, is that if you use the second method, be sure to mark your stored procedure as WITH (RECOMPILE) (I can't remember if the parentheses are require or not - check the syntax). That way the optimizer will create a new query plan based on which branch of the IF statement needs to be executed.