lazy loading and n+1 query problem

Apparently, like in react or nextjs we have lazy loading for realted entites which is handled by ORM’s in application layer as well. For example we have a entity named student and it has one to many realtionship with course table. While creating the one to may realtion , we can lazy load it i.e while fetching the student table ,we only get the records of student not the courses associated to each student. We only get the courses records when we run an extra query fetching the courses of a student which may lead to n+1 query problem. And this is totally handled in the application layer through the ORM. Here is an exmaple of lazy loading in typeORM:

@Entity()
export class Student {
    @OneToMany(() => Course, (course) => course.student, { lazy: true })
    courses: Promise<course[]>
}

Advantages of lazy loading:

• Resource efficiency: Only loads the necessary data when actually required, reducing query costs and memory usage
• Ideal for selective data usage: Suitable for scenarios where not all related data is needed.

Disadvantages of lazy loading:

• Increased query complexity: Each access to related data triggers an additional query to the database, which may increase latency if not managed properly.
• Difficult to track: Can lead to the n+1 query problem if used carelessly.

Now , previously we encountered that lazy loading can lead to n+1 query problem. Lets know about the n+1 query problem briefly with an example:

n+1 query problem

For example : let’s say we have 100 students and they have n number of courses each.

To get courses of each students we have to fetch all the students which gives 100 students in one query and then fetch courses of each 100 students which results to 101 quries. So , basically this is the n+1 query problem. The queries are as given below:

select * from student => 100 studnets
 
select * from course where studentId=xyz ==> this query is run for all the 100 students
 
total queries= 100+1 = 101 (basically to fetch courses of 100 students we need 101 queries)

The n+1 query problem can be solved using joins , eager loading in the ORM and batch queries:

1. joins

   select student.* , course.*
   from student left Join course
   on student.id = course.studentId

2. eager loading(fetches all the courses records with the students)

   @Entity()
   export class Student {
       @OneToMany(() => Course, (course) => course.student, { eager: true })
       courses: Promise<course[]>
   }

3. batch query(2 total queries)

   SELECT * FROM courses WHERE student_id IN (1, 2, 3, 4, 5..);

ps: the penguin from the penguin meme was skipper looking for klowski , rico and private 🤣