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