Big difference in execution time of stored proc between Managment Studio and TableAdapter.

Question

How could a stored procdure run in 10 seconds via Management Studio, but take 15 minutes via a TableAdapter for the same inputs? It is repeatable, meaning I have run it at least three times in each environment, and Management Studio is consistently about 100 times faster.

I'm using .net 2.0 and SQL Server 2000

In SQL Server Management, I'm executing it like this:

EXEC    [dbo].[uspMovesReportByRouteStep]
 @RouteStep = 12000,
 @RangeBegin = N'12/28/08',
 @RangeEnd = N'1/18/9'

In the TableAdapter, I'm using a StoredProcedure CommandType and dbo.uspMovesReportByRouteStep for the CommandText. I'm calling the table adapter from an ASP.NET page, although it times out in 30 seconds if I attempt to "Preview Data" locally too.

It's not practical to provide the stored procedure because it's over 100 lines long with dependencies on a number of other UDFs and views on the same and other databases.

All other stored procedures appear to run in about the same time using either method. How is this possible?

Answer 1

This is due to 'parameter sniffing' and a cached query plan that is not appropriate for the particular values of the parameters you are calling it with. How does that happen? Well, the first time you call a SP with one set of values, a query plan will be generated, parameterised and cached. If the SP is called again with another set of parameter values that would have resulted in a different query plan, but it uses the cached query plan, then performance can suffer.

It is often because statistics are out of date. You can determine if that's the case by comparing the Estimated execution plan against the Actual execution plan; if different then statistics are most likely out of date.

I would first try and get the Database's indexes rebuilt, or at least it's statistics updated (ask your DBA). One way to rebuild the indexes (should work on all versions on SQL Server):

exec sp_msforeachtable "dbcc dbreindex ('?')"

If it's still a problem, try temporarily adding the statement WITH RECOMPILE to the stored procedure definition. If the problem goes away, then take a look at using OPTIMIZE FOR, described in this blog post.