LINQ performance over several hundred records
in the following code I have commented out the line that SLOWS my page down. I did some speed test to show that CONTAINS
LINQ expression is the problem.
Does anyone know how to change this line to be more efficient using something else. I am also curious as to why it is so slow.
Any ideas (thanks in advance):
var allWaste = _securityRepository.FindAllWaste(userId, SystemType.W);
var allWasteIndicatorItems = _securityRepository.FindAllWasteIndicatorItems();
// First get all WASTE RECORDS
var searchResults = (from s in allWaste
join x in allWasteIndicatorItems on s.WasteId equals x.WasteId
where (s.Description.Contains(searchText)
&& s.Site.SiteDescription.EndsWith(searchTextSite)
&& (s.CollectedDate >= startDate && s.CollectedDate <= endDate))
&& x.EWC.EndsWith(searchTextEWC)
select s).Distinct();
var results = searchResults.AsEnumerable();
if (hazardous != "-1")
{
// User has requested to filter on Hazardous or Non Hazardous only rather than Show All
var HazardousBoolFiltered = (from we in _db.WasteIndicatorItems
.Join(_db.WasteIndicators, wii => wii.WasteIndicatorId, wi => wi.WasteIndicatorId, (wii, wi) => new { wasteid = wii.WasteId, wasteindicatorid = wii.WasteIndicatorId, hazardtypeid = wi.HazardTypeId })
.Join(_db.HazardTypes, w => w.hazardtypeid, h => h.HazardTypeId, (w, h) => new { wasteid = w.wasteid, hazardous = h.Hazardous })
.GroupBy(g => new { g.wasteid, g.hazardous })
.Where(g => g.Key.hazardous == true && g.Count() >= 1)
select we).AsEnumerable(); // THIS IS FAST
// Now join the 2 object to eliminate all the keys that do not apply
if (bHazardous)
results = (from r in results join x in HazardousBoolFiltered on r.WasteId equals x.Key.wasteid select r).AsEnumerable(); //This is FAST
else
results = (from r in results.Where(x => !HazardousBoolFiltered
.Select(y => y.Key.wasteid).Contains(x.WasteId)) select r).AsEnumerable(); // This is DOG SLOW 10-15 seconds !--- THIS IS SLOWING EXECUTION by 10 times --!
}
return results.AsQueryable();
source to share
Try Any
( MSDN )
Try the following:
results = (from r in results
.Where(x => !HazardousBoolFiltered
.Any(y => y.Key.wasteid == r.WasteId)))
.AsEnumerable()
Or Count
:
results = (from r in results
.Where(x => HazardousBoolFiltered
.Count(y => y.Key.wasteid == r.WasteId) == 0))
.AsEnumerable()
source to share
I suggest using a logging / tracing framework like smart inspect or log4net in conjunction with a debug text writer. http://www.codesprouts.com/post/View-LINQ-To-SQL-Statements-Using-A-Debug-TextWriter.aspx
Another possibility is to use a sql server profiler and see what sql linq2sql produces.
also a very good way is to use the mvc mini profiler combined with a profiled DB connection and SqlFormatters.SqlServerFormatter.
source to share