带有连接和组LINQ生成大量查询，速度很慢，

ttygqcqt 于 2022-12-06 发布在其他

关注(0)|答案(2)|浏览(131)

我的实体结构如下：

public class DisbursementItem
{
    public int DisbursementNumber;
    public int IDDisbursementItem;
    public int IDReceiptItem;
    public decimal Amount;
    public decimal MeasureUnit;
    public decimal PricePerMU;
    public decimal PriceTotal;
    public Disbursement Disbursement_IDDisbursement;
    public int IDDisbursementNumber;
}

public class Disbursement
{
    public int DisbursementNumber;
    DateTime date;
    DisbursementType DType;
    string Note;
    string Subscriber;
    Subscriber SubscriberModel;
    string ItemType;
    int ProcessNumber;
}

public class Subscriber 
{
    public string Name
    public string Address;
    public string City;
}

public class DisbursementDescription
{
    public int IDDisbursementItem;
    public string Description;
}

public class Receipt
{
    public int IDReceiptItem;
    public int ItemNumber;
}

public class StorageCard 
{
    public int ItemNumber;
    public string StorageCardGroup;
    public string StorageCardName;
}

我的EF6 LINQ查询是：
第一次
这就是我想在EF中实现的SQL查询
对于几百行，这个查询可能需要一分钟以上的时间。我该如何优化它呢？我怀疑多个联接是个问题，可能还有一些字段的Sum。
也无法修改数据库模式。
它生成的查询是巨大的。它就像一个SELECT在SELECT在SELECT的大约40倍。

linq

来源：https://stackoverflow.com/questions/73019187/linq-with-joins-and-group-generates-enormous-query-and-is-slow

2条答案

按热度按时间

e0bqpujr1#

最简单的方法是将结果所需的所有字段添加到分组关键字中。为提高可读性和可维护性，对Query语法进行了重写：

DateTime from;
DateTime to;

var query = 
    from di in context.DisbursementItem
    where di.Disbursement_IDDisbursement.Date <= to && di.Disbursement_IDDisbursement.Date >= from
    join dd in context.DisbursementDescription on di.IDDisbursementItem equals dd.IDDisbursementItem
    join r in context.Receipt on di.IDReceiptItem equals r.IDReceiptItem
    join sc in context.StorageCard on r.ItemNumber equals sc.ItemNumber
    group di by new 
    {
        di.DisbursementNumber, 
        sc.ItemNumber, 
        di.MeasureUnit, 
        di.Disbursement_IDDisbursement.Date, 
        di.Disbursement_IDDisbursement.DType, 
        di.Disbursement_IDDisbursement.Note,
        Subscriber = di.Disbursement_IDDisbursement.Subscriber,
        SubscriberName = di.Disbursement_IDDisbursement.SubscriberModel.Name,
        SubscriberAddress = di.Disbursement_IDDisbursement.SubscriberModel.Address,
        SubscriberCity = di.Disbursement_IDDisbursement.SubscriberModel.City,
        sc.ItemNumber,
        sc.StorageCardGroup,
        sc.StorageCardName,
        di.PricePerMU,
        Desc = dd.Description
    } into g
    select new 
    {
        g.Key.Date,
        g.Key.DisbursementNumber,
        g.Key.DType,
        g.Key.Note,
        g.Key.Subscriber,
        g.Key.SubscriberName,
        g.Key.SubscriberAddress,
        g.Key.SubscriberCity,
        g.Key.ItemNumber,
        g.Key.StorageCardGroup,
        g.Key.StorageCardName,
        g.Key.PricePerMU,
        g.Key.MeasureUnit,
        g.Key.Desc,

        Amount = g.Sum(x => x.Amount),
        PriceTotal = g.Sum(x => x.PriceTotal)        
    }

赞(0）回复(0）举报 2022-12-06

dzhpxtsq2#

你可以尝试一些多线程技术
可以将查询拆分为多个部分，并将每个部分分配给一个任务。在这里，您应该可以找到一些有用的内容（并行部分）：
https://learn.microsoft.com/en-us/dotnet/api/system.threading.tasks?view=net-6.0

赞(0）回复(0）举报 2022-12-06