Improve search performance in SQL Server through Entity Framework

Question

I'm using SQL Server 2012 Express, Entity Framework 6 and the ASP.NET MVC 5.

In SQL Server I've got a pricelist table with 400 thousand records. The structure of the pricelist is below:

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

Also I've got a price table:

|Id|Price|CurrencyId|

And a currency table:

|Id|Name|Alias|Value|

The Name and Description columns are indexed by a SQL Server full text index.

I need to get 20 records from the pricelist, where Name or Description contain the search query and return it as XML. And I need to get them less then a second, because it's one of the condition from the global search service (it's request timeout is 1 second and I can't change it). This is the structure of a result XML:

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

Where P1, P2 and P3 are prices for a different order's quantity.

I'm using this code to get records:

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

And it took about 2 seconds. How can I improve the performance?

Answer 1

Do not use contains. This is totally not a Entity Framework issue - but SQL.

pricelistRow.Name.Contains(search)

translates into a WHERE [Name] LIKE '%searchterm%'

see the % in front? that means full data scan and an index does not help.

Alternatives:

• StartsWith (LIKE 'searchterm%'), so an index works

• Using a stored procedure (no other way in EF) and then full text index syntax (which EF can not deal with).

But otherwise - that just does not work. Where conditions like that mean - in your case - a full table scan. Period.