LINQ查询优化 [英] LINQ query optimization

问题描述

var result= from VD in VisitDetails 
join PB in PatientBill on VD.VisitId equal PB.VisitId 
group new
{
PB
}
by new
{
VD.VisitCode
}
into data
select new
{
VisitCode= data.Key.VisitCode,
PatientPayable= data.sum(x=> x.PB.PatientPayable),
CompanyPayable= data.sum(x=> x.PB.CompanyPayable),
NeyPayable= data.Sum(item=> item.PB.PatientBillDetails.Sum(x=> x.NetPayable)
}

我的尝试：



我从这个查询得到了正确的输出，但由于NetPayable Calculation，它很慢。所以请帮我优化这个查询。

What I have tried:

I am getting correct output from this query but it's slow because of NetPayable Calculation. So please help me to optimize this query.

推荐答案

为了扩展Ehsan Sajjad在评论中所说的内容，有时为了提高性能，你必须做一些预先计算而不是离开这一切都要在最后一刻计算出来。这样你就可以从计算成本的时间摊销中受益。



所以这里建议只在PatientBill级别优化NetPayable，这可以很容易地完成没有添加新表。相反，只需在现有的PatientBill表中添加一个新列，并在每次添加/更改特定PatientBill记录的PatientBillDetails时添加几个步骤，如下所述：



- 添加RunningNetPayable列，该列将是PatientBillDetails项目'NetPayable的运行总计。

- 每当对PatientBill的PatientBillDetails记录中的条目进行插入或更改时，您必须更新此RunningNetPayable值，以便它是组成PatientBillDetails的NetPayable的总和。因此，在插入/更改PatientBillDetails的代码中，还要更新PatientBill的RunningNetPayable - 或者 - 如果您的数据库支持触发器，您也可以使用它来为您执行此操作。

- 然后你可以使用一个稍微修改过的查询：

To expand on what Ehsan Sajjad said in his comment, sometimes to improve performance you have to do some precalculations instead of leaving it all to be calculated at the very last moment. This way you benefit from amortization of time it costs to do that calculation.

So here's a suggestion for optimizing only NetPayable at the PatientBill level, which can be done quite easily without adding a new table. Instead just add a new column to your existing PatientBill table and a few steps whenever you add/change the PatientBillDetails for a particular PatientBill record as described in more detail here:

- Add "RunningNetPayable" column, which will be the running total of the PatientBillDetails items' NetPayable.
- You must update this "RunningNetPayable" value whenever an insert or change is made to an entry in your PatientBill's PatientBillDetails records, such that it is the sum of the constituent PatientBillDetails's NetPayable. So at the code that inserts/changes the PatientBillDetails, also make it update the PatientBill's "RunningNetPayable" -or- if your database supports 'triggers' you could make use of it to do this for you too.
- Then you can use a slightly modified query:

var result= from VD in VisitDetails 
join PB in PatientBill on VD.VisitId equal PB.VisitId 
group new
{
PB
}
by new
{
VD.VisitCode
}
into data
select new
{
VisitCode= data.Key.VisitCode,
PatientPayable= data.sum(x=> x.PB.PatientPayable),
CompanyPayable= data.sum(x=> x.PB.CompanyPayable),
NetPayable= data.Sum(item=> item.PB.RunningNetPayable)
}

但是，如果你想让它更快。您可以随时添加/更改值并预先计算所有值（PatientPayable，CompanyPaybable和NetPayable），并将其存储在单独的表中，如Ehsan Sajjad所说。

However, if you want to make it even faster. You could precalculate everything (PatientPayable, CompanyPaybable and NetPayable) anytime values are added/changed and stored in a separate table, like Ehsan Sajjad said.

请参阅我之前的回答： linq查询中的详细信息表总和 [ ^ ]

Please, see my previous answer: Detail table sum in linq query[^]

这篇关于LINQ查询优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！