StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Stored Procedure vs. Direct SQL Query

Stored Procedure vs. Direct SQL Query

Bill Cunnien — Thu, 13 Aug 2009 18:24:15 GMT

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?

RE: Stored Procedure vs. Direct SQL Query

Peter Jones — Thu, 13 Aug 2009 18:24:15 GMT

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

RE: Stored Procedure vs. Direct SQL Query

Trent L. Taylor — Thu, 13 Aug 2009 08:58:24 GMT

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.

RE: Stored Procedure vs. Direct SQL Query

Bill Cunnien — Tue, 11 Aug 2009 10:39:33 GMT

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