Performance shifting for SQL Database and Linq memory grouping

Question

I am messing around with shifting between stored procedures and business layer EF getting data rapidly then processing in memory.

This works and on a static data range of 3 months I get execution time of 2.3 seconds

Is there anything I can do to help the compiler increase CPU efficiency or even a better way to get data without worrying about locks, just get it in one quick go, it doesn't change any way?

public static List<DashGraph> GetTimeOverviewData(DateTime start, DateTime end)
 {
 List<DashHelper> totals;
 List<DashHelper> visitors;
 List<DashHelper> visits;
 List<DashHelper> calls;
 Stopwatch sw = new Stopwatch();
 sw.Start();
 //Works like NOLOCK?
 using (new TransactionScope(
 TransactionScopeOption.Required,
 new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted }))
 {
 using (var db = new Track_LOpEntities())
 {
 visitors = db.JTrack_Visitors.AsNoTracking()
 .Where(v => v.CreatedOn >= start & v.CreatedOn <= end)
 .Select(v => new DashHelper() { DateAndTime = v.CreatedOn.Value, Source = 1 })
 .ToList();
 visits = db.JTrack_Visits.AsNoTracking()
 .Where(v => v.DateTimeStart >= start & v.DateTimeStart <= end)
 .Select(v => new DashHelper() { DateAndTime = v.DateTimeStart.Value, Source = 2 })
 .ToList();
 calls = db.JTrack_Calls.AsNoTracking()
 .Where(v => v.calldate >= start & v.calldate <= end)
 .Select(v => new DashHelper() { DateAndTime = v.calldate.Value, Source = 3 })
 .ToList();
 }
 }
 totals = visitors.Concat(visits).Concat(calls).ToList();
 var visitorsGroup = totals.GroupBy(q => new { q.DateAndTime.Date, q.DateAndTime.Hour });
 List<DashGraph> dg = new List<DashGraph>();
 foreach (var g in visitorsGroup)
 {
 dg.Add(new DashGraph()
 {
 Date = g.Key.Date,
 Hour = g.Key.Hour,
 TotalVisitors = g.Where(x => x.Source == 1).Count(),
 TotalPageViews = g.Where(x => x.Source == 2).Count(),
 TotalCalls = g.Where(x => x.Source == 3).Count()
 });
 }
 sw.Stop();
 var exectionTime = TimeSpan.FromMilliseconds(sw.ElapsedMilliseconds);
 return dg;
 }

• moarboilerplate: Where's the main performance loss happening? It may be helpful to have 2 timers, one that tracks everything up until ToList() and then one to figure out how long it's taking to group/orchestrate your data on the server.

Answer 1

It's hard to tell if this will work without trying it but I'd at least give this a shot. By calling ToList on your calls, visits and visitors and then concating them you are losing the database query structure, try this instead:

 using (var db = new Track_LOpEntities())
 {
 var visitors = db.JTrack_Visitors.AsNoTracking()
 .Where(v => v.CreatedOn >= start & v.CreatedOn <= end)
 .Select(v => new DashHelper() { DateAndTime = v.CreatedOn.Value, Source = 1 });
 var visits = db.JTrack_Visits.AsNoTracking()
 .Where(v => v.DateTimeStart >= start & v.DateTimeStart <= end)
 .Select(v => new DashHelper() { DateAndTime = v.DateTimeStart.Value, Source = 2 });
 var calls = db.JTrack_Calls.AsNoTracking()
 .Where(v => v.calldate >= start & v.calldate <= end)
 .Select(v => new DashHelper() { DateAndTime = v.calldate.Value, Source = 3 });
 var totals = visits.Union(calls).Union(visitors);
 var visitorsGroup = totals.GroupBy(q => new { q.DateAndTime.Date, q.DateAndTime.Hour });
 List<DashGraph> dg = new List<DashGraph>();
 foreach (var g in visitorsGroup)
 {
 dg.Add(new DashGraph()
 {
 Date = g.Key.Date,
 Hour = g.Key.Hour,
 TotalVisitors = g.Where(x => x.Source == 1).Count(),
 TotalPageViews = g.Where(x => x.Source == 2).Count(),
 TotalCalls = g.Where(x => x.Source == 3).Count()
 });
 }
 }

Also I'm not sure why you need a transaction scope, your not doing any updates.