Dapper vs ADO.Net with reflection which is faster?

Question

I have studied about Dapper and ADO.NET and performed select tests on both and found that sometimes ADO.NET is faster than Dapper and sometimes is reversed. I understand this could be database issues as i am using SQL Server. As it is stated that reflection is slow and i am using reflection in ADO.NET. So can anyone tell me which approach is the fastest?

Here what i coded.

1. Using ADO.NET

   DashboardResponseModel dashResp = null;
   SqlConnection conn = new SqlConnection(connStr);
   try
   {
       SqlCommand cmd = new SqlCommand("spGetMerchantDashboard", conn);
       cmd.CommandType = CommandType.StoredProcedure;
       cmd.Parameters.AddWithValue("@MID", mid);
       conn.Open();
       var dr = cmd.ExecuteReader();
   
   List<MerchantProduct> lstMerProd = dr.MapToList<MerchantProduct>();
   List<MerchantPayment> lstMerPay = dr.MapToList<MerchantPayment>();
   
   if (lstMerProd != null || lstMerPay != null)
   {
       dashResp = new DashboardResponseModel();
       dashResp.MerchantProduct = lstMerProd == null ? new 
       List<MerchantProduct>() : lstMerProd;
       dashResp.MerchantPayment = lstMerPay == null ? new 
       List<MerchantPayment>() : lstMerPay;
   }
   
   dr.Close();
   
   }
   
   return dashResp;
   

2. Using Dapper

   DashboardResponseModel dashResp = null;
   
   var multipleresult = db.QueryMultiple("spGetMerchantDashboard", new { mid = 
   mid }, commandType: CommandType.StoredProcedure);
   var merchantproduct = multipleresult.Read<MerchantProduct>().ToList();
   var merchantpayment = multipleresult.Read<MerchantPayment>().ToList();
   
   if (merchantproduct.Count > 0 || merchantpayment.Count > 0)
   dashResp = new DashboardResponseModel { MerchantProduct = 
   merchantproduct, MerchantPayment = merchantpayment };
   
   return dashResp;
   

Gil

Interesting, I thought manual reflect is always faster than mapper. What's scenario you test that Dapper is faster? – Uremia

@蕭為元 it'll typically be "about the same" – Sludge

Answer 1

Dapper basically straddles ADO.NET as a very thin abstraction - so in theory it can't be faster than well written ADO.NET code (although to be honest: most people don't write well written ADO.NET code).

It can be virtually indistinguishable, though; assuming you're using just Dapper (not any of the things that sit on top of it) then it doesn't include any query generation, expression tree / DSL parsing, complex model configuration, or any of those other things that tend to make full ORMs more flexible but more expensive.

Instead: it focuses just on executing user-supplied queries and mapping results; what it does is to generate all of the materialization code (how to map MerchantProduct to your columns) via IL-emit and cache that somewhere. Likewise it prepares much of the parameter preparation code in the same way. So at runtime it is usually just fetching two delegate instances from cache and invoking them.

Since the combination of (latency to the RDBMS + query execution cost + network bandwidth cost of the results) is going to be much higher than the overhead of fetching two delegates from dictionaries, we can essentially ignore that cost.

In short: it would be rare that you can measure a significant overhead here.

As a minor optimization to your code: prefer AsList() to ToList() to avoid creating a copy.

Sludge

Answer 2

Theory:

Dapper is micro-ORM or a Data Mapper. It internally uses ADO.NET. Additionally, Dapper maps the ADO.NET data structures (DataReader for say) to your custom POCO classes. As this is additional work Dapper does, in theory, it cannot be faster than ADO.NET.

Following is copied from one of the comments (@MarcGravell) for this answer:

it can't be faster than the raw API that it sits on top of; it can, however, be faster than the typical ADO.NET consuming code - most code that consumes ADO.NET tends to be badly written, inefficient etc; and don't even get me started on DataTable :)

It is assumed that ADO.NET is used properly in optimized ways while this comparison. Otherwise, the result may be opposite; but that is not fault of ADO.NET. If ADO.NET used incorrectly, it may under-perform than Dapper. This is what happens while using ADO.NET directly bypassing Dapper.

Practical:

Dapper in most of the cases perform equally (negligible difference) compared to ADO.NET. Dapper internally implements many optimizations recommended for ADO.NET those are in its scope. Also, it forces many good ADO.NET coding practices those ultimately improve performance (and security).

As mapping is core part of Dapper, it is much optimized by use of IL. This makes Dapper better choice than manually mapping in code.

Refer this blog which explains how Dapper was invented and how it is optimized for performance: https://samsaffron.com/archive/2011/03/30/How+I+learned+to+stop+worrying+and+write+my+own+ORM

In following scenario, Dapper MAY be slower:

1. If returned data structure is large enough (which increases the mapping time), Dapper will be slightly slower. But, this is equally true for ADO.NET as well. As said earlier, mapper part of Dapper is much optimized; so it is still better choice than manual-mapping in code. Further, Dapper provides buffered parameter; if set to false, Dapper does not materialize the list. It simply hands over each item to you in iterator. Refer comment on this answer by @Marc.

2. Dapper does not implement provider-specific features as it is written over IDbConnection. This may hit the performance in those very rare cases. But this can be done if you implement an interface to tell Dapper how to do this.

3. Dapper does not support preparing the statements. That may be an issue in very few cases. Read this blog.

With this slight and rare performance hit, you get huge benefits including strongly typed data structure and much less and manageable code. This is really a big gain.

There are many performance comparison statistics of Dapper (with other ORMs and ADO.NET) available on net; have a look just in case you are interested.

Hydroelectric

I don't see the logic of point 1; can you clarify what you mean there? – Sludge

it will take longer than it would have taken for 20 rows, but: regular ADO.NET code processing 100,000 rows will also take the same "longer", for the same reasons - sheer weight of data. The two will remain directly comparable. Note that Dapper also has a buffered: false parameter to help with this scenario, helping you treat it as an open sequence directly on the reader (meaning: it doesn't put the items in a list - it just hands each to you in turn via an iterator block) – Sludge