Geeks With Blogs
Robin Hames Hints, tricks and tips relating to MS SQL Server and .NET
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 SQL Server , ADO.NET | Back to top


Comments on this post: Why you should always specify the SqlDbType for an ADO.NET SqlParameter object.

# re: Why you should always specify the SqlDbType for an ADO.NET SqlParameter object.
Requesting Gravatar...
Does it cause any problems setting all strings to nvarchar(max), so I dont have to specify the exact length anywhere?
Left by Andrew Bullock on Jul 23, 2014 4:59 PM

# re: Why you should always specify the SqlDbType for an ADO.NET SqlParameter object.
Requesting Gravatar...
@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.
Left by a on Aug 05, 2014 9:47 PM

Your comment:
 (will show your gravatar)


Copyright © Rhames | Powered by: GeeksWithBlogs.net | Join free