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.