StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POLinq Query Optimization joining of tables
text
Body
copied!<p>So I have a linq query, where <code>s1.code</code> comes from an object before this linq query. </p> <pre><code>var q1 = from cf in db.Control_Franchises join t1 in db.Territories on SqlFunctions.StringConvert((double)cf.FranchiseID).Trim() equals t1.FranchiseID.Trim() join cu in db.Control_Users on t1.FK_CompanyID equals cu.PrimaryCompanyID join u in db.Users on cu.UserID.ToLower() equals u.Username.ToLower() where cf.Status == "ACTIVE" && cf.FranchiseID > 1000 && cu.UserType == "Franchisee" && cu.Status == "ACTIVE" && t1.Province == s1.Code orderby cu.LastName ascending, cf.FranchiseID ascending select new { FranchiseId = cf.FranchiseID, Province = cf.StateCode, DisplayName = cu.LastName + ", " + cu.FirstName, UserId = u.PK_UserID }; </code></pre> <p>I have the same block of code but this time in my where clause, I changed the filter from using <code>t1.Province == s1.Code</code> to <code>cf.StateCode == s1.Code</code></p> <pre><code>var q1 = from cf in db.Control_Franchises join t1 in db.Territories on SqlFunctions.StringConvert((double)cf.FranchiseID).Trim() equals t1.FranchiseID.Trim() join cu in db.Control_Users on t1.FK_CompanyID equals cu.PrimaryCompanyID join u in db.Users on cu.UserID.ToLower() equals u.Username.ToLower() where cf.Status == "ACTIVE" && cf.FranchiseID > 1000 && cu.UserType == "Franchisee" && cu.Status == "ACTIVE" && cf.StateCode == s1.Code // DIFFERENT FROM ABOVE orderby cu.LastName ascending, cf.FranchiseID ascending select new { FranchiseId = cf.FranchiseID, Province = cf.StateCode, DisplayName = cu.LastName + ", " + cu.FirstName, UserId = u.PK_UserID }; </code></pre> <p>Now the first query runs 10 times as fast as the second one.<br> How I am measuring the speed though, is the load time of my Edit Page, which in turn is loading a mvc tree view of the nodes. This query as well portions of a different query.</p> <p>Now I'm trying to understand why my first query loads much faster and the only reasoning I can think of is because I am doing a condition on a table that will be joined, so rather then joining the entire "Territories" table with "Control_Franchises" I am only joining a portion of the "Territories" table. </p> <p>Any thoughts?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload