c# - 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;
   

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.