Linq连接效率问题

发布于 2024-10-27 04:47:42 字数 653 浏览 0 评论 0原文

// Loop each users profile
using (DataClassesDataContext db = new DataClassesDataContext())
{
    var q = (from P in db.tblProfiles orderby P.UserID descending select new { LastUpdated = P.ProfileLastUpdated, UserID = P.UserID }).ToList();
    foreach(var Rec in q){
        string Username = db.tblForumAuthors.SingleOrDefault(author => author.Author_ID == Rec.UserID).Username;
        AddURL(("Users/" + Rec.UserID + "/" + Username), Rec.LastUpdated.Value, ChangeFrequency.daily, 0.4);
    }
}

这是我的站点地图，打印系统上每个用户配置文件的 URL。但是假设我们有 20,000 个用户，用户名查询是否会显着减慢速度？

我习惯于在 SQL 查询中加入联接，但将其与主查询分开并放在循环中似乎效率很低，除非它编译得很好。

原文

// Loop each users profile
using (DataClassesDataContext db = new DataClassesDataContext())
{
    var q = (from P in db.tblProfiles orderby P.UserID descending select new { LastUpdated = P.ProfileLastUpdated, UserID = P.UserID }).ToList();
    foreach(var Rec in q){
        string Username = db.tblForumAuthors.SingleOrDefault(author => author.Author_ID == Rec.UserID).Username;
        AddURL(("Users/" + Rec.UserID + "/" + Username), Rec.LastUpdated.Value, ChangeFrequency.daily, 0.4);
    }
}

This is for my sitemap, printing a URL for each users profile on the system. But say we have 20,000 users, is the Username query going to slow this down significantly?

I'm used to having the join in the SQL query, but having it separated from the main query and in the loop seems like it could be inefficient unless it compiles it well.

分享到QQ

分享到微博

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

发布评论

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

蓝颜夕 2024-11-03 04:47:42

它可能会慢得难以忍受。在您的情况下，这将向数据库发出 20,000 个单独的 SQL 查询。由于查询同步运行，因此每次迭代都会产生服务器通信开销。延迟会累积得相当快。

加入吧。

from P in db.tblProfiles
join A in db.tblForumAuthors on P.UserID equals A.Author_ID
orderby P.UserID descending
select new { LastUpdated = P.ProfileLastUpdated, UserID = P.UserID, Username = A.Username };

顺便说一下，如果缺少 author，SingleOrDefault(...).Username 将抛出 NullReferenceException。最好使用 Single() 或检查你的逻辑。

It will probably be unbearably slow. In your case this will issue 20,000 separate SQL queries to the database. Since the queries run synchronously, you will incur the server communication overhead on each iteration. The delay will accumulate quite fast.

Go with a join.

from P in db.tblProfiles
join A in db.tblForumAuthors on P.UserID equals A.Author_ID
orderby P.UserID descending
select new { LastUpdated = P.ProfileLastUpdated, UserID = P.UserID, Username = A.Username };

By the way, SingleOrDefault(...).Username will throw a NullReferenceException if the author is missing. Better use Single() or check your logic.

回复收藏 0 原文

哽咽笑 2024-11-03 04:47:42

如果在设计 DataContext 时在数据库中正确设置了约束，那么设计者应该在您的 Profile 和 Author 类中生成一对一的成员。
如果没有，您可以在设计器中手动完成。
然后你就可以做这样的事情：

var q =
 from profile in db.tblProfiles
 order by profile.UserID descending
 select new {
  LastUpdated = profile.ProfileLastUpdated,
  profile.UserID,
  profile.Author.Username
 };

If you have constranints set up correctly in your database while designing the DataContext, then the designer should generate a one-to-one members in your Profile and Author classes.
If not, you can do it manually in designer.
Then you will be able to do something like this:

var q =
 from profile in db.tblProfiles
 order by profile.UserID descending
 select new {
  LastUpdated = profile.ProfileLastUpdated,
  profile.UserID,
  profile.Author.Username
 };

回复收藏 0 原文