10

I was researching porting some of our EF6 code to Dapper for better performance when I ran into a weird issue. A single row query was taking almost 10 times as much in Dapper than it did in EF. It looked like this:

using (IDbConnection conn = new SqlConnection("connection string"))
{                
      row = conn.Query<ReportView>("select * from ReportView where ID = @ID", 
                                          new {ID = id}))
                                  .FirstOrDefault();
}

This query targets a view with about 80 columns, and the EF version uses the same exact query and the same model. For reference, this is the EF version:

row = context.ReportViews.Where(s => s.ID == id).FirstOrDefault();

I took into account that the first query might be slow, so I took measurements after a "warm up" period. I thought it might be an issue with reusing the EF model, so I created a simple POCO as a model. None of that worked. So I played around with it, trying different things, and decided to try to use a SQL-injectiony concatenated SQL statement.

using (IDbConnection conn = new SqlConnection("connection string"))
{                
      row = conn.Query<ReportView>(string.Format("select * from ReportView where ID = '{0}'", 
            id)).FirstOrDefault();
}

This query was actually faster than the EF one.

So what's happening here? Why is the parametrized query so much slower?

System Down
  • 6,192
  • 1
  • 30
  • 34
  • 1
    Profile the generated SQL – stuartd Jul 22 '16 at 19:17
  • I had all kinds of problems using Views with EF because of the lack of a natural key. I'm not sure what your view does (maybe something you couldn't do in EF like using a CTE), but why not try the query in the view instead of the view with Dapper. – juharr Jul 22 '16 at 19:19
  • 1
    How are you benchmarking? – mxmissile Jul 22 '16 at 19:27
  • @stuartd - How would I get the generated SQL? – System Down Jul 22 '16 at 19:40
  • If you're using SQL Server use the profiler tool. I'm sure similar tools are available for other RDBMS. – stuartd Jul 22 '16 at 19:40
  • @juharr - For one thing this is a legacy app, but the point of contention here isn't *how* to get more performance, but rather why that certain Dapper query was so slow? – System Down Jul 22 '16 at 19:41
  • @mxmissile - Nothing fancy. Run the query a few times to warm up, start a stopwatch, do the query for a set number of times, stop the stopwatch. I do the exact same thing for both the EF query and the Dapper query, once with EF being first, and another time with Dapper being first. – System Down Jul 22 '16 at 19:43
  • What is the previous code? How is the EF context set up? Could it be due to some kind of EF magic that the context stays in memory? [Could context-lifetime be relevant?](https://stackoverflow.com/questions/10777630/questions-about-entity-framework-context-lifetime) – surfmuggle Nov 28 '18 at 00:04

2 Answers2

17

Based on your final example, it seems most likely that your column is varchar but when you use the parameterized query the parameter is being sent in as nvarchar. Since nvarchar to varchar could involve data loss, SQL converts each value in the table to nvarchar for comparison. As you can imagine, converting every row for comparison is slow and prevents the use of the index.

To work around this, you have two choices:

If your database doesn't use nvarchar at all, you can simply change the mapping during application startup:

Dapper.SqlMapper.AddTypeMap(typeof(string), System.Data.DbType.AnsiString);

Otherwise you can change it per query:

row = conn.Query<ReportView>("select * from ReportView where ID = @ID", 
                              new {ID = new DbString { Value = id, IsAnsi = true }})
                              .FirstOrDefault();
Richard Szalay
  • 83,269
  • 19
  • 178
  • 237
1

Its to do with the datatype of the paramater. If it doesn't match that of the index then it casts every row to compare it. Doing it as a string the type gets chosen by the sql parser.

Kev
  • 11
  • 1