How to retrieve a lot of data in my asp .NET app efficiently?

Question

and thanks for paying attention to my issue Im trying to make a dashboard which shows every notable data in the team (created sales, lost sales, average response and so on). So on the Controller I retrieve all selected datas in my mysql database to display this in my view to display this using Chart.js. My code to retrieve the data is like this:

        public async Task<IActionResult> Charts()
        {

            int thisMonth= DateTime.Today.Month;
            int thisYear= DateTime.Today.Year;

            var rdhTable =  _context.RequestDateHistory.Where(rdh=>rdh.Date.Year ==thisYear) ;

            var productDtos = new Dictionary<int, ProductChartsDto>();
            foreach (var rdh in rdhTable)
            {
                int productId = await FindRelatedRequestId(rdh.RequestName);
                if (productId == 0) continue;
                ProductModel product = await _context.ProductModel
                    .Include(p =>p.User)
                    .Include(p=> p.RequestModel.CountryModel)
                    .FirstAsync(p => p.ID ==productId);
                if (product != null && !productDtos.ContainsKey(productId))
                {

                    productDtos.Add(productId,new ProductChartsDto
                    {
                        ID = product.ID,
                        Type_Enum= product.Type_Enum,
                        Name = product.Name,
                        TemporaryName = product.Temporary_Name,
                        SacManager = product.User_Sac,
                        FactoryId = product.FACTORY_ID,
                        Country = product.RequestModel.CountryModel.Name

                    }) ;
                }

            }


            //var userIdToUserName = await _context.Users.ToDictionaryAsync(u => u.Id, u => u.FirstName + ' ' + u.LastName);
            var requestData =await rdhTable
                .GroupBy(rdh => rdh.Date.Month)
                .Select(group => new
                {
                    Month = group.Key,
                    CreatedRequests = group.Count(rdh => rdh.StatusID == 1),
                    LostRequests = group.Count(rdh => rdh.StatusID == 21),
                    Agreements = group.Count(rdh => rdh.StatusID == 5),
                    Delivered = group.Count(rdh => rdh.StatusID == 17),
                })
                .ToListAsync();
            Dictionary<int, string> monthLabels = new Dictionary<int, string>();

            foreach(var data in requestData)
            {
                monthLabels.Add(data.Month, CultureInfo.InvariantCulture.DateTimeFormat.GetMonthName(data.Month));
            }

            ViewBag.MonthLabels = monthLabels;
            ViewBag.CreatedRequestsDict = requestData.ToDictionary(key => key.Month, value => value.CreatedRequests);
            ViewBag.LostRequestsDict = requestData.ToDictionary(key => key.Month, value => value.LostRequests);
            ViewBag.AgreementsDict = requestData.ToDictionary(key => key.Month, value => value.Agreements);
            ViewBag.DeliveredDict = requestData.ToDictionary(key => key.Month, value => value.Delivered);




            // Get and sort Enum type counts so far this year 
             var enumTypeCounts= productDtos.Where(p=> p.Value.Type_Enum != null)
                .GroupBy(p=> p.Value.Type_Enum)
                .ToDictionary(p=> p.Key,p=> p.Count());


            // Get and sort Products count by SAC User so far this year 
            var sacWorkload = productDtos.Where(p => p.Value.SacUser != null)
                .GroupBy(p => p.Value.SacUser)
                .Select(g => new {
                    Name = g.Key.FirstName+' '+g.Key.LastName ,
                    Count = g.Count() })
                .ToDictionary(x => x.Name, x => x.Count);

            var ordersByCountry = productDtos
                .GroupBy(p => p.Value.Country)
                .ToDictionary(x => x.Key, x => x.Count());



            ViewBag.ProductTypeCounts = enumTypeCounts;
            ViewBag.SacWorkload = sacWorkload;
            ViewBag.OrdersByCountry = ordersByCountry;

            return View();


        }

By the way, my helper FindRelatedRequestId is to see the object related from a single string(I know it's :

        private async Task<int> FindRelatedRequestId(string requestName)
        {
            var nameSplitted = requestName.Split('/');
            string productName = nameSplitted.Length > 1 ? nameSplitted[1] : requestName;
            var product = await _context.ProductModel
                .FirstOrDefaultAsync(p => p.Name == productName || p.Temporary_Name == productName);
            var productId = product?.ID ?? 0;
            return productId;
        }

The code runs and retrieves as I expect but for my case it's a too high amount of datas. it can take more than 900 seconds from my pc to retrieve everything. I know the loading time is much faster in a server but still testifies a lack of optimization.

I assume this issue is first of all due to all the database calls. Instead of calling every objects in one instruction, everything is called one by one. I searched for a solution but I didn't find anything for this.

The 2nd solution I found is to make a cache and store the data in cache but someting bugs in my mind: How to make the cache evolve and scale as the program sees new and uncached data everytime this method is called?

If you're asking why I deal with this, I'm a Junior developer of an app and I'm completely alone in the team to develop this app, I'm not in a programing framework so my code is not reviewed by another person (Im in a very specific case). The ASP .NET version is 2.0 (I know it's so old).

I'm currently trying to shape the data to remove my helper by making a foreign key PRODUCT_ID, I know this method doesn't simplify the problem.

If you have any clue to enlighteen me I'll be so glad to read it.

Thaks again for paying attention to my issue

Answer 1

I would also guess it is slow by repetitive calls to DB - 2 per each loop iteration.

What you could try to do is to fetch all data in one go. It will require some code to prepare data for query filtering and also then to retrieve particular products, but should be quicker, as it will be one query.

Here's example code:

var requestNameToProductName = rdhTable.ToDictionary(
    x => x.RequestName,
    x => 
    {
        var nameSplitted = x.RequestName.Split('/');
        return nameSplitted.Length > 1 ? nameSplitted[1] : x.RequestName;
    });
    
var productNames = requestNameToProductName.Values.ToArray();

// Fetch all data in one go.
var productModels = await _context.ProductModel
    .Where(p => productNames.Any(productName => p.Name == productName || p.Temporary_Name == productName))
    .Include(p => p.User)
    .Include(p => p.RequestModel.CountryModel)
    .ToArrayAsync();
    
foreach (var rdh in rdhTable)
{
    var productName = requestNameToProductName[rdh.RequestName];
    ProductModel product = productModels.FirstOrDefault(x => x.Name == productName || x.Temporary_Name == productName);
}