title: Calling SQL Functions much slower when using SqlCommand Parameters
body: <p>I am calling a SQL Function (UDF) from my C# ASP.Net application. The function I am calling requires one parameter of type "uniqueidentifier".</p>
  
  <p>If I make the call and pass "null" in the CommandText of the SqlCommand, the results come back in about 3 seconds...</p>
  
  <pre><code>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
  </code></pre>
  
  <p>But, if I make the call and pass DBNull.Value as a SqlParameter, the results take over 60 seconds to return...</p>
  
  <pre><code>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
  </code></pre>
  
  <p>If I run the same query in SQL Management Studio, it takes about 3 seconds, even when passing the null as a parameter...</p>
  
  <pre><code>declare @CustomerGroupID uniqueidentifier
  set @CustomerGroupID = null
  
  select * from GetLocalFirmLoginsSummary(@CustomerGroupID)
  </code></pre>
  
  <p>The Function I'm calling is defined as:</p>
  
  <pre class="lang-sql prettyprint-override"><code>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)
  
  )
  </code></pre>
  
  <p>...</p>
  
  <pre><code>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))
  
  )
  </code></pre>
  
  <p>So what causes it to take so much longer when I'm calling the SQL function from C# using a SqlParameter of a SqlCommand?</p>
  
  <p>I'm using SQL Server 2000.</p>
  
  <p>I've searched everywhere and in every way I can think of, but can't find anybody else having an identical problem.</p>