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()等