EF 提高多对多 / .Include .ThenInclude 的性能
我有一个相对基本的模型 - 用户和标签。有一个固定的标签列表。一个用户可以有多个标签，一个标签可以被多个用户使用。
我采用了下面的结构，并在返回结果时发现了性能问题。
public class User
{
public string Id {get; set;}
public virtual List<UserTag> UserTags {get; set}
}
public class UserTag
{
public string UserId { get; set; }
public User User { get; set; }
public int TagId { get; set; }
public Tag Tag{ get; set; }
}
public class Tag
{
[Key]
public int TagId { get; set; }
public string Name { get; set; }
public virtual List<UserTag> UserTags { get; set; }
}
我有以下查询，该查询需要很长时间（几秒钟）：
var x = db.Users.Include(u => u.UserTags).ThenInclude(u => u.Trait).ToList<User>();
我尝试这样编写它，这提高了时间，但仍然花费太长时间：
db.UserTags.Load();
db.Tags.Load();
var x = db.Users.ToList<User>();
还有其他方法可以加快速度吗？直接在 SQL SMS 中运行查询几乎是即时的（例如，
select * from Users u left outer join UserTags t on t.UserId = u.Id)
就数据行而言，它是 apx Tags: 100, UserTags:50,000, Users: 5,000
I've got a relatively basic model - Users and Tags. There is a fixed list of Tags. A User can have multiple Tags and a Tag can be used by multiple users.
I had gone with structure below and finding performance issues when returning results.
public class User
{
public string Id {get; set;}
public virtual List<UserTag> UserTags {get; set}
}
public class UserTag
{
public string UserId { get; set; }
public User User { get; set; }
public int TagId { get; set; }
public Tag Tag{ get; set; }
}
public class Tag
{
[Key]
public int TagId { get; set; }
public string Name { get; set; }
public virtual List<UserTag> UserTags { get; set; }
}
I have the following query which is takings a long time (several seconds):
var x = db.Users.Include(u => u.UserTags).ThenInclude(u => u.Trait).ToList<User>();
I have tried writing it as such, which has improved the time, however it is still taking too long:
db.UserTags.Load();
db.Tags.Load();
var x = db.Users.ToList<User>();
Is there any other way to speed this up? Running a query directly in SQL SMS is almost instant (e.g.
select * from Users u left outer join UserTags t on t.UserId = u.Id)
In terms of data rows, it is apx Tags: 100, UserTags:50,000, Users: 5,000
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
首先，您可以检查 EF 如何将您的请求转换为 SQL Server - 因此使用“SQL Server Profiler”
然后您可以使用生成的查询来检查是否可能缺少索引以加快查询速度
您也可以尝试编写一个 Join而不是 然后包含 并查看查询的行为方式
最好的问候
吉米
First you can check how EF translates your request to SQL Server - therefore use the "SQL Server Profiler"
Then you could use the genereated query to check if there might be an missing index which speeds up the query
You also can try to write a Join instead of ThenInclude and see how the query then behaves
best regards
Jimmy