Robin Hames

Hints, tricks and tips relating to MS SQL Server and .NET
posts - 14 , comments - 44 , trackbacks - 0

Wednesday, October 29, 2008

Why you should always specify the SqlDbType for an ADO.NET SqlParameter object.

How not specifying the ADO.NET SqlParameter DbType can lead to a horrible query execution plan!
 
I was looking into a query that was performing much worse than expected. The query was a simple select of the form:
select
      e.EmployeeID,
      count(r.EmployeeRoleID)
from Employee e
left join EmployeeRole r
      on e.EmployeeID = r.EmployeeID
where e.EmployeeRef = @EmployeeRef
group by e.EmployeeID
I expected the query plan for this to be fairly straightforward, a couple of Index Seeks and a Left Outer Join, but on examining the query plan, I saw something horrendous. There were 3 Nest Outer Joins, and a Sort that was taking 53% of the execution cost.
Further examination revealed that although the EmployeeRef field on the Employee table was a varchar(50), the @EmployeeRef parameter was being passed in as a nvarchar(6)!
I tracked this down to the use of the ADO.NET SqlParameter(String, Object) constructor. The developer had used this constructor thus:
SqlParameter param1 = new SqlParameter("@EmployeeRef", "XXX123");
sqlCommand.Parameters.Add(param1);
 
This constructor only initialises the name and the value of the SqlParameter object. As the SqlDbType is not specified, the CLR infers the type, in this case as nvarchar(6).
I changed the above code to:
sqlCommand.Parameters.Add("@EmployeeRef",
SqlDbType.VarChar, 50).Value = "XXX123";
 
This gave the expected query execution plan, and the cost of the statement was reduced by about 50%.
I tend to avoid any usage of the SqlParameter constructors that do not specify the SqlDbType, unless I am sure I will set the type explicitly later. For the same reason I avoid the SqlParameter.AddWithValue() method:
sqlCommand.Parameters.AddWithValue("@EmployeeRef", "XXX123");
 
I have on more than one occasion seen the following code, which is obviously wrong, but can give very subtle errors:
sqlCommand.Parameters.AddWithValue("@EmployeeRef", SqlDbType.Int);
 

Posted On Wednesday, October 29, 2008 3:50 PM | Comments (1) | Filed Under [ SQL Server ADO.NET ]

Powered by: