ADO.NET Performance Boost

This is something interesting that we discovered during our PSR tests.  So we had a table that had a char(5) as primary key and which was accessed very frequently in order to retrieve data from the table.  By reviewing the execution plans for all select statements we realized that every select on that table was performing an Index Scan instead of an Index Seek.  Cheers! There is room for improvement!  As a result, I started looking into the application code in order to find which part of the application was actually sending the SQL request.  So I came across a method that looked more or less like this:

 

Hm! Nothing strange so far, right!  Well, not exactly because the developer who wrote this piece of code forgot something fundamental about .NET String and ADO.NET.  You see System.String by default is Unicode.  As a result in the example above when you don’t specify the DBParameter type ADO.NET will default it to nvarchar for the actual SQL request execution.  So the T-SQL emitted is:

which will result to the following execution plan:

Oops! There you got it.  So how do you fix it?  Well it is really easy.  Just specify the DBParameter type to be in accordance with the primary key type or else:

So with this small change the query emitted becomes:

which gives us an optimized execution plan that performs Index Seek instead of Index Scan

So this really small change gave us a 15% performance boost.  Oh well! One problem solved, one thousand more waiting to be solved.

I am not a smoker and this is the SmokingCode!