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?

Abnormal

Answer 1

This is very likely 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.

Alvarez

Hilariously, we don't really have a DBA here. Somehow the database keeps on trucking on without any intervention, and has for years. But I have privileges to do pretty much anything on the server, so maybe I'll start poking at this stuff. Thanks for the pointers. – Abnormal

WITH RECOMPILE in the sproc definition makes it run faster than ever. I haven't rebuilt the indexes yet. But the WITH RECOMPILE definitely solved the problem. – Abnormal

Please Note: WITH RECOMPILE will recompile your query each time the query is run (and the compilation is relatively expensive, which is why SQL Server tries to cache them). I only advocate using this temporarily, and would use OPTIMIZE FOR instead. – Alvarez

I'll check that out next week. But I'd say it's significantly less expensive than doing nothing, as illustrated by the multiple order of magnitude execution time reduction. – Abnormal