Calling SQL functions is much slower when using SqlCommand parameters

Question

I call the SQL function (UDF) from my ASP.NET C # application. The function I call requires a single parameter of type "uniqueidentifier".

If I make a call and pass "null" to the CommandText SqlCommand, the results will return 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 a call and pass DBNull.Value as SqlParameter, the results will take more than 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 null as a parameter ...

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

The function that I call 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 makes it take a lot longer when I call the SQL function from C # using SqlParameter SqlCommand?

I am using SQL Server 2000.

I searched everywhere and in every possible way, but I can not find someone else with the same problem.

Answer 1

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

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

Adding OPTION (LOOP JOIN) to the request in dbo.GetLocalFirmLogins can be fixed here.

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

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

It may have two different plans depending on the ANSI NULL settings in the connection you came from. ANSI NULL and the effect is described here:

Set ANSI_NULLS

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

DBCC USERS

SSMS may have a different NULL parameter, which you use for C # code and call a different request evaluation.

Answer 2

Perhaps this is due to the Sniffing parameterization issue in SQL Server.

You will find more details in the following link:

Sniffing parameter

Hope you find this helpful.

Answer 3

The performance differences between Studio Management and .NET code are often associated with settings such as ANSI_NULLS.

This might be a bug in SQL Server 2000 - see this post , which seems to imply that SET ANSI_NULLS ON before CREATE FUNCTION will not count.

I do not have SQL Server 2000 to test this, but maybe you can try the script of your function.