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;
}
ToList()
and then one to figure out how long it's taking to group/orchestrate your data on the server. \$\endgroup\$ – moarboilerplate Feb 23 '15 at 22:23