Robin Hames

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

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

Print | posted on Wednesday, October 29, 2008 3:50 PM | Filed Under [ SQL Server ADO.NET ]

Feedback

Gravatar

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

Does it cause any problems setting all strings to nvarchar(max), so I dont have to specify the exact length anywhere?
7/23/2014 4:59 PM | Andrew Bullock
Gravatar

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

@Andrew Bullock It uses a lot more memory for each field and across an entire database, can make a difference. The point of specifying each field's size is so that SQL doesn't assign that field any more memory than needed. Your use of nvarchar(max) should be limited.
8/5/2014 9:47 PM | a
Post A Comment
Title:
Name:
Email:
Comment:
Verification:
 
 

Powered by: