Improve search performance in SQL Server through Entity Framework
asp.net-mvc, c#, entity-framework, sql, sql-server
Solution
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.
Problem
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?