PetaPoco and VarChar

Back in 2012, Jimmy Bogard wrote a great post on the specifics of querying against a varchar column in SQL Server. As it turns out, it’s pretty easy to encounter this problem even when you don’t realize it. So, I thought it’d be a good idea to revisit this from the perspective of PetaPoco (my favorite ORM).

Let’s start with our database table which, in this example, has some fields I’m sure we’re all familiar with. I’ve also filled it up with a significant amount of data.

CREATE TABLE Users(
    id [uniqueidentifier] NOT NULL,  /*Primary Key */
    username  [nvarchar](100) NOT NULL,
    firstname [nvarchar](100) NULL,
    lastname  [nvarchar](100) NULL,
    email     [varchar](200) NULL,
....

SELECT COUNT(1) FROM Users  /*  15000003, executed in ~19 seconds */

Lets say we want to create a simple search that lets us look up someone based on their email address. This is a pretty easy task with PetaPoco…

using (var db = new Database("db"))
{
    var searchVal = "someone@somwhere.com";
    var user = db.FirstOrDefault<User>("WHERE email = @0", searchVal);
}

So, we fire this up, we run a test, and we find out it takes ~20 seconds to execute. You probably already know the next step - we need an index.

CREATE NONCLUSTERED INDEX Email_Index ON Users
(
    email ASC
)

BOOM! now we’re down to …. ~2 seconds. This a step in the right direction but still too slow.

Let’s see whats up with the query…

SELECT * FROM Users WHERE email = 'someone@somwhere.com'
/*  < 1 second */

So what the heck is PetaPoco generating?

When we crack open our profiler (lately I’m enjoying Express Profiler), the plot thickens…

exec sp_executesql N'SELECT ... 
FROM [Users] WHERE email = @0',N'@0 nvarchar(4000)',@0=N'someone@somwhere.com'
go

Notice anything weird? Well, if you look at our original table declaration, we defined [email] as varchar(100). When we examine our execution plan, we see the exact problem identified by Jimmy. As he explains…

going to our ORM and forcing the SQL parameter type to be an ANSI string fixed the problem

Okay, so how do we do that?

db.FirstOrDefault<User>("WHERE email = @0", 
new SqlParameter() { DbType = DbType.AnsiString, Value = searchVal });

Easy enough. Our tests now average under 40ms. However, by doing this you also are likely to run into the following exception - {“The SqlParameter is already contained by another SqlParameterCollection”}.

This is the side-effect of passing explicit SqlParameters to PetaPoco. Its pretty easy to imagine a scenario where you need to query somwhere along these lines…

db.FirstOrDefault<User>("WHERE firstname = @0 OR lastname = @0", 
new SqlParameter() { DbType = DbType.AnsiString, Value = searchVal });

Named Parameters

The reason for this exception is because of PetaPoco’s “NamedParameters” feature, which basically causes it to run a RegEx replace on your sql. Its also very easy to disable:

db.EnableNamedParams = false;

But at the end of the day, you don’t even need to do that. In PetaPoco 2.1 they added a nice little class called AnsiString just for this purpose.

var user = db.FirstOrDefault<User>("WHERE email = @0", new AnsiString(searchVal));

It then becomes a general rule of thumb to use AnsiString when you need to query against a table using string parameter. This will help keep you out of trouble when your data grows.