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;
        }

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.