How can I improve performance on a 3 table query?
I see joins to v1 on start_dttm and end_dttm but dont see it in the view or the table listed.

Looking at your definition of your view, are you sure this is an issue with the join, or does the view itself have issues?

There are a few problems, and I will list them in order my brain saw them:


, DATEDIFF(d,GETDATE()+(14-(select DayofPayPeriod from Table_Calendar where date = CONVERT(date,GETDATE()))),date) day_offset
, CASE
    when DATEDIFF(d,GETDATE()+(14-(select DayofPayPeriod from Table_Calendar where date = CONVERT(date,GETDATE()))),date) &lt; 0 
    then '-'
    else '+'
    end + right('0000'+ REPLACE(DATEDIFF(d,GETDATE()+(14-(select DayofPayPeriod from Table_Calendar where date = CONVERT(date,GETDATE()))),date),'-',''),4)
    day_offset_label


These sections of the query indicate you might have RBAR behavior, eg the queries in the parenthesis may be running for each row that you need to return, as you add more data this will take longer and longer. 

Of the columns you use that I can see:

FLOOR(((CONVERT(float,(c1.[Date]-GETDATE()))+(14-c1.[DayofPayPeriod]))/14))

Uses a calculation which is not SARGable in anyway, and so that -1 comparison would need to be calculated for each row as well. 

This may be a case where the execution plan's estimates vs actual rows would be off, you may want to download a tool such as SQL Sentry Plan Explorer (dont need to buy the pro version to easily compare things like this http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view) which will highlight problem areas.

From what I can see of the plan (not much) it is giving me the impression that the planner cannot produce a useful plan and causing you to have a bad time. 

My recommendations:


Compare like types so that you can use an index, or worst case make some sort of materialized view if you cant. See if you can unwrap some of those calculations and use less human readable/nice numbers to evaluate date offsets.


You may need to add an index or two to make this fly.

Consider using a numbers table to calculate offsets or values needed for far in the future, Why are numbers tables &quot;invaluable&quot;? so that you can use the power of the index to query this instead of churn your CPU each time you run this query.
Try to query some of this information in a SARGable fashion and then do the gross formatting work on a temp table or something that is your "final set", if this is still to slow you may want to consider doing this in some other programming paradigm.