如何才能通过Management Studio中存储的procdure运行在10秒内，而是通过一个TableAdapter对于相同的输入需要15分钟？它是可重复的，这意味着我已经在每个环境中运行它至少三次，并且管理工作室是更快始终如一大约100倍。

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.

我使用的是.NET 2.0和SQL Server 2000

I'm using .net 2.0 and SQL Server 2000

在SQL Server的管理，我执行这样的：

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'

在TableAdapter的，我使用的是 StoredProcedure的 的CommandType 和 dbo.uspMovesReportByRouteStep 为的CommandText 。我打电话从ASP.NET页的表适配器，虽然时间超过30秒，如果我试图preVIEW数据在本地了。

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.

这是不实际提供的存储过程，因为它是100线长与其他一些UDF和同和其他数据库视图的依赖。

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?

推荐答案

这是由于'参数嗅探'和缓存的查询计划，是不适合你与调用它的参数的具体数值很可能。这是如何发生的呢？好了，你第一次打电话与一组值的SP，查询计划将产生，参数设置和缓存。如果SP与另一组将导致不同的查询计划的参数值再次调用，但它使用缓存的查询计划，那么性能会受到影响。

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.

我第一次尝试，并获得数据库的索引重建，或至少它的数据更新（请询问您的DBA）。

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 ('?')"

如果它仍然是一个问题，尝试暂时添加语句 WITH RECOMPILE 存储过程的定义。如果问题解决了，那么就来看看使用 OPTIMIZE FOR 在这个的博客文章。

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.