Keeping Too Much Logic in SQL Queries Does Not Scale

Replacing hundreds of lines of code by a single clever SQL snippet is fun and intellectually satisfying. And, after all, there are compelling reasons.

It's good for performance. Processing data in situ before returning saves bandwidth and reduces latency. This is especially true for mega-queries that embed complex business logic and avoid back-and-forth between the application code and the database. I've seen tremendous performance improvement by replacing complicated code with pompous SQL. A request that took a few seconds to complete now takes a few milliseconds—really.

Besides, it's pretty elegant. SQL is an expressive language particularly suited to data manipulation, certainly more than the typical languages we use for application code.

Despite the benefits, I try to write more "boring" SQL these days and avoid offloading too much heavy work onto the database, trying to keep transactions short and lightweight.

Leave a Comment