I am trying to fine tune a SPROC that is taking about 25 seconds to run.

I have one table with a record every day. I have a second table with a record for a time frame, and it has a StartDate and EndDate field.

I want to link the tables together in one query. I have been doing something like this:

SELECT
     Table1.Table1ID
     , Table1.Field1
     , Table1.Field2
     , Table1.EffectiveDate
     , Table2.Table2ID
     , Table2.Field1
FROM
     Table1
          LEFT OUTER JOIN Table2 ON (Table1.EffectiveDate > Table2.StartDate AND Table1.EffectiveDate < Table2.EndDate)

This is getting me the results I want, but at a performance price. Is there a better way of doing this, like loading Table2 into a temp table before joining? Thanks!