ADO.NET 命令和 SQL 查询计划

发布于 2024-12-15 23:50:03 字数 1199 浏览 2 评论 0 原文

我一直在阅读查询计划以及如何最大限度地减少 SQL Server 为同一基本查询创建的重复计划。例如,如果我理解正确,发送带有内联值的查询可能会导致 2 个不同的查询计划。过于简单的示例:

"SELECT FirstName FROM Members WHERE LastName = 'Lee'"
"SELECT FirstName FROM Members WHERE LastName = 'MacGhilleseatheanaich'"

使用存储过程可以避免这种情况,因为它确保查询哈希相同。 “LastName”作为参数传递,例如:

CREATE PROCEDURE sp_myStoredProcedure
   @LastName varchar(100)
AS
SELECT FirstName FROM Members WHERE LastName = @LastName
Go

现在,我的问题是这是否同样适用于 Command 对象(例如 ADO.NET 中的 SQLClient.SQLCommand)。我问的原因是字符串参数没有定义最大长度,如上面的代码所示。采取以下代码:

MyCmd.CommandText = "SELECT FirstName FROM Members WHERE LastName = @LastName"
MyCmd.Parameters.AddWithValue("@LastName", "Lee")

然后稍后:

MyCmd.Parameters.Clear()
MyCmd.Parameters.AddWithValue("@LastName", "MacGhilleseatheanaich")

由于@LastName尚未向SQL Server声明为具有定义的最大长度,当我以这种方式执行命令时,SQL Server是否会为每个不同的值创建一个新的查询计划?

我的问题主要来自于阅读.NET 3.5中的LINQ2SQL如何通过定义不同的参数长度来错过缓存(http://damieng.com/blog/2009/12/13/sql-server-query-plan-cache)。由于使用 Command 对象时也没有定义长度,所以它不会有同样的问题吗?

I've been reading up on query plans and how to minimise duplicate plans being created by SQL Server for the same basic query. For example, if I understand correctly, sending queries with inline values may result in 2 different query plans. Oversimplified example:

"SELECT FirstName FROM Members WHERE LastName = 'Lee'"
"SELECT FirstName FROM Members WHERE LastName = 'MacGhilleseatheanaich'"

Using a stored procedure avoids this, as it ensures the query hash will be the same. "LastName" is passed as a parameter, eg:

CREATE PROCEDURE sp_myStoredProcedure
   @LastName varchar(100)
AS
SELECT FirstName FROM Members WHERE LastName = @LastName
Go

Now, my question is whether the same applies to the Command object (eg. SQLClient.SQLCommand in ADO.NET). The reason I ask is that string parameters don't have a defined max length, as in the code above. Take the following code:

MyCmd.CommandText = "SELECT FirstName FROM Members WHERE LastName = @LastName"
MyCmd.Parameters.AddWithValue("@LastName", "Lee")

Then later:

MyCmd.Parameters.Clear()
MyCmd.Parameters.AddWithValue("@LastName", "MacGhilleseatheanaich")

Since @LastName hasn't been declared to SQL Server as having a defined maximum length, will SQL Server create a new query plan for every different value when I execute the command this way?

My question comes mainly from reading how how LINQ2SQL in .NET 3.5 can miss the cache by defining different parameter lengths (http://damieng.com/blog/2009/12/13/sql-server-query-plan-cache). Since lengths aren't defined either when using Command object, would it not share the same problem?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

没︽人懂的悲伤 2024-12-22 23:50:03

您的参数化查询实际上作为对系统存储过程 sp_executesql 的调用发送到服务器,该存储过程非常擅长通过比较未填充的的哈希值来尝试尽可能重用查询计划。 em> 计划的语句掩码仍然存在于缓存中,因此如果可能并且服务器认为其合适,您就有很大的机会重用计划(也可能出现参数嗅探问题)。

另外值得一提的是,简单参数化几乎可以保证您中的两个语句第一个示例实际上最终会使用相同的计划。

Your paramaterized queries are actually sent to the server as a call to the system stored procedure sp_executesql which is very good at attempting to reuse query plans where possible by comparing a hash of the the unpopulated statement mask with those of plans still live on the cache, so if its possible and if the server thinks its appropriate you have a good chance of plan reuse (and potentially of paramater sniffing problems too).

Also worth mentioning that Simple Parameterization pretty much guarantees that the two statements in you first example would actually end up using the same plan.

咋地 2024-12-22 23:50:03

查询提示可以添加到您的 SP 中,以控制何时、如何创建 QEP。这可能无法解决您的特定问题(或者确实回答您的特定问题!),但是网上有很多关于此问题的信息,例如 http://msdn.microsoft.com/en-us/library/ms190727.aspx

Query hints can be added to your SP that control when - how - QEP will be created. This may not solve your particular issue (or indeed answer your specific question!) however a lot of info on the web about this e.g. http://msdn.microsoft.com/en-us/library/ms190727.aspx

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文