0

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;
 {
jarlh
  • 42,561
  • 8
  • 45
  • 63
lasvegasnay
  • 29
  • 1
  • 1
  • 2
  • 1
    why don't you perform count using query? why to bring the calculation on app layer when you can do it at db level? – Danyal Sandeelo Aug 17 '16 at 09:20
  • 1
    "Possible Duplicate" - [orm - What is the N+1 selects issue](http://stackoverflow.com/q/97197/15498) was probably what they were looking for, but I agree it's also a bad example because the entire count should be pushed down to the database. – Damien_The_Unbeliever Aug 17 '16 at 09:31
  • Or the [Shlemiel the Painter Pattern](http://www.joelonsoftware.com/articles/fog0000000319.html) – Filburt Aug 17 '16 at 09:39

2 Answers2

0

I'am not a C# but what I can see from my side is you are not using any join procedure.

If you have 1 million of employees and you have about 1000 tickets per employee.

You will do a 1 billion of query (loop including) :/ and you just want to return a count of ticket reported by your employee

Edit : I supposed you are in a eager loading and during your loop your EntityFramework instance will be open for the all duration of your loop.

Edit 2 : With a inner join you wont have to repeat t => t.AssignedToID ==employee.Employee.ID The join will do that for you.

stix
  • 110
  • 10
0

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.

Peter Henell
  • 2,416
  • 1
  • 17
  • 24