Thursday, February 24, 2011

ADO.NET Query Causes SQL Server to Ignore Primary Key Index

*Disclaimer* I am not a DBA. I'm just a poor, humble developer. I just happened to be the guy standing closest to the server when the DBA quit.1

The Story

I came across a performance bottleneck today that had me stumped for a while.  A query to the database, for a single record, by the primary key, was taking 3 seconds.  Let me say that again, by the primary key, for a single record, taking 3 seconds.  How could this possibly be?  I pulled the query out into Management Studio and ran the query again after turning on the option to display the actual query plan. (Query –> Include Actual Execution Plan)  I was quite surprised when the query plan was not using the primary key index at all.  To make a long story short, SQL Server didn’t use the primary key index because the generated query supplied the parameters as NVarChar while the actual columns were VarChars.

The Cause

The query was generated by an open source tool, so I was able to dig in to find out how it crafted the query.  Basically it came down to this code:

IDbDataParameter parameter = command.CreateParameter();
parameter.ParameterName = paramPrefix + name;
parameter.Value = val ?? DBNull.Value;

This is pretty standard code for ADO.NET but the key isn’t in what’s there, it’s in what is not there.  The parameter’s type is not specific which causes the .NET framework to use it’s own mapping of .NET type to SQL Server type.  The value that was being passed into this piece of code was a string and automatically got mapped as an NVarChar, or System.Data.DbType.String.

The Solution

To get around this default I needed to use my own mapping to use the non-unicode string type.

parameter.DbType = DbType.AnsiString;

With this additional line of code, this query executed infinitely faster; I’m not kidding.  It went from taking around 3 seconds and now consistently measures at 0 milliseconds.  How often can you claim an infinite performance optimization in your system?

1 This story is not true. It's a lie, made up.  There’s has never been a DBA here, and if there had been, I would not have set foot anywhere near the server just to avoid such a situation.  However, in many cases I am the Accidental DBA and I find myself struggling through problems just like this one.