Mike,
- I assume you've got indexes on all three fields...??
- How many records in the tables, and how many are returned.
- Also the 25 seconds, is that measured from the QA or from your application (the QA includes the time for creating the statistics and getting the results returned)?
- Could you try using a BETWEEN ?
- Could you try one of the JOIN Hints (LOOP, HASH and MERGE)
Walter,
>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!