c# - 通过 Entity Framework 提高 SQL Server 中的搜索性能

Question

我正在使用 SQL Server 2012 Express、Entity Framework 6 和 ASP.NET MVC 5。

在 SQL Server 中，我有一个包含 40 万条记录的 pricelist 表。价目表的结构如下:

|Id|Manufacturer|Name|Description|StockQuantity|PriceId|

我还有一个价格表:

|Id|Price|CurrencyId|

还有一个currency表:

|Id|Name|Alias|Value|

Name 和 Description 列由 SQL Server 全文索引编制索引。

我需要从 pricelist 中获取 20 条记录，其中 Name 或 Description 包含 搜索查询和将其作为 XML 返回。而且我需要在不到一秒的时间内获取它们，因为它是全局搜索服务的条件之一(它的请求超时为 1 秒，我无法更改它)。这是结果 XML 的结构:

<items>
    <item mfr="PC" Name="Laptop" Description="2.4GHz, etc." StockQuantity="500" P1="100" P2="200" P3="300" Cur="USD"/>
</items>

其中 P1、P2 和 P3 是不同订单数量的价格。

我正在使用这段代码来获取记录:

using (var db = new DatabaseContainer()) {
    db.Configuration.AutoDetectChangesEnabled = false;
    db.Configuration.ValidateOnSaveEnabled = false;
    db.Configuration.LazyLoadingEnabled = false;

    var result = 
        (from pricelistRow in db.EFPricelist
        where pricelistRow.Name.Contains(search) || pricelistRow.Description.Contains(search)
            select new Result {
            Manufacturer = pricelistRow.Manufacturer,
            Name = pricelistRow.Name,
            Description = pricelistRow.Description,
            StockQuantity = pricelistRow.StockQuantity,
            P1 = pricelistRow.EFPricelistRowPrice.Any() ? SqlFunctions.StringConvert(pricelistRow.EFPricelistRowPrice.Min(x => x.Price)) : "",
            P2 = pricelistRow.EFPricelistRowPrice.Count() == 3 ? SqlFunctions.StringConvert(pricelistRow.EFPricelistRowPrice.OrderBy(x => x.Price).Skip(1).FirstOrDefault().Price) : "",
                P3 = pricelistRow.EFPricelistRowPrice.Count() > 1 ? SqlFunctions.StringConvert(pricelistRow.EFPricelistRowPrice.Max(x => x.Price)) : "",
                Cur = pricelistRow.EFPricelistRowPrice.Any() ? pricelistRow.EFPricelistRowPrice.FirstOrDefault().EFCurrency.Alias : ""
                    }).Take(20).ToList();
    return new XmlResult(new Result {
        Items = result
    });
}

大约用了 2 秒。我怎样才能提高性能？

Answer 1

不要使用包含。这完全不是 Entity Framework 问题 - 而是 SQL。

pricelistRow.Name.Contains(search)

转换为 WHERE [Name] LIKE '%searchterm%'

看到前面的%了吗？这意味着完整的数据扫描和索引没有帮助。

备选方案:

• StartsWith(LIKE 'searchterm%')，因此索引有效

• 使用存储过程(在 EF 中没有其他方法)，然后使用全文索引语法(EF 无法处理)。

但除此之外——那是行不通的。这样的条件意味着 - 在您的情况下 - 全表扫描。期间。