Posted by Erland Sommarskog on 06/04/05 01:48

mas (bmasephol@gmail.com) writes:
> 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.

10-20 seconds for the volumes you indicate is unreasonably high. You
should be able to slash this to subsecond execution time. Assuming
that is, you can rip out all the cursors, and replace everythng
with set-based processing.

> 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.
>...
> 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.

If the stored procedure uses variables, and QA uses hard-coded
constants there is a huge difference when it comes to performance.
If you have constants the optimizer knows exacrly which values that
you have. If you have variables, the optimizer has no idea, but have
to make standard assumptions. Less accurate information => less accurate
estimates => less optimal execution plan.

But the real problem is the cursor. Iterative processing in SQL is
something you should try to avoid.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]