Fetch records from Database in Tree Structure

Question

I am trying fetch the records from the Database.

My Database tables:

1. Departments
2. EmployeeDepartments (Intermediate Table for Department --- > Employees)
3. Employees
4. EmployeeLocations (Intermediate Table for Employees --- > Locations)
5. Locations

The above tables contains huge records.

My data would be generated based on the below structure:

What I tried:

My first approach:

using(SQLConnection con = new SQLConnection())
{
     //Get all the departments
     List<Department> departmentList = sp.GetAllDeparments();

     foreach(Deparment deparment in departmentList){

     //Get all employees
     List<Employee> employeeList= sp.GetEmployeesByDepartment(departmentId);

      foreach(Employee employee in employeeList){

            //Get all locations
            List<Location> locationList= sp.GetLocationsByEmployee(employeeId);

            foreach(Location location in locationList){
                  //Code for location generation for PDF. 
            }
       }
    }
}

The above code is taking time around 6 minutes if I hit the database in foreach loops.

My second approach:

//Get all the departments
List<Department> departmentList = sp.GetAllDeparments();

//Get all employees
List<Employee> employeeList= sp.GetAllEmployees();

//Get all locations
 List<Location> locationList= sp.GetAllLocations();

//Get EmployeeLocations List<EmployeeLocations> emplocationList= 
 sp.GetAllEmployeeLocations();

//Get EmployeeDepartments List<EmployeeDepartments> empDepList= 
sp.GetAllEmployeeDepartments();

foreach(Deparment deparment in departmentList){

    foreach(Employee employee in employeeList){

        foreach(Location location in locationList){

        }
    }
}

I am fetching all the records from all the above mentioned tables and manipulating using LINQ in foreach code. Thus, reducing the time from 6 minutes to 4 minutes.

My question: What would be the efficient way to fetch the data at minimal time as possible. at this kind of scenario ?

I need the data manipulated in the above picture.

Answer 1

The second approach involving less database roundtrips is much better. But according to your comment

Even though I am currently using the second approach, fetching the data at once and keeping it in memory is not an issue. But while doing foreach I am taking fetching using LambdaExpression. That takes time.

looks like your processing part is using a lot of inefficient linear searches. It can be improved significantly by preparing and using a fast hash based lookup data structures.

In this particular case, you need two dictionaries for fast locating the employee by PK and location by PK, and two lookups for fast locating employees by department PK and locations by employee PK.

Assuming your class model is something like this (you can modify it with the actual property names/types):

class Department
{
    public int Id { get; set; } // PK
    // Other properties...
}

class Employee
{
    public int Id { get; set; } // PK
    // Other properties...
}

class Location
{
    public int Id { get; set; } // PK
    // Other properties...
}

class EmployeeDepartment
{
    public int EmployeeId { get; set; } // FK
    public int DepartmentId { get; set; } // FK
}

class EmployeeLocation
{
    public int EmployeeId { get; set; } // FK
    public int LocationId { get; set; } // FK
}

Then the processing could be like this:

//Fetch all necessary data
List<Department> departmentList = sp.GetAllDeparments();    
List<Employee> employeeList = sp.GetAllEmployees();
List<Location> locationList = sp.GetAllLocations();
List<EmployeeLocation> employeeLocationList = sp.GetAllEmployeeLocations();
List<EmployeeDepartment> employeeDepartmentList = sp.GetAllEmployeeDepartments();

// Build the helper fast lookup structures
var employeeById = employeeList.ToDictionary(e => e.Id);
var locationById = locationList.ToDictionary(e => e.Id);
var employeesByDepartmentId = employeeDepartmentList.ToLookup(e => e.DepartmentId, e => employeeById[e.EmployeeId]);
var locationsByEmployeeId = employeeLocationList.ToLookup(e => e.EmployeeId, e => locationById[e.LocationId]);

// The processing
foreach (Department deparment in departmentList)
{
    foreach (Employee employee in employeesByDepartmentId[deparment.Id])
    {
        foreach (Location location in locationsByEmployeeId[employee.Id])
        {

        }
    }
}

Answer 2

Question & Suggestion;

If the sql connection is opened repeatedly when receiving the data from the list, Only one request to the database is required. You need to list the list if it has multiple occurrences in this request

As a result: the request should be sent to the database once. If all the lists are retrieved using the dictionary method, then 15% performance increase is achieved. If you are working with really big data, you can work asynchronously

We need to see the contents of the functions that start with get if more detail is needed.