I am not sure where to turn on this issue. I have a report that is a list of raw material and their inventory quantities. Until this last month-end, the report was working fine. Now, the report times out for one of our two locations. If I call the stored procedure in the Query Analyzer, the stored procedure takes nearly 60 seconds to run. If I run the bare SQL script (copied and pasted from the the stored procedure) in the Query Analyzer, it takes less than 3 seconds. If I remove the sort in the query, it takes only 1 second. I do not want to increase the timeout on the command object because I think there is something else going on here...more on the server and the stored procedure than on the client. Any ideas?

There were statistics missing from one of the tables. I remedied that situation but it did nothing to alter the timeout issue.



So, I checked every line in the stored procedure to validate each part. Everything was working fine...except the speed problem. I ended up using a temporary table to establish a particular look-up rather than building it directly into the WHERE clause. This has changed the stored procedure to load in 2 seconds rather than 60. The direct SQL script still runs in under 3 seconds without using the temporary table, though.



Why this anomaly has occurred is beyond my reasoning abilities. I have to admit that I am out of my league. Perhaps a SQL guru can step in here and fill in some of my RDBMS knowledge voids.



Thanks!

Bill

Well, this can be a long and complex discussion as well as a test. But in short, it has to do with evaluation and trips over the table(s) being evaluated. Many times when you create a temp table, you drastically reduce the number of reflections over a base or core table being queried. Also, indexes, joins, and constraints can have an effect on this as well. Giving you a straight answer is not possible without having your data and sproc so we can see what is actually going on and look at the execution plans, etc.

Hi Bill,



Try recompiling the proc. It may well be the stats used when the current execution plan was calculated have changed significantly and you need a new plan (which is what you got when executing the identical code in QA).



Cheers, Peter