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

喜欢ヅ旅行 2022-06-13 07:16 168阅读 0赞

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

查询性能提升方式：

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}");

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

![Center][]

[EF Attatch()方法附加对象][EF Attatch]

[EF批量操作数据之EntityFramework.Extended][EF_EntityFramework.Extended]

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

[Center]: /images/20220613/40476f54bebc4d1abeda0115638cae23.png
[EF Attatch]: http://blog.csdn.net/u011127019/article/details/73381460
[EF_EntityFramework.Extended]: http://blog.csdn.net/u011127019/article/details/71538350
[EF _Max_Min]: http://blog.csdn.net/u011127019/article/details/61413670