使用 SqlCommand 参数时调用 SQL 函数的速度要慢得多

本文关键字：速度 函数 参数 SqlCommand 调用 SQL 使用 | 更新日期: 2023-09-27 18:37:05

我正在从我的 C# ASP.Net 应用程序中调用 SQL 函数 （UDF）。我调用的函数需要一个类型为"唯一标识符"的参数。

如果我进行调用并在 SqlCommand 的命令文本中传递"null"，结果会在大约 3 秒内返回......

SqlCommand Command = new SqlCommand();
Command.Connection = (SqlConnection)(DbDataModifier.CreateConnection());
Command.CommandText = "select * from GetLocalFirmLoginsSummary(null) order by Date asc"
Command.CommandType = CommandType.Text;
Command.Connection.Open();
SqlDataReader Reader = Command.ExecuteReader(); // takes 3 seconds

但是，如果我进行调用并将 DBNull.Value 作为 SqlParameter 传递，则结果需要 60 多秒才能返回...

SqlCommand Command = new SqlCommand();
Command.Connection = (SqlConnection)(DbDataModifier.CreateConnection());
Command.CommandText = "select * from GetLocalFirmLoginsSummary(@CustomerGroupID) order by Date asc"
Command.CommandType = CommandType.Text;
SqlParameter Param = new SqlParameter();
Param.ParameterName = "@CustomerGroupID";
Param.SqlDbType = SqlDbType.UniqueIdentifier;
Param.Direction = ParameterDirection.Input;
Param.IsNullable = true;
Param.Value = DBNull.Value;
Command.Parameters.Add(Param);
Command.Connection.Open();
SqlDataReader Reader = Command.ExecuteReader(); // takes over 60 seconds

如果我在 SQL 管理工作室中运行相同的查询，则大约需要 3 秒，即使将 null 作为参数传递也是如此......

declare @CustomerGroupID uniqueidentifier
set @CustomerGroupID = null
select * from GetLocalFirmLoginsSummary(@CustomerGroupID)

我调用的函数定义为：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetLocalFirmLoginsSummary]
(   
    @CustomerGroupID uniqueidentifier
)
RETURNS TABLE 
AS
RETURN 
(
SELECT     CustomerGroupID, CustomerGroupName, USR, DATEADD(MONTH, DATEDIFF(MONTH, 0, createdDate), 0) AS Date, COUNT(*) AS Quantity
FROM         dbo.GetLocalFirmLogins(@CustomerGroupID) AS Logins
GROUP BY USR, CustomerGroupID, CustomerGroupName, DATEADD(MONTH, DATEDIFF(MONTH, 0, createdDate), 0)
)

。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetLocalFirmLogins]
(   
@CustomerGroupID uniqueidentifier
)
RETURNS TABLE 
AS
RETURN 
(
SELECT     vw_CustomerGroupAccountDetails.CustomerGroupID, vw_CustomerGroupAccountDetails.CustomerGroupName, Organisations.USR, Organisations.town AS FirmTown, 
                  Users.id AS ID, Users.userName, Users.password, Users.isPartner, Users.isFeeEarner, Users.nlisUserName, Users.nlisPassword, Users.email, Users.lastLoginDate, Users.createdDate
FROM         vw_CustomerGroupAccountDetails RIGHT OUTER JOIN
                      Organisations ON vw_CustomerGroupAccountDetails.AccountID = CAST(Organisations.id AS nvarchar(50)) RIGHT OUTER JOIN
                  Users ON Organisations.clientId = Users.uploadedBy
WHERE     (Users.canLogin = 1) AND (Users.isDeleted = 0) AND (NOT (Organisations.USR IS NULL)) AND 
                      ((vw_CustomerGroupAccountDetails.CustomerGroupID = @CustomerGroupID) OR (@CustomerGroupID IS NULL))
)

那么，当我使用 SqlCommand 的 SqlParameter 从 C# 调用 SQL 函数时，是什么原因导致它花费如此长的时间呢？

我正在使用SQL Server 2000。

我

到处搜索过，以我能想到的各种方式，但找不到其他人有同样的问题。

使用 SqlCommand 参数时调用 SQL 函数的速度要慢得多

吉姆，你能发布 dbo 的来源吗？请问GetLocalFirmLogins？

正如马丁所说，这可能是参数嗅探。如果是，代码中的查询提示，其中查找@CustomerGroupID可能是"外科手术"解决此问题的最佳方法。

将选项（循环联接）添加到 dbo 中的查询中。GetLocalFirmLogins可以在这里修复。

我想我也可以解释为什么 SSMS 看不到问题，而 C# 可以看到。查询的这一部分：

(CustomerGroupID = @CustomerGroupID) OR (@CustomerGroupID IS NULL)

可能有两个不同的计划，具体取决于您来自的连接的 ANSI NULL 设置。ANSI NULL 和效果在这里描述：

设置ANSI_NULLS

您可以通过检索此查询的结果来查看连接的 ANSI 设置：

DBCC 用户选项

SSMS 可能具有与 C# 代码不同的 NULL 设置，并导致对查询的不同评估。

它可能是由 SQL Server 中的参数嗅探问题引起的。

您可以在以下链接中找到更多详细信息：

参数嗅探

我希望这会对您有所帮助。

Management Studio 和 .NET 代码之间的性能差异通常与ANSI_NULLS等设置有关。

这可能是SQL Server 2000中的一个错误 - 请参阅这篇文章，它似乎暗示在未考虑CREATE FUNCTION之前设置ANSI_NULLS打开。

我没有SQL Server 2000来测试这一点，但也许您可以尝试编写函数脚本。

相关文章：