在SQL Server中，TSQL是否比存储过程返回更快的结果?

I have a stored procedure that works fine previously. It took 4 to 5 secs to get the results.

我有一个以前运行良好的存储过程。需要4到5秒才能得到结果。

I didn't used this stored procedure for the past two months. When I call the same procedure now it takes more than 5 minutes to produce the result. (There is no records populated to my source tables in the past two months)

在过去的两个月里，我没有使用这个存储过程。当我现在调用相同的程序时，生成结果需要超过5分钟。(在过去两个月内，我的源表中没有填充记录)

I converted the stored procedure and executed as TSQL block it is back to normal. But when I convert back to stored procedure again it is taking more than 5 minutes.

我转换了存储过程，并以TSQL块的形式执行，使它恢复正常。但是当我再次转换回存储过程时，它需要超过5分钟。

I am wondering why it is behaving like this. I used 6 table variables. I just populating those table variables to get the desired results by joining all those.

我想知道它为什么会这样。我使用了6个表变量。我只是填充这些表变量，通过加入所有这些变量来得到想要的结果。

I already tried the below options

我已经尝试了以下选项

With Recompile at the stored procedure level
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
sp_updatestats

but there is no improvement. When I execute it as TSQL it works fine.

但没有任何改善。当我以TSQL执行它时，它工作得很好。

Please suggest me any ideas to optimize the stored procedure.

请给我一些优化存储过程的建议。

2 个解决方案

#1

In your queries, add OPTION(OPTIMIZE FOR UNKNOWN) (as the last clause) to prevent parameter sniffing. For syntax and explanation, see the documentation on Query Hints.

在查询中，添加选项(为未知进行优化)(作为最后一个子句)以防止参数嗅探。有关语法和解释，请参阅查询提示的文档。

What SQL Server does the first time it runs a Stored Procedure is optimize the execution plan(s) for the parameters that were passed to it. This is done in a process that is called Parameter Sniffing.

SQL Server第一次运行存储过程时所做的就是为传递给它的参数优化执行计划。这是在一个称为参数嗅探的过程中完成的。

In general, execution plans are cached by SQL Server so that SQL Server doesn't have to recompile each time for the same query. The next time the procedure is run, SQL Server will re-use the execution plan(s) for the queries in it... However, the execution plan(s) might be totally inefficient if you call it (them) with different parameters.

通常，执行计划由SQL Server缓存，因此SQL Server不必每次都为相同的查询重新编译。下次运行过程时，SQL Server将对其中的查询重新使用执行计划……但是，如果您用不同的参数调用执行计划(它们)，那么执行计划可能会非常低效。

The option I gave you will tell to the SQL compiler that the execution plan should not be optimized for specific parameters, but rather for any parameter that is passed to the Stored Procedure.

我给您的选项将告诉SQL编译器，执行计划不应该针对特定的参数进行优化，而应该针对传递给存储过程的任何参数进行优化。

To quote the documentation:

引用的文档:

OPTIMIZE FOR UNKNOWN

优化未知

Instructs the query optimizer to use statistical data instead of the initial values for all local variables when the query is compiled and optimized, including parameters created with forced parameterization.

指示查询优化器在编译和优化查询时使用统计数据而不是所有本地变量的初始值，包括使用强制参数化创建的参数。

In some cases Stored Procedures can benefit from Parameter Sniffing, in some cases they don't. For the Stored Procedures that don't benefit from Paramater Sniffing, you can add the option to each query that uses any of the parameters of the Stored Procedure.

在某些情况下，存储过程可以从参数嗅探中获益，在某些情况下则不会。对于不能从参数嗅探中获益的存储过程，可以向每个使用存储过程的任何参数的查询添加选项。

#2

You may have bad execution plan associated with that proc. Try this one

你可能有与这个过程相关的糟糕的执行计划。试试这个

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

You may also find this interesting to read http://www.sqlpointers.com/2006/11/parameter-sniffing-stored-procedures.html

您可能还会发现阅读http://www.sqlpointers.com/2006/11/parameter- sniffing-stordures .html也很有趣

#1