SqlServer 大量数据热汇总优化整理

一、大量数据热汇总查询优化

查询性能提升方式：

1.为汇总查询列创建非聚集索引，索引很大的方面提升查询性能

2.尽量避免分组汇总，解决方法将分组汇总改成多次查询

3.使用多线程或者或者说多任务，对汇总查询性能并没有提升。

二、操作示例，测试数据基数20万

1.分组查询代码

public IEnumerable getSummaryTotalDataSourceByCode(out int total, int _start, int pageSize){    var query = DB.Fin_Info.Where(a => a.IsSettlement == true)        .GroupBy(a => new { a.MemberId, a.MemberCode, a.NickName })        .Select(a => new        {            a.Key.MemberCode,            a.Key.MemberId,            a.Key.NickName,            报单费 = a.Where(p => p.TypeName == "报单费").Sum(p => p.Amount),            见点奖 = a.Where(p => p.TypeName == "见点奖").Sum(p => p.Amount),            推荐奖 = a.Where(p => p.TypeName == "推荐奖").Sum(p => p.Amount),            层奖 = a.Where(p => p.TypeName == "层奖").Sum(p => p.Amount),            对碰奖 = a.Where(p => p.TypeName == "对碰奖").Sum(p => p.Amount),            团队奖 = a.Where(p => p.TypeName == "团队奖").Sum(p => p.Amount),            级差奖 = a.Where(p => p.TypeName == "级差奖").Sum(p => p.Amount),            全球奖 = a.Where(p => p.TypeName == "全球奖").Sum(p => p.Amount),            小计 = a.Sum(p => p.Amount),            手续费 = a.Sum(p => p.Poundage),            实际金额 = a.Sum(p => p.RealAmount)        });    var data = query.OrderByDescending(a => a.MemberCode).Skip(_start).Take(pageSize).ToList();    total = data.Count;    if (data.Count >= pageSize)    {        total = query.Count();    }    return data;}

测试结果：

//按会员统计分页/*    * 创建MemberID索引前                创建MemberID索引后          * 调试状态：执行时间大约5秒左右     大约4秒多    * 非调试状态：3秒多                 大约3秒多    */Stopwatch watch = new Stopwatch();watch.Start();//获取分页数据int total = 0;IEnumerable list = DB.Fin_Info.getSummaryTotalDataSourceByCode(out total, 0, 10);Console.WriteLine(list.ToJsonLongDate());watch.Stop();Console.WriteLine($"总共执行秒数：{watch.Elapsed.TotalSeconds}");

2.优化处理代码：

public IEnumerable getByCode(out int total, int _start, int pageSize){    //1.会员表获取会员分页    List<Member_Info> memberList = null;    var memberQuery = DB.Member_Info.Where();    memberQuery = memberQuery.Where(q => q.IsActive == "已激活");    //分页    total = memberQuery.Count();    memberList = memberQuery.OrderByDescending(q => q.CreateTime)        .Skip(_start)        .Take(pageSize)        .ToList();    //2.汇总会员的奖金    List<object> list = new List<object>();    var query = DB.Fin_Info.Where();    query = query.Where(q => q.IsSettlement == true);    foreach (var item in memberList)    {        var a = query.Where(q => q.MemberId == item.MemberId);        list.Add(new        {            MemberCode = item.Code,            MemberId = item.MemberId,            NickName = item.NickName,            报单费 = a.Where(p => p.TypeName == "报单费").Sum(p => p.Amount),            见点奖 = a.Where(p => p.TypeName == "见点奖").Sum(p => p.Amount),            推荐奖 = a.Where(p => p.TypeName == "推荐奖").Sum(p => p.Amount),            层奖 = a.Where(p => p.TypeName == "层奖").Sum(p => p.Amount),            对碰奖 = a.Where(p => p.TypeName == "对碰奖").Sum(p => p.Amount),            团队奖 = a.Where(p => p.TypeName == "团队奖").Sum(p => p.Amount),            级差奖 = a.Where(p => p.TypeName == "级差奖").Sum(p => p.Amount),            全球奖 = a.Where(p => p.TypeName == "全球奖").Sum(p => p.Amount),            小计 = a.Sum(p => p.Amount),            手续费 = a.Sum(p => p.Poundage),            实际金额 = a.Sum(p => p.RealAmount)        });    }    return list;}

测试结果 

//按会员统计分页/* 创建MemberID索引前   创建MemberID索引后    * 调试状态：4秒多      大约2秒多    * 非调试状态:2秒左右   大约0.4秒    */Stopwatch watch = new Stopwatch();watch.Start();//获取分页数据int total = 0;IEnumerable list = DB.Fin_Info.getByCode(out total, 0, 10);Console.WriteLine(list.ToJsonLongDate());watch.Stop();Console.WriteLine($"总共执行秒数：{watch.Elapsed.TotalSeconds}");

三、其他实例 ，包含按日期汇总查询

更多：

EF Attatch()方法附加对象

EF批量操作数据之EntityFramework.Extended

EF 汇总函数使用注意事项Max()/Min()等