Improving Search Efficiency in SQL Server through the Entity Framework

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

In SQL Server, I have a table pricelistwith 400 thousand records. The structure of the price list is below:

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

I also have a table price:

|Id|Price|CurrencyId|

And the table currency:

|Id|Name|Alias|Value|

Columns Nameand Descriptionindexed by SQL Server full text index.

I need to get 20 entries from pricelistwhere Nameor Description contain a search query and return it as XML. And I need to get them in less than a second, because this is one of the conditions of the global search service (the request timeout is 1 second, and I cannot change it). This is the structure of the XML result:

<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 . ?

+3
1

. Entity Framework, SQL.

pricelistRow.Name.Contains()

WHERE [Name] LIKE '% searchterm%'

% ? , .

:

  • (LIKE 'searchterm%'),

  • ( EF), ( EF ).

- . , - - . .

+4

All Articles