英文:
Why does AsNoTracking is increasing the loading time instead of improving the joins operation on large dataset in LinqToSQL c#?
问题
我有一个查询，它与一个包含数百万条记录的表进行左连接，未来可能会翻倍。它开始需要大约6-7秒来响应。我尝试使用AsNoTracking，但奇怪的是，它增加了加载时间，而不是改善它。
现在，我已经尽量缩小范围，并在查阅文章后发现AsNoTracking()会加速查询，但实际上它增加了加载时间。我还有哪些其他选项？
RequiredDataMapper表是一个桥梁表，用于维护RequiredData和RequiredDataMapper之间的多对多关系。
英文:
I have a query that takes a left join with a table that contains million of records and could double even in a future. It has started to take around 6-7 seconds to respond. I tried using the AsNoTracking but weirdly it increases the load time instead of improving.
var res = (from rd in db.RequiredData
join rdm in db.RequiredDataMapper
on new { t1 = rd.Id } equals new { t1 = rdm.Id }
into rd_rdm
from rdm in rd_rdm.DefaultIfEmpty()
where (rdm.Name == null || rdm.Name == nameof(Name))
&& (model.TypeId == 0 || rdm.Id == model.STypeId)
&& (ApplicationType.ToLower() != "QR".ToLower() ?
rdm.ATypes.TypeName.ToLower() != "QR".ToLower() : 1 == 1)
select rd)
.OrderByDescending(s => s.date)
.AsNoTracking();
Now, I have narrowed it down as much as I could and after going through articles I found out that AsNoTracking() would fast it up but it has increased it's load time even more. What other options I have?
RequiredDataMapper table is a bridge table to maintain many to many relationships between RequiredData and RequiredDataMapper.
答案1
得分: 0
以下是翻译好的部分：
1 - 我会获取查询的原始版本，如Select *
2 - 我会在MSSQL上运行这个查询，看看是否需要很长时间。
3 - 如果查询需要很长时间，如果读取脏数据不是问题的话，我会应用WITH(NOLOCK)。在Ef core中实现它有点困难。您必须将其视为隔离级别。
4 - 我会查看查询的执行计划。数据来自哪里会成为瓶颈。相应地，我会切换到索引操作。一般来说，执行计划会警告您是否缺少索引，并提供一个示例脚本。
5 - 如果到目前为止我所考虑的都没有问题，那么在查询进行时使用的索引可能会分散。您可以通过重新构建或重组索引来修复索引。
英文:
Here's what I would do if I were you. I'm assuming you're using SQL.
1 - I would get the raw version of the query like Select *
2 - I would run this on MSSQL and see if it takes really long.
3 - If it takes a long time, if it is not a problem to read dirty data, I would apply WITH(NOLOCK) It is a bit difficult to implement in Ef core. You have to consider it as Isolation level.
4 - I would look at the execution plan of the query. Where the data comes from is bottlenecked. Accordingly, I would switch to indexing operations. In general, the execution plan warns you if there is a missing index and gives an example script.
5 - If there is no problem with what I have counted so far, the indexes used while the query is coming may be fragmented. You can have the index rebuilt by Rebuild or Reorganize.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论