The Underrated Discipline of Writing Efficient SQL in an Era of ORM Convenience

Let’s be honest: how many times have you written a Django ORM query that looked beautiful, only to find it generated a five-level nested SELECT with 200 rows of pointless joins? ORMs are a gift—they abstract away boilerplate, keep your code tidy, and make onboarding non-DBAs easier. But they also hide an uncomfortable truth: your database doesn’t care how elegant your Python is.

SQL is still the engine. And ignoring its performance quirks can cost you real money (and real users) when your app scales.

The ORM Illusion: It’s Not Free

ORMs like SQLAlchemy, Django ORM, and SQLModel are built to shield you from SQL. But that shield is translucent at best. Here’s what often goes wrong:

• The N+1 query problem: You fetch 100 blog posts, then loop through each to get the author. Voilà: 101 queries instead of one JOIN. ORMs have select_related and prefetch_related, but developers forget.
• Over-fetching columns: SELECT * everywhere. You might only need id and name, but the ORM pulls 30 columns. That’s wasted I/O.
• Mysterious performance cliffs: A simple .filter() that looks innocent can trigger full table scans if the ORM’s query optimizer misjudges indexes.

Real example: A Django app querying a 50k-row table took 8 seconds per request. The ORM had generated a subquery with a correlated IN clause. One explicit SQL rewrite with a JOIN: 0.02 seconds.

Why Efficiency Still Matters

You might think “my database handles it fine,” until it doesn’t. Consider:

• Latency magnifies at scale: A 50ms query becomes 5 seconds when you run it 100 times sequentially.
• Lock contention: Slow queries hold row locks longer, blocking other transactions.
• Cloud costs: Database CPU and memory aren’t free. AWS RDS charges per hour—slow queries eat resources you pay for.

Plus, your users will notice. A 200ms page load feels instant. A 2-second load feels sluggish. And you can’t always just throw more hardware at the problem.

The 4 SQL Disciplines ORMs Hide (But You Must Master)

1. Understand Indexes (And Check the Explain Plan)

ORMs don’t tell you when an index is being ignored. They just run the query.

# Django ORM
User.objects.filter(email__icontains='example')

That icontains usually triggers a LIKE '%example%', which a standard B-tree index cannot use efficiently. The ORM won’t warn you. You must:

• Use EXPLAIN ANALYZE to see the actual execution plan.
• Add indexes for columns used in WHERE, JOIN, and ORDER BY.
• Avoid LIKE with leading wildcards unless you use pg_trgm GIN indexes.

2. Fight the N+1 Epidemic

This is the #1 ORM performance killer. The fix is simple but often forgotten:

# Bad: N+1
for order in Order.objects.all():
    print(order.customer.name)

# Good: one query
orders = Order.objects.select_related('customer').all()

But select_related only works for ForeignKey relations. For ManyToMany or reverse relations, use prefetch_related. Pro tip: Profile with django-debug-toolbar or SQLAlchemy’s echo=True to spot every generated query.

3. Write What You Mean: Raw SQL Isn’t Evil

Sometimes you need window functions, LATERAL joins, or complex aggregations that an ORM can’t express elegantly. Don’t be afraid to drop into raw SQL:

# SQLAlchemy
result = session.execute(text("""
    WITH ranked AS (
        SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as rn
        FROM events
    )
    SELECT * FROM ranked WHERE rn = 1
"""))

Raw SQL gives you total control. Keep it in a separate queries.py module if you want the safety of typed returns. Most modern ORMs let you map raw results to models.

4. Batch Operations in Bulk

ORMs often do row-by-row updates or inserts. Flatten them:

# SQLAlchemy bulk insert (10x faster than individual rows)
session.bulk_insert_mappings(User, [
    {'name': 'Alice', 'email': 'alice@example.com'},
    {'name': 'Bob', 'email': 'bob@example.com'},
])

Same for updates: use session.bulk_update_mappings() or raw SQL UPDATE ... FROM syntax. Your database will thank you.

A Practical Workflow: Profile First, Optimize Later

Here’s a disciplined approach that balances ORM convenience with SQL efficiency:

1. Profile before you optimize. Use EXPLAIN ANALYZE or your ORM’s query logger to find slow queries.
2. Check for N+1. If you see 101 queries for 100 objects, fix the eager loading.
3. Add indexes based on WHERE and ORDER BY columns. The database’s own pg_stat_user_indexes or sys.dm_db_index_usage_stats can tell you which indexes are unused.
4. Benchmark the SQL in isolation. Copy the ORM-generated SQL, run it in a console, and measure before/after.
5. Only rewrite to raw SQL as a last resort after indexing fails. ORMs are fine for 95% of queries—the 5% that matter are where you need discipline.

The Bottom Line

ORMs are not your enemy. They save you from writing repetitive CRUD. But treating them as a black box is a recipe for slow apps and angry users. A little SQL discipline—knowing how indexes work, watching for N+1, and not fearing raw queries—transforms you from a “developer who uses an ORM” into a “developer who owns the data layer.”

And that’s a skill that will keep your app fast long after the next ORM version is released.