Lab Lab Lab Lab - 3 years ago 55
ASP.NET (C#) Question

How can I simplify (speed up) the selecting query from a database that contains more than 1 million records using LINQ

I have two models in my application: Clients from where the client name is taken and Payments where the information about purchase is taken from. In the result I get the list of every client's purchases in the time intervals -

fromDate
and
toDate
. But all this process takes too much time. Because the client's db is around a 1.500 records and payments = 0.5 mln. So how can I speed up this process?

public async Task<List<SomeModel>> SomeMethod(DateTime? fromDate, DateTime? toDate)
{
var clients = await _db.Clients.ToListAsync();

var totals = new List<SomeModel>();

foreach (var client in clients)
{
var payment = await _db.Payments.Where(pay => pay.ClientId == client.Id).Where(
p =>
DateTime.Compare(p.TradeDate, (DateTime)fromDate) >= 0 &&
DateTime.Compare(p.TradeDate, (DateTime)toDate) <= 0).ToListAsync();
var totalsByCust = new SomeModel{ Username = client.Username };
foreach (var item in payment)
{
totalByCust.Bcf += item.Bcf;
totalByCust.Ecn += item.Ecn;
totalByCust.Ecbt += item.Ecbt;
totalByCust.OpenGl += item.OpenGl;
totalByCust.JeyK += item.JeyK;

}
totals.Add(totalByCust);
}
return totals;
}

Answer Source

Make sure you have a navigation property on Payment to Client. Alternatively, you can load up all the clients into a dictionary since there is only 1500 of them. This is how you would do it with a navigation property:

public async Task<List<SomeModel>> SomeMethod(DateTime? fromDate, DateTime? toDate)
{
  return _db.Payments
    .Where(p =>
        DateTime.Compare(p.TradeDate, (DateTime)fromDate) >= 0 &&
        DateTime.Compare(p.TradeDate, (DateTime)toDate) <= 0))
    .GroupBy(p=>p.ClientId)
    .Select(g=>new SomeModel { 
      UserName = g.First().Client.UserName,
      Bcf = g.Sum(p=>p.Bcf),
      Ecn = g.Sum(p=>p.Ecn),
      Ecbt = g.Sum(p=>p.Ecbt),
      OpenGl = g.Sum(p=>p.OpenGl),
      JeyK = g.Sum(p=>p.JeyK)
    })
    .ToListAsync();
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download