N+1 Query
N+1 Query
is a Database Querying (anti) pattern. In this pattern, the client
makes N + 1 SELECT
calls to database to retrieve information.
Consider the following example. First we are retrieving multiple records from
the Department
table.
SELECT * from Department;
Now, for each record from the Department
table, we are retrieving the
Employee
.
SELECT * from Employee WHERE dept_id = ?
This is N+1 Pattern. N is the Number of records returned by the first SELECT
statement, plus 1 (the first statement itself). This pattern will cause
significant loss in performance. And should be avoided.
This could be prevented by using some sort of caching mechanism. First,
SELECT
all the rows from Employee
table. Store it in memory (or in Disk)
using a caching Database such as Redis. Now instead of querying the actual
database, make sure to use the in-memory Redis database. Also make sure that
you invalidate cache as and when required.