CODEWITHSUNDEEP
c#linqperformance
Tom Gullen
Tom Gullen

Reputation: 61773

Linq join efficiency question

// 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.

Upvotes: 0

Views: 353

Answers (3)

Numan
Numan

Reputation: 3948

Do the JOIN!

Save yourself from unnecessary database access, join and get everything you need in a single shot!

Upvotes: 0

Stefan Dragnev
Stefan Dragnev

Reputation: 14493

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.

Upvotes: 3

George Polevoy
George Polevoy

Reputation: 7681

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
 };

Upvotes: 1

Related Questions