Server参数化查询-显式SQL语句与sp_executesql

Question

对于参数化查询，使用显式SQL语句与sp_executesql之间是否存在性能(或任何其他)差异？

例如SQL语句：

DECLARE @objectName SYSNAME, @objectType CHAR(2)

SELECT @objectName = N'sysrscols', @objectType='s' 

SELECT * 
FROM sys.objects 
WHERE [name] = @objectName AND [type] = @objectType

通过sp_executesql也是如此

EXEC sys.sp_executesql
   N'select * from sys.objects where [name] = @objectName and [type] = @objectType',
       N'@objectName SYSNAME, @objectType CHAR(2)',
       @objectName = N'sysrscols', @objectType = 's'

为什么实体框架使用sp_executesql？当使用查看它生成的查询时，如果它们只是SQL语句，那么读取它们就容易得多。

Answer 1

细微差别是第一个查询没有参数化。它是在查询中使用局部变量的一批SQL语句。这批语句作为批处理请求发送到Server，其中没有编译和执行它的参数。您可以从跟踪(事件探查器或扩展事件)中看到，事件类型是*batch*而不是*rpc*。

当SqlClient应用程序(包括EF生成的SQL语句)使用参数化查询(通过在SQL语句中向SqlCommand.Parameters集合和参数名称添加参数)时，作为RPC (远程过程调用)请求发送到Server的请求，其中参数值是独立于SQL语句本身定义和传递的。跟踪事件将显示rpc_*而不是sql_batch_*。SqlClient API为此使用了一个sp_executesql。

参数化查询有许多好处，无论是由EF生成的还是直接在代码中生成的。其中包括：

• 更安全，防止SQL注入。
• 消除在字符串中转义引号的需要
• 避免以特定方式格式化日期字符串文本的需要。
• 不需要十进制分隔符
• 通过促进计划缓存重用来提高性能

我要补充的是，您在RPC调用的Profiler跟踪中看到的文本只是底层TDS协议请求的反向工程。如果运行网络跟踪，将不会看到对跟踪中看到的sp_executesql语句的引用。但是，参数化查询的要点是参数值以本机格式分别传递给Server。

Answer 2

如果只更改参数，那么SQL server将如何优化执行，这一点完全不同。如果您使用这样的普通语句，则将像每次运行它时一样编译和执行该语句。如果您使用sp_executesql并使用其他参数多次运行它，则server“很可能”重用执行计划，从而节省性能：

当对语句的参数值的更改是唯一的变化时，可以多次使用sp_executesql代替存储过程来执行Transact-SQL语句。因为Transact-SQL语句本身保持不变，而且只有参数值变化，所以Server查询优化器很可能重用它为第一次执行而生成的执行计划。

(来源于MSDN)