I'm using dapper for a new project and love it but I don't understand why my queries are really slow. The execution time is very fast, almost instant, but the connection stays open much longer while dapper is mapping the result to my object I guess.

Here is an example in glimpse :

Glimpse result

This query is just a SELECT on something like 15 fields with a where on the primary key, so it's really fast to execute and it doesn't return that much of data. My code to execute it is :

 using (var conn = GetConnection())
    obj = conn.Get<T>(id);

And the object is a very basic poco with Strings and Ints. So why do I waste 220 ms doing this while the query execution itself takes 3 ms ? Where is the difference ?

Thanks for your help !

  • 1
    How are you measuring this, and (perhaps more importantly): how many times are you measuring this? the first run of a query has the overhead of building a strategy in IL (which is then cached and re-used). And the first time of anything also has all the JIT/fusion overheads, all the socket overheads, all the security overheads, etc – Marc Gravell Aug 7 '14 at 9:05
  • 2
    I use glimpse to measure this. And yeah, first run is slower but next runs aren't very good. I ported my code to NPoco to see if it changes anything and it does : NPOCO is way faster (like 20 times faster). I'm sure there's something messed up with dapper and my db or something, but I don't know what. – Dawmz Aug 7 '14 at 11:55
  • Does your class properties exactly match the data type's of the columns in your query? – Magnus Jan 31 '18 at 8:29


There was one field that was causing problems for me in the selection part of my SQL statement. I just went by removing each field one by one and then found the one which was causing the problem.

I had to cast one of my fields to nvarchar like this:

CAST(my_field AS nvarchar(max)) as my_field


It has to do something with the mapping. Because if I change it from "Strongly Typed" (which is taking for ever, almost 1 minute):

var products = connection.Query<Product>(sql).ToList();

to "Anonymous":

var products = connection.Query(sql).ToList();

then it executes really fast (1 second).

I tried and executed the SQL statement directly in "SQL Server Management Studio" as a query and it finishes in less then 1 second.

So my suggestion is, that you use the "anonymous mapping" until dapper guys fix this if they will be able to.

| improve this answer | |

I had a similar experience with Dapper as I was trying to project from a View to an POCO object.

The problem ended up being for me that I did not have a column for each property on my object, so the Convert.ChangeType() was very slow, I added a column to my View that would always return NULL, and the Query<T>() call sped up dramatically.

| improve this answer | |

In my example, the database had an indexed column of type VARCHAR(10). I was attempting to filter via dapper parameter, like so:

DbConnection con = ...
string filterParam = "test";
var results = con.Query("SELECT IndexColumn, Column1, ... FROM MyTable WHERE IndexColumn = @filterParam", new { filterParam });

The issue was dapper (or possibly ADO.Net) converting my filterParam to NVARCHAR(MAX) data type. Sql Server then casts IndexColumn to NVARCHAR, and was doing a full table scan rather than indexed lookup. Code was fixed by casting the parameter before comparison:

var results = con.Query("SELECT IndexColumn, Column1, ... FROM MyTable WHERE IndexColumn = CAST(@filterParam  AS VARCHAR(10))", new { filterParam });
| improve this answer | |
  • 1
    This was the problem for me too. Had to convert the data to the same type of the db column. Thanks – Liquid Core Jun 17 at 10:51

In my case the poor performance seems to have been caused by the fact I was using an asterisk rather than a list of fields when doing the SELECT (i.e. SELECT * instead of SELECT Foo, Bar, Baz, ...).

| improve this answer | |

Your Answer

