By Bill Cunnien - 8/11/2009
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?
|
By Bill Cunnien - 8/11/2009
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
|
By Trent L. Taylor - 8/13/2009
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.
|
By Peter Jones - 8/13/2009
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
|
|