使用大型记录集优化实体框架查询

本文关键字：实体 框架 查询 优化 大型 记录 | 更新日期: 2023-09-27 18:33:58

我需要优化以下EF查询，因为最初我从CustomerWidgets表中带回所有内容。 现在该表有 100,000 行，它开始变慢。 我正在查询一个sql服务器dbase。

我想我不需要先将所有内容都带回来，那么修改以下代码的最佳方法是什么？

        public List<CustomerWidget> SearchWidgets(string surname, string firstname, string ZipCode)
    {
        // i don't need to bring back everything here!!  optimize it I say!!
        var widgetSearchResults = _context.CustomerWidgets.Where(x => x.IsDeleted == false).ToList();
        if (!string.IsNullOrEmpty(surname))
        {
            widgetSearchResults =
                widgetSearchResults.Where(x => x.Surname.ToUpper().Contains(surname.ToUpper())).ToList();
        }

        if (!string.IsNullOrEmpty(firstname))
        {
            widgetSearchResults =
                widgetSearchResults.Where(x => x.Forename.ToUpper().Contains(firstname.ToUpper())).ToList();
        }
        if (!string.IsNullOrEmpty(ZipCode))
        {
            widgetSearchResults =
                widgetSearchResults.Where(
                    x => x.ZipCode.Replace(" ", "").ToUpper().Contains(ZipCode.Replace(" ", "").ToUpper()))
                    .ToList();
        }
        return widgetSearchResults;
    }

使用大型记录集优化实体框架查询

仅在末尾调用ToList()，并在之前使用IQueryable：

public List<CustomerWidget> SearchWidgets(string surname, string firstname, string ZipCode)
{
    var widgetSearchResults = _context.CustomerWidgets.Where(x => x.IsDeleted == false);
    if (!string.IsNullOrEmpty(surname))
    {
        widgetSearchResults =
            widgetSearchResults.Where(x => x.Surname.ToUpper().Contains(surname.ToUpper()));
    }

    if (!string.IsNullOrEmpty(firstname))
    {
        widgetSearchResults =
            widgetSearchResults.Where(x => x.Forename.ToUpper().Contains(firstname.ToUpper()));
    }
    if (!string.IsNullOrEmpty(ZipCode))
    {
        widgetSearchResults =
            widgetSearchResults.Where(
                x => x.ZipCode.Replace(" ", "").ToUpper().Contains(ZipCode.Replace(" ", "").ToUpper()));
    }
    /********************
      Call ToList() only here
      *******************/
    return widgetSearchResults.ToList();
}

EF 在实际从数据库中检索元素时构造整个 SQL 查询，因此使用你的方法，首先检索：

SELECT * FROM CustomerWidgets WHERE IsDeleted = false

然后，其余查询将从内存中列表(100.000 个项目(执行。

通过删除ToList()调用直到最后，您可以使用一个IQueryable，它只是添加Where子句，并且仅在您调用ToList()时才被构造和检索，因此您的SQL查询最终如下所示：

SELECT * FROM CustomerWidgets WHERE 
           IsDeleted = false 
       AND UPPER(Surname) LIKE UPPER('%Something%')
       AND UPPER(FirstName) LIKE UPPER('%Something%')
       AND UPPER(ZipCode) LIKE UPPER('%Something%') -- plus all the replacing

哪个应该只检索要查询的确切项目

为了进行额外的优化，您可能希望删除所有这些ToUpper并在SQL服务器中设置不区分大小写的排序规则(名称中带有CI的排序规则(：使用正确的索引，这应该比在查询中将字符串转换为大写要快得多。

此外，您的ZipCode替换(对于" " ""(最好退出Where子句，因此您可以执行以下操作：

    if (!string.IsNullOrEmpty(ZipCode))
    {
        ZipCode = ZipCode.Replace(" ", "");
        widgetSearchResults =
            widgetSearchResults.Where(
                x => x.ZipCode.Replace(" ", "").ToUpper().Contains(ZipCode.ToUpper()));
    }

这仍将在列 tho 上调用 REPLACE SQL 函数(而不是在 where 子句上(。您不能替换那里的任何内容：SQL Server 检查额外的空格比为每一行调用函数更快。但警告：这可能会改变功能：使用您当前的代码，"01 05"将与"0 105"匹配;如果没有替换，"01 05"将仅匹配"01 05"。如果这对您不起作用，请保留替换项。

PS：我还没有测试您的查询是否会直接转换为 SQL Server(如果没有，可能会引发异常(......乍一看，我认为他们应该转换得很好，但如果他们不这样做，首先解决这些表达应该很容易。

除此之外，如果您的应用程序允许，如果您想一次检索较少的元素，您可以使用分页(在 Linq 中使用 Skip 和 Take...但我看不到您可以采取的进一步优化。

回顾一下：

  1. 使用 WHERE 子句构造查询，而不是检索所有查询，然后在内存中筛选。这是通过在末尾调用ToList()来完成的
  2. 删除所有不必要的每行函数(在本例中具体为ToUpper(。如果可以更改列排序规则，SQL Server 将执行不区分大小写的搜索。
  3. 无需在记录和查询子句上将" "替换为""。如果你不这样做，它会更快。不过，请检查答案文本上的警告。
  4. 在 SQL Server 上建立正确的索引
  5. 如果这还不够，并且应用程序支持分页，请使用分页
相关文章：