关于Dapper使用中的一点小坑及经验

相信很多人都喜欢用Dapper这款极小且易用、性能高效的ORM

其中DynamicParameters中参数非常有意思

如果使用匿名模板的时候就得一定要注意对应参数类型了

如果使用匿名模板的时候就得一定要注意对应参数类型了

如果使用匿名模板的时候就得一定要注意对应参数类型了重要的事情说3遍

var param = new DynamicParameters(new
{
       model.WorkFlowId,
       model.WorkCenterId,
       Start = start,
       End = end
});

我们正常情况下其实也不会有太大问题，比如数据量在一定级别时性能问题，或者说性能问题凸显并不明显。
但事实上，我们企业中某些数据表数据量极大，简单的讲，我们有一些表数据量已经超过亿甚至过十亿级。在这种情况下，这种性能问题非常明显(尽量我们专业DBA对索引优化的非常好,而且正常情况下，
我们在Sql Server中执行对应语句也就1s内，但事实上我们通过调试或者stopwatch等方式拿到结果看到执行时间近20s

那请问为什么会出现这种情况呢？？？

经分析，我们发现同Dapper中参数解析的问题(理论上在Ado.net中也存在)，当参数化的时候不指定具体参数类型及大小的时候，sql server本身会将对应的参数强制转化为对应默认相近数据类型
比如：我们发现：在DB中WorkFlowId定义为char(12)，
但WorkFlowId = model.WorkFlowId这里则生成如下 
类型：DbType.String（Unicode)  长度是默认长度4000
但实际上我们需要的真实类型是DbType.AnsiStringFixedLength（一个non-Unicode char)以及固

// 说明：db中是char(12)故使用DbType.AnsiStringFixedLength,长度12，而不是使用DbType.String这种(这个是varchar&nvarchar
// 同时长度12则与db中完全一致(char(12)),避免使用其他类型或者长度，而导致的sql server解析的时候还需要做Cast类型转换,引起很大的性能损耗
param.Add("WorkFlowId", model.WorkFlowId, DbType.AnsiStringFixedLength, ParameterDirection.Input, 12);

同样还有Start参数
而实际上DB里类型为DateTime

我们分析发现：Start = start生成情况如下：
类型：DateTime2而非DateTime
但实际上我们需要的真实类型是DateTime类型

// 同理，如果直接用 Start = start,或者 param.Add("Start", start)则会根据传入的时间是2018-06-01转为Date,是2018-06-01 10:00:00转为DateTime2
// 这样在db中执行查询的时候还需要将Date | DateTime2作Cast转换为DateTime,同样有很大性能损耗)
param.Add("Start", start, DbType.DateTime);

所以在大数据量的时候请注意千万别偷懒，直接使用model作为匿名参数或者直接使用 pa.Add(paramter, value)的方式，切记！切记！切记

下面给出对应的demo示例及运行过程，因为本地电脑里随便插入的几百万记录，可能无法很好体现这个过程，但也非常容易看出差异
总数据量397W笔

其中我们假定查询数据量297W笔,sql server查询+输出24s

   接着我们使用2种方式来比较对应的差异   

   第一种直接使用匿名方式

private static List<TestClass> GetSimpleTestList(TestClass model, DateTime start, DateTime end)
{
      var param = new DynamicParameters(new
      {
            model.WorkFlowId,
            model.WorkCenterId,
            Start = start,
            End = end
       });

       var list = new List<TestClass>();
       using (var conn = new SqlConnection(CONN_STRING))
       {
            var query = conn.Query<TestClass>(SQL_STRING, param);
            if (null != query) list = query.AsList();
       }

       return list;
}

   

   第2种使用精准数据类型及长度方式

private static List<TestClass> GetMatchTestList(TestClass model, DateTime start, DateTime end)
{
    var param = new DynamicParameters();
    // 说明：db中是char(12)故使用DbType.AnsiStringFixedLength,长度12，而不是使用DbType.String这种(这个是varchar&nvarchar
    // 同时长度12则与db中完全一致(char(12)),避免使用其他类型或者长度，而导致的sql server解析的时候还需要做Cast类型转换,引起很大的性能损耗
    param.Add("WorkFlowId", model.WorkFlowId, DbType.AnsiStringFixedLength, ParameterDirection.Input, 12);
    param.Add("WorkCenterId", model.WorkCenterId, DbType.AnsiStringFixedLength, ParameterDirection.Input, 12);
    // 同理，如果直接用 Start = start,或者 param.Add("Start", start)则会根据传入的时间是2018-06-01转为Date,是2018-06-01 10:00:00转为DateTime2
    // 这样在db中执行查询的时候还需要将Date | DateTime2作Cast转换为DateTime,同样有很大性能损耗)
    param.Add("Start", start, DbType.DateTime);
    param.Add("End", end, DbType.DateTime);

    var list = new List<TestClass>();
    using (var conn = new SqlConnection(CONN_STRING))
    {
        var query = conn.Query<TestClass>(SQL_STRING, param);
        if (null != query) list = query.AsList();
    }

    return list;
}

   

  OK，我们来写一个时间测试

static void Main(string[] args)
{
    #region 添加测试数据
    //var list = new List<TestClass>();
    //for (var i = 0; i < 3000000; i++)
    //{
    //    var testClass = new TestClass()
    //    {
    //        WorkFlowId = "A10356987412",
    //        WorkCenterId = "A10236985214",
    //        DateTime = DateTime.Parse("2018-05-10 00:00:00.000")
    //    };
    //    list.Add(testClass);
    //}
    //Insert(list);
    #endregion

    var stopWatch = new Stopwatch();
    stopWatch.Start();

    var model = new TestClass()
    {
        WorkFlowId = "A10356987412",
        WorkCenterId = "A10236985214",
    };
    var start = DateTime.Parse("2018-05-01 10:00:00");
    var end = DateTime.Parse("2018-06-20 10:00:00");


    // 监测普通写法执行时间
    stopWatch.Restart();
    var list = GetSimpleTestList(model, start, end);
    stopWatch.Stop();

    Console.WriteLine("执行GetSimpleTestList()方法返回计算结果条数：{0}，用时{1}毫秒", list.Count, stopWatch.ElapsedMilliseconds);

    // 监测使用正确类型及长度写法执行时间
    stopWatch.Restart();
    list = GetMatchTestList(model, start, end);
    stopWatch.Stop();
    Console.WriteLine("执行GetMatchTestList()方法返回计算结果条数：{0}，用时{1}毫秒", list.Count, stopWatch.ElapsedMilliseconds);

    Console.ReadKey();
}

  

  CTRL + F5查看输出结果：

  

  我们看到使用精准数据类型及长度的方式比匿名直接使用参数+值的方式少了1.8s

  详细代码请在本地址下载 https://gitee.com/jimmyTown_admin_admin/DapperDemo