为什么sp_executesql的运行速度较慢时，参数作为参数传递 [英] Why does sp_executesql run slower when parameters are passed as arguments

问题描述

查询1：（快如闪电）

sp_executesql "select * from tablesView where Id = 1"

VS

问题2：（太慢）

sp_executesql "select * from tablesView where Id = @Id", N"@Id int", @Id=1

tablesView - 一个包含多个视图联接

LINQ总是转换查询Query2形式，因此表现实在太差。

LINQ always converts queries to Query2 form and hence the performance is really bad.

问题：我需要理由query2缓慢，而任何决议，如果有一个。 而对于LINQ的分辨率。

Questions: I need reason for query2 slowness, and any resolution if there's one. And a resolution for LINQ.

----附加注释：

的性能损失肯定是它使用的是排名函数（ROW_NUMBER），但2列，因为我无法避开他们，我需要他们。

The performance hit is definitely because of the 2 columns which are using ranking functions(row_number) but I can't avoid them I need them.

推荐答案

我要出去肢体这里假设你有很多行，其中n = 1。

I'm going to go out on a limb here and assume that you have a lot of rows where ID = 1.

如果没有，请大家指正。

If not, please correct me.

一个可能的原因是SQL Server正在缓慢处理您的查询是，它看起来在查询和云：

One possible reason that SQL Server is processing your query slow is that it looks at the query and goes:

嗯，我不知道他要传递该参数。
  在这将是1？在这里我有一个极大的行？
  或者1742，在那里我有仅有3
  我只是不知道，我最好做一个表扫描，以确保产生一个执行计划，将涵盖所有的基础

hmm, I wonder what he's going to pass for that parameter.
is it going to be 1? where I have about a gazillion rows?
or perhaps 1742, where I have just 3
I just don't know, I better do a table scan to be sure to produce an execution plan that will cover all my bases

如果一列或一列集，具有低的选择性（即，唯一的值的数目远远大于行数较少），SQL Server将有时恢复到表扫描或类似的，只是为了得到所有行确定性

If a column, or a column set, has low selectivity (ie. the number of unique values is far less than the number of rows), SQL Server will sometimes revert to a tablescan or similar, just to get all rows deterministically.

至少这是我的经验。特别是在做日期范围内有时限的数据表选择的时候，这样做我已经看到了同样的行为，其中DT＆LT; = @dt和DT＆GT; = @dt 让所有行@dt里面的一段时间在该行，恢复到一个表扫描，然后当我把实际的日期到SQL作为一个文字它运行得更快。

At least that's been my experience. In particular I've seen the same behavior when doing date range selects on tables with time-bound data, doing a WHERE dt <= @dt AND dt >= @dt to get all rows where @dt is inside a period of time in that row, reverts to a table-scan, and then when I place the actual date into the SQL as a literal it runs far faster.

这里的问题是选择性的，SQL Server不知道如何建立你的语句的执行计划时，最好满足所有场景，所以它会尝试猜测。

The problem here is the selectivity, SQL Server doesn't know how to best cater for all scenarios when building an execution plan for your statement, so it'll try to guess.

尝试添加查询提示指定的典型的值参数，即：

Try adding a query hint to specify a typical value for the parameter, ie.:

sp_executesql "select * from tablesView where Id = @Id option (optimize for (@id = 1742))", N"@Id int", @Id=1

这篇关于为什么sp_executesql的运行速度较慢时，参数作为参数传递的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！