Ah, the N+1 query problem: a sneaky trap that can silently kill your app’s performance while you sit there wondering why your page loads like it’s stuck in 2003.
Let’s break down the N+1 problem, and then look at how it can be resolved using JSON Relational Duality Views.
The N+1 Problem
Imagine you’re building an app using an ORM like Hibernate, and want to query a list of blog posts, where each post has an author.
You start with some code like this:
List<Post> posts = postRepository.findAll();
for (Post post : posts) {
System.out.println(post.getAuthor().getName());
}
Looks innocent, right?
Behind the scenes, this code is likely doing something very sub-optimal.
First, it runs one query to fetch all the posts:
SELECT * FROM posts;Then, for each post, it runs another query to fetch the author:
SELECT * FROM authors WHERE id = ?;The result? If you have 10 posts, you end up running 10+1 queries. That’s the N+1 problem. And why is the N+1 problem a “problem”?
- It burdens your database with repeated, redundant queries.
- It scales poorly: what worked in milliseconds with 10 records now takes minutes with thousands of records.
- You might not catch it before you deploy to production. Because of the N+1’s scaling behavior, you may not notice it in a development environment with a small amount of records.
The typical solution with ORMs
If you’re using Spring JPA, you might write a query method like this, that fetches all posts and their associated author data. Note the use of JOIN FETCH, which tells the ORM to eagerly load the author data.
@Query("SELECT p FROM Post p JOIN FETCH p.author")
List<Post> findAllWithAuthors();There are still a few gotchas here to consider:
- Pagination: JOIN FETCH eagerly fetches all records. If you have a large amount of records, you’ll need to implement pagination with a native query to avoid the N+1 problem.
- If author is nullable, you’ll need to tweak your JOIN logic to avoid missing records.
- If you need other joins, like post comments, you’ll need to change your JOIN logic again to avoid duplicates records.
So in addition to ORM gotchas, you’ll be worrying about SQL gotchas as well. The simple use case of Posts and Authors becomes quite complex.
What if there was another data model?
JSON Relational Duality Views blend of relational and document data models, enabling developers to work with JSON documents backed by normalized relational tables, without the limitations of traditional ORMs or document stores.
Duality views remove ORM mapping layers, improve performance by assembling and transmitting complete documents in a single operation, and ensure consistency/de-duplication through relational constraints.
In the duality view model, relational data is sent to the database as one JSON document, and the database server handles splitting that data into its corresponding tables.
Similarly, when I query a duality view, the database server composes relational data from one or more tables into a single JSON document.
Let’s see how this might work in the Posts-Author model, and how it avoids the N+1 query problem.
First, let’s define a relational schema for posts and authors:
CREATE TABLE author (
id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name VARCHAR2(100) NOT NULL,
email VARCHAR2(150) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT SYSTIMESTAMP
);
CREATE TABLE post (
id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
author_id NUMBER NOT NULL,
title VARCHAR2(200) NOT NULL,
content CLOB,
created_at TIMESTAMP DEFAULT SYSTIMESTAMP,
CONSTRAINT fk_post_author
FOREIGN KEY (author_id)
REFERENCES author(id)
ON DELETE CASCADE
);
Next, we’ll create a JSON Relational Duality View for our schema. I like to use the SQL Developer Extension for Visual Studio Code, as it includes a handy duality view builder tool.
CREATE OR REPLACE FORCE EDITIONABLE JSON RELATIONAL DUALITY VIEW posts_dv
AS post @insert @update @delete {
_id : id
title
content
createdAt : created_at
author @link (from : [AUTHOR_ID]) @insert @update {
id
name
email
createdAt : created_at
}
};Notice the view contains references to both the posts and authors tables, as well as their data. This makes it easy to query the contents of both as JSON with one round trip, completely avoiding the N+1 problem.
Let’s try inserting some records using our duality view. We can use a simple JSON document to insert rows in both the posts and authors tables.
insert into posts_dv (data) values ('{
"title": "my first post",
"content": "lorem ipsum",
"author": {
"name": "Anders Swanson",
"email": "example@example.com"
}
}');Notice the use of “data” as the insert object. All duality views use this as the root JSON container name by default.
Now we’ll query the duality view, retrieving our relational data as a JSON document.
select json_serialize(p.data pretty) as posts
from posts_dv p;{
"_id" : 1,
"_metadata" :
{
"etag" : "E80039A74B71698FF2ADA4915E47184C",
"asof" : "000023C4E13CB799"
},
"title" : "my first post",
"content" : "lorem ipsum",
"createdAt" : "2025-07-16T18:33:36.066401",
"author" :
{
"id" : 1,
"name" : "Anders Swanson",
"email" : "example@example.com",
"createdAt" : "2025-07-16T18:33:36.023836"
}
}The _metadata field is generated by the database, and contains data used for lock-free concurrency control and object versioning. I’ll talk more on this in a future blog post.
Lastly, we’ll show a simple update. Note that the data object is JSON, so any Oracle Database JSON function can be used on it, for filtering, transposing, and more.
update posts_dv p set data = '{
"_id": 1,
"title": "learn the basics of duality views",
"content": "duality views are pretty cool!",
"author": {
"name": "Anders Swanson",
"email": "example@example.com"
}
}' where p.data."_id" = 1;I encourage you to try it out. Maybe with Oracle Database Free. Duality Views work anywhere you have Oracle Database 23ai!
References
Questions? Leave a comment or connect on LinkedIn. I’m always happy to chat about database use cases!
Leave a Reply