Try dropping and recompiling the procedure. It may be that the query plan
stored for the procedure was based on table statistics that have now become
obsolete.
Joe Weinstein at BEA

mas wrote:

> I have a Stored Procedure (SP) that creates the data required for a
> report that I show on a web page. The SP does all the work and just
> returns back a results set that I dump in an ASP.NET DataGrid. The SP
> takes a product area and a start and end date as parameters.
>
> Here are the basics of the SP.
>
> 1. Create temp table to store report results, all columns are created
> that will be needed at this point.
> 2. Select products and general product data into the temp table.
> 3. Create a cursor that loops through all the products in the temp
> table, running a more complex query with each individual product.
> 4. The results of that query are updated on the temp table based on the
> current product of the cursor.
> 5. A complex "totals" query is run and the results from that are
> inserted into the temp table as the last 3 rows.
>
> In all we are talking about 120 rows in the temp table with 8 columns
> that are mostly numbers.
>
> I originally wrote this report SP about a month ago and it worked fine,
> ran in about 10 - 20 seconds based on server traffic and amount of
> data in the temp table. For the example I'm running there are the
> 120 products.
>
> Just yesterday the (SP started timing out and when I ran the SP
> manually from Query Analyzer (QA) (exec SP_NAME ... ) with the same
> parameters as it was getting in the code it took 6 minutes to complete.
> I was floored. I immediately copied the SQL out of the SP and pasted
> into another QA window, changed the variables to be hard coded values
> and ran it. It completed in 10 seconds.
>
> I'm really confused now. I ran a Profiler on the 2 when I ran them
> again. The SQL code in QA executed again in ~10 seconds with 65,000
> reads. When the SP finished some 6 minutes later it had completed wit
> the right results but it needed 150,000,000 reads to do its job.
>
> How can the exact same SQL code produce such different results (time,
> disk reads) based on whether its in a SP or just run from QA but still
> give me the exact same output. The reports both look correct and have
> the same numbers of rows.
>
> I asked my Sys Admin if he had done anything to anything and he said
> no.
>
> I've been reading about recompiles and temp table indexes and all
> kinds of other stuff that could possibly be affecting it but have
> gotten nowhere.
>
> Any ideas are appreciated.
>