c# - Linq Where 比 SQL Where 快得多

Question

我是 SQL 的新手，我很难理解为什么这个 Where 语句效率如此低下。

一些关于数据库的背景知识。它是一个用于存储图标的 SQL Compact Edition 数据库。一个项目可以有多个图标，每个图标可以有多个路径，每个路径由一个几何图形、一个颜色和一个不透明度组成。大约有 2000 个图标，这导致大约 12000 条路径。

我正在尝试创建一个只返回属于给定项目的图标的查询

SELECT Icons.Id, Icons.Name... etc FROM Icons
INNER JOIN Projects ON Icons.FK_ProjectId = Projects.Id 
INNER JOIN IconDetails ON Icons.Id = IconDetails.FK_IconId
INNER JOIN Paths ON IconDetails.FK_PathId = Paths.Id
INNER JOIN Colours ON IconDetails.FK_ColourId = Colours.Id
WHERE (Icons.FK_ProjectId = 5)

这大约需要 2.8 秒才能完成。但是，如果我删除底部的 Where 语句，它只需要大约 0.3 秒。然后，我可以使用 C# Linq 选择属于我想要的项目的所有图标。

var iconTable = GetIconDataFromDatabase().Where(e => e.Project == projectName);


private List<IconData> GetIconDataFromDatabase()
{
    var getAllIconsCommand = new SqlCeCommand( // SQL Above);

    return ReturnIconData(LOCAL_CONNECTION_STRING, getAllIconsCommand);
}

private List<IconData> ReturnIconData(string connectionString, SqlCeCommand command)
{
    var IconDataToReturn = new List<IconData>();

    using (var connection = new SqlCeConnection(connectionString))
    {
        command.Connection = connection;

        using (command)
        {
            try
            {
                connection.Open();

                 using (SqlCeDataReader dataReader = command.ExecuteReader())
                 {
                     while (dataReader.Read())
                     {
                         IconDataToReturn.Add(new IconData
                         {
                             Id = int.Parse(dataReader["Id"].ToString().Trim()),
                             Project = dataReader["ProjectName"].ToString().Trim(),
                             Name = dataReader["Name"].ToString().Trim(),
                             Geometry = Geometry.Parse(dataReader["Geometry"].ToString().Trim()),
                             Colour = dataReader["Colour"].ToString().Trim(),
                             Opacity = double.Parse(dataReader["Opacity"].ToString().Trim()),
                             IsPathCompact = bool.Parse(dataReader["Compact"].ToString().Trim()),
                             ZOrder = int.Parse(dataReader["ZOrder"].ToString().Trim())
                       });

                    }
                }  
            }
        }
    return IconDataToReturn;
}

我不明白返回每个图标，然后自己过滤结果怎么会这么快。

Answer 1

对此没有统一的答案。您的性能值将在很大程度上取决于数据库大小和使用的索引等因素。如果您的表中有很多行并且您在 Icons.FK_ProjectId 上有一个索引，该索引非常有选择性(例如，您只选择一百万行中的 10 行)，我怀疑它会更快加载所有行并使用 LINQ 选择，因为数据库可以使用索引进行查找操作(快速)并仅返回行的一小部分(也快速)。

另一方面，如果您没有索引并且选择了很大一部分行(例如 2500 行中的 2000 行)，SQL Server 将首先必须执行聚簇索引扫描，然后它会返回几乎所有行数据集。这个额外的操作会占用执行时间的大部分时间，并且不会显着减少结果集的大小。

你应该做的是比较你的执行计划有没有where，看看你是否可以优化你的查询。在数据库级别进行调整通常优于在客户端进行调整。