CODEWITHSUNDEEP
asp.net-mvcperformancelinq
foop
foop

Reputation: 523

Linq Query Optimization joining of tables

So I have a linq query, where s1.code comes from an object before this linq query. 

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

I have the same block of code but this time in my where clause, I changed the filter from using t1.Province == s1.Code to cf.StateCode == s1.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
         };

Now the first query runs 10 times as fast as the second one.
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.

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.

Any thoughts?

Upvotes: 1

Views: 377

Answers (1)

G. Stoynev
G. Stoynev

Reputation: 7783

Most likely the two different columns t1.Province and cf.StateCode are indexed differently in underlying data source (or one isn't at all)

Upvotes: 3

Related Questions