Mike, two additional suggestions

- Try LEFT HASH JOIN or LEFT MERGE JOIN join hints
- Try to add another, but compound, index on table2 based on startdate, enddate (In SQL server you can base an index on multiple fields with concatenate them via an expression). You can try to make this a unique index or a candidate key.

Good luck

Walter,



>If I run the query right from QA without calling the SP, it is still slow.
>
>Here is the info from the step that is taking most of the time:
>
>Nested Loops/Left Outer Join
>For each row in the top (other) inout, scan the bottom (inner) input, and output matching rows.
>
>Physical operation: Nested Loops
>Logical Operation: Left Outer Join
>Estimated row count: 124,200
>Estimated row size: 205
>...
>...
>nothing else really of interest.
>
>>There must be someting seriously wrong here as that amount of records should not take so much time. If you run the Query directly from the QA and not by calling the SP, do you get different results... Did you try execute the SP with the WITH RECOMPILE option ?
>>
>>Walter,
>>
>>>Walter,
>>>
>>>Yes, there are indexes on all three fields.
>>>
>>>Table1 has 11,341 records, Table2 has 657 records, and the number returned from the query is 11,341.
>>>
>>>The 25 seconds is in QA. My application takes roughly the same amount of time.
>>>
>>>I tried a BETWEEN, but it didn't help.
>>>
>>>I'm not sure what you mean by JOIN Hints. I will do some research.
>>>
>>>>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!