Why Does Using an ORM Decrease Database Performance?
While trying to optimize the shipping module in a production ERP, I noticed that database queries were incredibly slow. At first, I examined the SQL queries and checked the indexes. However, I couldn't get the performance boost I expected. The problem lay in the Object-Relational Mapper (ORM) library, which was the cornerstone of our application. ORMs make things easier for software developers by providing an abstract layer for database operations, but this convenience often comes at a performance cost. In this post, I will explain why using an ORM decreases database performance, using concrete examples from my own field experience.
The core promise of ORMs is to keep developers away from SQL and allow them to interact with databases in a way that is more aligned with the object-oriented paradigm. This is a huge advantage, especially in small and medium-sized projects or rapid prototyping processes. However, when things get complex and performance becomes critical, the efficiency of the queries generated by ORMs starts to be questioned. In many cases I have encountered, especially in enterprise software development processes, the default behaviors of ORMs created an unexpected load on database servers.
Query Inefficiencies Generated by ORMs
ORMs usually manage database relationships using mechanisms like "eager loading" or "lazy loading". Depending on the developer's preference, these mechanisms either fetch all related data at once (eager loading) or fetch it in pieces as needed (lazy loading). However, ORMs may not always perform these loads in the most optimized way. For example, while only a few fields like ID and name are sufficient in a list view, the ORM might query the entire table or all related tables. This situation causes unnecessary data transfer and unnecessarily overloads the database server.
To give an example, on the order list screen of an e-commerce site, we needed to display the customer information and products for each order. When our ORM fetched the Order object, by default, it also brought the related Customer and OrderItems tables using "eager loading". This meant at least three queries for each order. If there were 100 orders in the list, this went up to 300 queries. Yet, all we needed was the order ID, customer name, and total amount. Even with this simple change, we could have dramatically reduced the number of queries.
-- Example of a potentially inefficient query that an ORM might generate
SELECT
o.order_id,
o.order_date,
o.total_amount,
c.customer_name, -- Only this field was needed
-- ... and all other customer fields
oi.product_id, -- Only this field was needed
oi.quantity -- Only this field was needed
-- ... and all other order_item fields
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date BETWEEN '2026-05-01' AND '2026-05-28';
Such queries lead to serious performance issues, especially with large datasets or frequently executed operations. Since the developer does not directly see the SQL generated by the ORM, they may struggle to identify the root cause of the problem.
⚠️ Default Behaviors of ORMs
ORMs make things easier by abstracting the developer from SQL. However, this abstraction can sometimes cause unnecessarily large amounts of data to be fetched and complex queries to be formed on the database server. Especially when using
eager loading, fetching fields that are not actually needed is a common problem.
The N+1 Problem and Lazy Loading Traps
Another performance issue frequently encountered in ORMs is the "N+1" problem. This problem occurs when N related records need to be fetched along with a main query. The ORM executes the main query once, and then executes separate additional queries for each related record. This means a total of 1+N queries, which leads to a serious performance loss, especially when N is large.
Although lazy loading is designed to solve this problem, it harbors its own traps. If you are looping over a collection and the related data is lazy-loaded in each iteration, this can lead to a scenario similar to the N+1 problem. For example, when fetching a blog post, if comments are lazy-loaded and we want to show the author of each comment, this can trigger a separate author query for each comment.
In one of our client projects, we wanted to display users' purchase histories. There were Customers and Purchases tables in the database. When a customer was fetched, our ORM lazy-loaded the Purchases. However, in the user interface, we needed to show the last 5 purchases of each customer and the product details of these purchases. Inside the loop, every time customer.purchases was called for each customer, new queries were generated, and then every time purchase.product was called, new queries were triggered again. This led to thousands of queries in a list with hundreds of customers.
# Example of the N+1 problem with Python and ORM (simplified)
customers = session.query(Customer).all() # 1 query
for customer in customers:
print(f"Customer: {customer.name}")
# This line triggers a separate query for customer.purchases (first of N queries)
for purchase in customer.purchases[:5]:
# This line triggers a separate query for purchase.product (first of M queries)
print(f" - Purchase: {purchase.product.name} - {purchase.amount}")
In this scenario, accessing customer.purchases and purchase.product dynamically generated new queries in the database. To solve this problem, we had to optimize the query right from the start using ORM features like joinedload or selectinload.
ℹ️ Solving the N+1 Problem
The N+1 problem is one of the most common performance bottlenecks when using an ORM. To solve this problem, it is critical to correctly use the eager loading strategies provided by ORMs (for example,
joinedloadorselectinloadin SQLAlchemy). These strategies ensure that related data is fetched in a single batch query, thereby reducing the number of queries.
Unnecessary SELECT * and Field Selection
Another inefficiency of ORMs is that they generally produce queries that select all columns (SELECT *). When a developer works on an object, they can access all fields of that object, and the ORM tries to fetch all of these fields from the database. However, most of the time we only need a few key fields. Fetching all columns increases network traffic, raises the database server's disk I/O, and increases memory usage.
In an accounting software project, when fetching the invoice list, we only needed the invoice number, date, amount, and status fields. However, our ORM fetched all fields (SELECT *) in the invoice table. This meant megabytes of unnecessary data most of the time. To fix this, we had to specify only the columns we needed using the ORM's field selection feature.
# Example of selecting only necessary fields (e.g., SQLAlchemy)
from sqlalchemy import select
stmt = select(
Invoice.invoice_number,
Invoice.invoice_date,
Invoice.total_amount,
Invoice.status
).where(Invoice.status == 'Paid')
invoices = session.execute(stmt).fetchall()
With this type of statement, much less data is fetched from the database, which reduces the load on both the application server and the database server.
Database Routing and Optimization of ORMs
ORMs generally adopt general-purpose approaches when generating database queries. This means they may not fully utilize specific database functions, optimized JOIN strategies, or platform-specific optimizations. In complex reporting scenarios or when performing custom analyses, optimized SQL queries we write directly can be much more performant than the queries generated by the ORM.
For example, in a production planning system, I had written a SQL query containing a complex window function and custom aggregated functions to optimize production for the next 3 months. This query ran in milliseconds in the database. However, when I tried to do the same thing with the ORM, since the ORM did not directly support these complex functions, I either had to fetch the data with many intermediate queries and do the calculations on the application side (which was a performance issue) or use the ORM's "raw SQL" execution feature.
🔥 Flexibility Limits of ORMs
ORMs are great for general-purpose database operations. However, when highly specific and optimized queries are required, the flexibility of ORMs can be limited. In these cases, writing direct SQL queries or using the ORM's raw SQL execution capabilities can be a more efficient solution.
When to Use an ORM and When to Avoid It
ORM usage varies depending on the project and the needs. For small and medium-sized applications, prototypes, or CRUD (Create, Read, Update, Delete) heavy projects, ORMs are an excellent choice. They increase development speed and make the code more readable.
However, in the following situations, you should carefully evaluate ORM usage or look for alternative ways:
- High-Performance Applications: In places where performance is critical, such as financial transactions, real-time analysis systems, or high-traffic websites, it is necessary to deeply analyze and optimize the performance of the queries generated by ORMs.
- Complex Database Structures and Relationships: In scenarios requiring many complex relationships, subqueries, or custom database functions, ORMs may fall short.
- Large Datasets: Query performance is vital in tables containing millions of records. The default behaviors of ORMs can cause serious problems in such datasets.
- Cases Requiring Specific Optimizations: When you want to take full advantage of specific database features, direct SQL queries can be more effective.
In my own experience, I have seen that, especially in enterprise software development processes, "using it correctly" is much more important than just "using" an ORM. This means reading the ORM's documentation well, understanding the SQL queries it generates, and making performance optimizations when necessary.
Conclusion and My Position
ORMs are one of the indispensable tools of modern software development, and they provide great convenience with the development speed and abstraction they offer. However, we must not forget that this convenience can come with a price. Especially in performance-critical situations, it is vital to understand how the default behaviors of ORMs affect database performance and to take proactive steps to minimize these effects.
My position is clear: I will continue to use ORMs to increase development speed and ensure code readability. However, especially in projects where performance is at the forefront, I will closely monitor the queries generated by ORMs. When necessary, writing optimized queries that only fetch the fields I need, using the ORM's advanced loading strategies to prevent the N+1 problem, or resorting directly to SQL queries in the most critical scenarios are acceptable trade-offs for me. We must remember that in software architecture, the best choice is the one that best fits the requirements and performance goals of the project.
