2
\$\begingroup\$

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;
        }
\$\endgroup\$
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – moarboilerplate Feb 23 '15 at 22:23
2
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.