Calling SQL Functions much slower when using SqlCommand Parameters

Question

I am calling a SQL Function (UDF) from my C# ASP.Net application. The function I am calling requires one parameter of type "uniqueidentifier".

If I make the call and pass "null" in the CommandText of the SqlCommand, the results come back in about 3 seconds...

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

But, if I make the call and pass DBNull.Value as a SqlParameter, the results take over 60 seconds to return...

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

If I run the same query in SQL Management Studio, it takes about 3 seconds, even when passing the null as a parameter...

declare @CustomerGroupID uniqueidentifier
set @CustomerGroupID = null

select * from GetLocalFirmLoginsSummary(@CustomerGroupID)

The Function I'm calling is defined as:

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))

)

So what causes it to take so much longer when I'm calling the SQL function from C# using a SqlParameter of a SqlCommand?

I'm using SQL Server 2000.

I've searched everywhere and in every way I can think of, but can't find anybody else having an identical problem.

Answer 1

Jim, could you post the source of dbo.GetLocalFirmLogins please?

As Martin says, this could be parameter sniffing. If it is, a query hint in the code where the seek for @CustomerGroupID may be the best way to "surgically" fix this.

Adding OPTION (LOOP JOIN) to the query in dbo.GetLocalFirmLogins could be fix here.

I think I can also explain why SSMS does not see the problem, while C# does. This part of the query:

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

Could have two different plans depending on the ANSI NULL settings of the connection you are coming from. ANSI NULL and the effect is decribed here:

SET ANSI_NULLS

You can see the ANSI settings of your connection by retrieving the results of this query:

DBCC USEROPTIONS

SSMS might have a different NULL setting that you C# code and cause a different evaluation of the query.

Answer 2

It maybe caused by Parameter Sniffing problem in SQL Server.

You can find more detail in following link:

Parameter Sniffing

I hope you'll find this helpful.