When was the last time that you update the statistics of the indexes that are involved? Remember that SQL Server has a cost-based optimizer. The optimizer relies on the statistics to decide the best approach to solving a query.

Also, as the amount of data and the distribution of data changes, you can expect that you index scheme may change. S

At a minimum you should take a look at the query plan to see how SQL Server is solving the query.

>>At any rate, this SP has always finished processing within 1 - 4 minutes - which is acceptable when we're processing over 100,000 records.

I'm not sure that I agree with this as a general statement.

>>I have a SP in my app that creates a cursor with between 20,000 and 100,000 rows. It is used in a matching routine to match records from one table to records in another table (it is a financial transaction reconciliation application).

Is there anyway to do this without the cursor? You'll see your performance just tremendously.

-Mike

>Hi,
>
>I have a SP in my app that creates a cursor with between 20,000 and 100,000 rows. It is used in a matching routine to match records from one table to records in another table (it is a financial transaction reconciliation application).
>
>At any rate, this SP has always finished processing within 1 - 4 minutes - which is acceptable when we're processing over 100,000 records.
>
>On one of my P.C.'s - which had been executing this query between 1-4 minutes up to a couple of days ago - now the query is taking 10 minutes to process just 20,000 records.
>
>(Note: I did some testing and it confirmed that it was taking this long to loop through the cursor - in other words - it wasn't something else in the SP causing the bottleneck)
>
>Is there something that can explain why a query that has been consistently running at acceptable speeds for a number of months - all of a sudden is running like a dog? This has me baffled.
>
>
>Thanks, Al