Optimize nested loop in java spring boot

Question

I am about to generate an excel based on the user's request.

Input:
DateRange - 2022/02/01-2022/02/07

Scenario
The system will retrieve the logs from the database based on the DateRange. The logs contain the names of people & date when it was added. Also, the system will retrieve the list of people from the database. Now, after retrieving the logs and the people, I want to get the number of occurrence a person appeared on each date.

Database Info:

logs table - 10k or more
person table - at least 1,500 people.

Expected output:

Problem Issue

From the given data above there is a possibility of 10,000(logs) * 1,500(person) = 15m or more iteration to get the total occurrence of a person. This results to a heavy traffic on the response which took almost 60seconds or more.

Here is my code:

// initialize days
List<Date> days = getDaysFromRequest(); // get  the range from request

for (Person person: getPersonList()) {
  // .... code here to display Persons
  for (Date day: days) {
    // .... code here to display day
     int total = 0;
     for(UserLog log: getUserLog()) {
        if ( day == log.dateAdded && log.personName == person.Name) {
           total++; 
        }
     }
     System.out.printLn(total); // write total here in excel sheet Like,  B2 address
  }
}

How should I optimize this?

Answer 1

If I get it right, all the information you want seems to be in the logs or if not it defaults to zero. Therefor I would do something like:

Map<String<Map<LocalDate,Long>> occurrenceByNameAndDate =  // Map<Username<Map<Date,Count>>

    userLogs.strream().collect(Collectors.groupingBy(UserLog::personName,
                               Collectors.groupingBy(UserLog::dateAdded, 
                                    Collectors.counting())));

and use the above map some how like:

personList.forEach(person -> dateRange.forEach(day -> {
             long count = occurrenceByNameAndDate.getOrDefault(person.Name,Collections.EMPTY_MAP).getOrDefault(day,0);
             writeToExcel(person,day,count);
           }));

Or do it on the DB side

SELECT personName, dateAdded,COUNT(*) 
FROM UserLog 
WHERE dateAdded between(...)
GROUP BY personName,dateAdded