Which model is the fastest using linq bindings, foreign keys, or local lists?

Question

Some basics

I have two tables, one of which contains users, and the other a log with logins. The user table contains approximately 15,000 users, the login table grows and reaches 150,000+ messages. The database is built on SQL Server (not express).

To manage users, I got a gridview (ASPxGridView from Devexpress), which I populate from ObjectDatasource.

Are there any common dos and donts that I should know about when summing up the number of logins made by the user.

Things are becoming strangely slow.

Here is an image showing the tables involved.

Ive tried a few things.

DbDataContext db = new DbDataContext(); // Using foregin key relationship foreach (var proUser in db.tblPROUsers) { var count = proUser.tblPROUserLogins.Count; //... } 

Lead Time: 01: 29.316 (1 minute and 29 seconds)

 // By storing a list in a local variable (I removed the FK relation) var userLogins = db.tblPROUserLogins.ToList(); foreach (var proUser in db.tblPROUsers) { var count = userLogins.Where(x => x.UserId.Equals(proUser.UserId)).Count(); //... } 

Lead time: 01: 18.410 (1 minute and 18 seconds)

 // By storing a dictionary in a local variable (I removed the FK relation) var userLogins = db.tblPROUserLogins.ToDictionary(x => x.UserLoginId, x => x.UserId); foreach (var proUser in db.tblPROUsers) { var count = userLogins.Where(x => x.Value.Equals(proUser.UserId)).Count(); //... } 

Lead time: 01: 15,821 (1 minute and 15 seconds)

The best performance model is actually a dictionary. However, I know of any options that I would like to hear about this, also if there is something “bad” with this type of encoding when processing such large amounts of data.

thanks

==================================================== ======

UPDATED With model according to BrokenGlass example

 // By storing a dictionary in a local variable (I removed the FK relation) foreach (var proUser in db.tblPROUsers) { var userId = proUser.UserId; var count = db.tblPROUserLogins.Count(x => x.UserId.Equals(userId)); //... } 

Lead time: 02: 01.135 (2 minutes and 1 second)

In addition to this, I created a list in which a simple class was stored

 public class LoginCount { public int UserId { get; set; } public int Count { get; set; } } 

And in the summation method

 var loginCount = new List<LoginCount>(); // This foreach loop takes approx 30 secs foreach (var login in db.tblPROUserLogins) { var userId = login.UserId; // Check if available var existing = loginCount.Where(x => x.UserId.Equals(userId)).FirstOrDefault(); if (existing != null) existing.Count++; else loginCount.Add(new LoginCount{UserId = userId, Count = 1}); } // Calling it foreach (var proUser in tblProUser) { var user = proUser; var userId = user.UserId; // Count logins var count = 0; var loginCounter = loginCount.Where(x => x.UserId.Equals(userId)).FirstOrDefault(); if(loginCounter != null) count = loginCounter.Count; //... } 

Lead Time: 00: 36.841 (36 seconds)

Conclusion so far, summing with linq is slow, but Im getting there!

Answer 1

Perhaps it would be useful if you tried to build an SQL query that does the same and executes it regardless of your application (in SQL Server Management Studio). Sort of:

 SELECT UserId, COUNT(UserLoginId) FROM tblPROUserLogin GROUP BY UserId 

(NOTE: This simply selects UserId . If you need other fields from tblPROUser , you will need a simple JOIN "on top" of this basic request.)

Make sure that {UserId, UserLoginId} has a composite index used by the query plan. The presence of both fields in the index and in this order ensures that your query can work without touching the tblPROUserLogin table:

Then check and see if you can get a significantly better time than your LINQ code:

• If so, then you need to find a way to “coax” LINQ to create a similar query.
• If not, then you are as fast as you can.

--- EDIT ---

The LINQ description snippet is equivalent to the query above:

 var db = new UserLoginDataContext(); db.Log = Console.Out; var result = from user_login in db.tblPROUserLogins group user_login by user_login.UserId into g select new { UserId = g.Key, Count = g.Count() }; foreach (var row in result) { int user_id = row.UserId; int count = row.Count; // ... } 

What the following text prints in the console:

 SELECT COUNT(*) AS [Count], [t0].[UserId] FROM [dbo].[tblPROUserLogin] AS [t0] GROUP BY [t0].[UserId] -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1 

--- EDIT 2 ---

To have a “total” user, not just a UserId , you can do this:

 var db = new UserLoginDataContext(); db.Log = Console.Out; var login_counts = from user_login in db.tblPROUserLogins group user_login by user_login.UserId into g select new { UserId = g.Key, Count = g.Count() }; var result = from user in db.tblPROUsers join login_count in login_counts on user.UserId equals login_count.UserId select new { User = user, Count = login_count.Count }; foreach (var row in result) { tblPROUser user = row.User; int count = row.Count; // ... } 

And the console output shows the following query ...

 SELECT [t0].[UserId], [t0].[UserGuid], [t0].[CompanyId], [t0].[UserName], [t0].[UserPassword], [t2].[value] AS [Count] FROM [dbo].[tblPROUser] AS [t0] INNER JOIN ( SELECT COUNT(*) AS [value], [t1].[UserId] FROM [dbo].[tblPROUserLogin] AS [t1] GROUP BY [t1].[UserId] ) AS [t2] ON [t0].[UserId] = [t2].[UserId] -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1 

... which should be very effective if your indexes are correct:

Answer 2

The second case should always be the fastest if you release ToList() , so the calculation can be done on the database side, and not in memory:

 var userId = proUser.UserId; var count = db.tblPROUserLogins.Count(x => x.UserId == userId); 

Also, you must first enter the user ID in a “regular” primitive variable, since EF cannot deal with object matching properties.

Answer 3

Sorry, I'm doing it blind, because I'm not on my regular computer. Just a few questions.

• You have an index for the user ID in the login table.
• Have you tried a look specially created for this page?
• Are you using paging to get users, or trying to get all bills at once?
• Do you run the sql profiler and watch how the actual sql is sent?

Does something like this work for you?

 var allOfIt = from c in db.tblProUsers select new { User = c, Count = db.tblProUserLogins.Count(l => l.UserId == c.UserId) } .Skip(pageSize * pageNumber) .Take(pageSize) // page size