如何提高此EF代码的速度？

发布于 2025-02-12 04:40:37 字数 720 浏览 0 评论 0原文

我正在填写trperday词典，具有〜2700的值，需要几秒钟。这些数据库查询可以通过某种方式优化吗？

        // transactions per day
        SortedDictionary<DateTime, int> trPerDay = new SortedDictionary<DateTime, int>();

        foreach (Order order in orders)
        {
            foreach (OrderDetail od in order.OrderDetails)
            {
                int qty = CommonUtils.ComputeQuantity(od);

                DateTime date = order.date.Date;

                if (!trPerDay.ContainsKey(date))
                {
                    trPerDay.Add(date, qty);
                }
                else
                {
                    trPerDay[date] += qty;
                }
            }
        }

原文

I'm filling the trPerDay dictionary with ~2700 values and it takes several seconds. Can these DB queries be optimized in some way?

        // transactions per day
        SortedDictionary<DateTime, int> trPerDay = new SortedDictionary<DateTime, int>();

        foreach (Order order in orders)
        {
            foreach (OrderDetail od in order.OrderDetails)
            {
                int qty = CommonUtils.ComputeQuantity(od);

                DateTime date = order.date.Date;

                if (!trPerDay.ContainsKey(date))
                {
                    trPerDay.Add(date, qty);
                }
                else
                {
                    trPerDay[date] += qty;
                }
            }
        }

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

养猫人 2025-02-19 04:40:37

我知道您想按日期获得订单详细信息

orders.GroupBy(x => x.date.Date)
.Select(x => new { date = x.Key, list = x.ToList().Sum(y => y.OrderDetails.Sum(od => CommonUtils.ComputeQuantity(od))))})

I understand that you want to get order details count by date

orders.GroupBy(x => x.date.Date)
.Select(x => new { date = x.Key, list = x.ToList().Sum(y => y.OrderDetails.Sum(od => CommonUtils.ComputeQuantity(od))))})

回复收藏 0 原文

近箐 2025-02-19 04:40:37

最低限度，当您加载订单时，请确保急切地加载OrderDetails，以避免每次订单的懒惰负载。这是假设您的计算性非常复杂，并且不能将其表达为LINQ计算。

如果可以计算LINQ表达式中的数量，则可以通过执行groupby来大大改进查询性能。例如：

var quantitiesPerDay = _context.Orders
    .GroupBy(o => DbFunctions.TruncateTime(o.date))
    .ToDictionary(g => g.Key, 
        g.Sum(o => o.SelectMany(x => x.OrderDetails
            .Where(od => /* Linq logic to determine what to include */)
            .Quantity)));

var trPerDay = new SortedDictionary(quantitiesPerDay);

这里的捕获是，必须在 expression expression中表达订单详细信息的逻辑，而无需执行诸如声明c＃函数以计算它之类的事情。 EF必须能够将表达式转换为SQL。这看起来不像将其分为单独的方法那样“干净”，但是差异允许数据库进行计算，可能是下秒的时间与秒或更高（随着系统的大小增长）进行计算在记忆中获取后，所有适用的订单和订单详细信息首先进入内存。

At a bare minimum, when you load orders, be sure to eager load the OrderDetails to avoid a lazy load hit for each order. This assumes that your ComputeQuantity is prohibitively complex and cannot be expressed down into a Linq calculation.

If it is possible to compute the Quantity within a linq expression, the query performance can be drastically improved by doing a GroupBy. For example:

var quantitiesPerDay = _context.Orders
    .GroupBy(o => DbFunctions.TruncateTime(o.date))
    .ToDictionary(g => g.Key, 
        g.Sum(o => o.SelectMany(x => x.OrderDetails
            .Where(od => /* Linq logic to determine what to include */)
            .Quantity)));

var trPerDay = new SortedDictionary(quantitiesPerDay);

The catch here is that the logic to determine what order details to include has to be expressed in a Where expression without doing something like declaring a C# function to compute it. EF has to be able to translate the expression down to SQL. This may not look as "clean" as separating it off into a separate method, but the difference is allowing the database do do the computation is likely sub-second time vs. seconds or more (as the system grows in size) doing the computation in memory after fetching all applicable orders and order details into memory first.

回复收藏 0 原文

~没有更多了~