What is the potential performance problem with the following code and how would you suggest fixing it?


i had an interview with microsoft and they asked me this following question! i didn't knew how to solve it and i'm very interesting to know what's the solution p.s: it's only for me to improve myself because i was denied..

anyways: please assume that EmployeeRepository and ServiceTicketsRepository are implementing EntityFramework ORM repositories. The actual storage is a SQL database in the cloud. Bonus: what is the name of the anti-pattern?

 // Return overall number of pending work tickets for all employees in the repository
 public int GetTicketsForEmployees()
EmployeeRepository employeeRepository = new EmployeeRepository();
       ServiceTicketsRepository serviceTicketRepository = new ServiceTicketRepository();

       int ticketscount = 0;

       var employees = employeeRepository.All.Select(e => new EmployeeSummary { Employee = e }).ToList();
       foreach (var employee in employees)
           var tickets = serviceTicketRepository.AllIncluding(t => t.Customer).Where(t => t.AssignedToID ==employee.Employee.ID).ToList();
           ticketscount += tickets.Count();

       return ticketscount;

This is called the 1 + N anti-pattern. It means that you will do 1 + N round trips to the database where N is the number of records in the Employee table.

It will do 1 query to find all employees, then for each employee do another query to find their tickets, in order to count them.

The performance issue is that when N grows, your application will do more and more round trips, each taking a few milliseconds. Even at only 1000 employees this will be slow.

In addition to the round trips, this code is fetching all the columns for all the rows in the Employee table and also from the Ticket table. This will add up to a lot of bytes and in the end might cause an out of memory exception when the number of Employees and Tickets have grown to a big amount.

The fix is to perform one query which counts all the tickets which belongs to employees and then only returning the count. This will become one round trip sending only a few bytes over the network.