使用 linq、外键关系或本地列表哪种模型最快？

Question

一些基础知识

我有两张表，一张保存用户，一张保存登录日志。 用户表包含大约 15000 多个用户，登录表正在增长并达到 150000 多个帖子。 该数据库基于 SQL Server（非 Express）构建。

为了管理用户，我获得了一个从 ObjectDatasource 填充的 gridview（来自 Devexpress 的 ASPxGridView）。

在总结用户登录次数时，我应该了解哪些一般注意事项？

事情变得异常缓慢。

这是一张显示涉及的表的图片。

我尝试了一些方法。

DbDataContext db = new DbDataContext();

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

执行时间：01:29.316（1分29秒）

// 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();
    //...
}

执行时间：01:18.410（1分18秒）

// 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();
    //...
}

执行时间：01:15.821（ 1分15秒）

表现最好的模型实际上是字典。不过，我想知道我想听听的任何选项，以及在处理如此大量的数据时这种编码是否存在“不好”的地方。

谢谢

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

更新了根据 BrokenGlass 示例的模型

// 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));
    //...
}

执行时间：02:01.135（2 分 1 秒）

除此之外，我创建了一个存储简单类的列表

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

，并且在总结方法

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

执行时间：00:36.841（36秒）

结论如此到目前为止，用 linq 进行总结很慢，但我已经到了！

Answer 1

如果您尝试构建一个执行相同操作的 SQL 查询并独立于您的应用程序（在 SQL Server Management Studio 中）执行它，也许这会很有用。类似于：

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

（注意：这仅选择 UserId。如果您想要来自 tblPROUser 的其他字段，则需要在这个基本的“顶部”进行简单的 JOIN查询。）

确保 {UserId, UserLoginId} 上有一个复合索引并且查询计划正在使用它。在索引中并按该顺序包含两个字段可确保您的查询可以在不触及 tblPROUserLogin 表的情况下运行：

然后进行基准测试，看看是否可以获得比 LINQ 代码明显更好的时间：

• 如果是，那么您需要找到一种方法来“哄骗”LINQ 生成类似的查询。
• 如果不是，那么您已经达到了前所未有的速度。

--- EDIT ---

以下 LINQ 代码段相当于上面的查询：

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

在控制台中打印以下文本：

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拥有“整个”用户而不仅仅是 UserId，您可以执行以下操作：

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

控制台输出显示以下查询...

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

...如果您的索引正确，这应该非常有效：

Answer 2

第二种情况应该是迄今为止最快的，只要您删除ToList()，这样计数就可以在数据库端完成，而不是在内存中：

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

此外，您还必须将由于 EF 无法处理对象的映射属性，因此首先将用户 id 转换为“普通”原始变量。

Answer 3

抱歉，因为我不在普通计算机上，所以盲目地这样做。只是几个问题，

• 您在登录表中是否有用户 ID 的索引，
• 您是否尝试过专门为此页面设计的视图？
• 您是使用分页来获取用户，还是尝试一次获取所有计数？
• 您是否运行了 sql profiler 并观察了实际发送的 sql？

这样的事情对你有用吗？

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