I recently had an odd experience with a select statement. It was a very simple select statement hitting a unique index on a varchar column. But it was very slow! It took some time to figure out why, as the reason was a little bit hidden.
I was using Dapper which I did not think could cause any issues at first, I was certain it had to do with my index. After a while I tried the select directly in management studio, but it was not slow there. After a while it hit me, I tried to select it with a unicode string, prefixing the value for the select statement with "N". Something like: select * from Thing where Name = N'Peter';.
Bingo! - the SQL now ran slow again. Meaning the SQL that Dapper generates was a unicode string (nvarchar) targeting a ASCII string (varchar). This creates a conversion with a heavy overhead when running the select statement.
My fix was to make the column NVARCHAR instead of VARCHAR.
Under Ansi Strings and varchar in the dapper documentation. It states that you can use the following syntax to achieve the same without changing the type, which might be better in your situation:
Query<Thing>(
"select * from Thing where Name = @Name",
new {Name = new DbString { Value = "abcde", IsFixedLength = true, Length = 10, IsAnsi = true }
);
I hope this helps you to avoid some of the pain I went through figuring this out. Please leave a comment below!