01 Dec 2014 →
In my previous post, I made the argument that this:
is better than this:
But that first query is way bigger. Other than a speed boost, it may not clear why I think the first query is better, so I thought I’d dig into it a bit more. I’d like to explain more about why I think this is better (not just faster) and offer up some alternative notation.
I think the biggest reason to avoid correlated subqueries is not the performance benefit, it’s the cognitive load that it forces upon us. A big, gnarly query with many correlated subqueries makes it difficult to reason about.
When you encounter a big query that’s doing more than you can take in at once, what’s the first thing you do? You break it down. Start executing pieces of the query to get an idea of the relations that are at play in your mind. Correlated subqueries make that difficult because you cannot select a piece of the query and run it individually. Especially in the case of PeopleSoft, these subqueries are wired to other relations outside of the immediate scope.
Effective date subqueries also often get thrown into the
alongside conditions that represent real business logic, clouding the intention
of the query. The goal should be to shove all of this accidental complexity
aside, leaving the minimal amount of code to represent the core of what the query
is trying to accomplish.
The following query speaks for itself, doesn’t it? This should be our goal. Any effective dated nonsense that this requires should take a back seat to this distilled statement.
Since we’re specifically talking PeopleSoft/Oracle here, we have the often
WITH clause at our disposal. We can use
WITH to destructure the
ugly peripheral bits into meaningful relation names.
In Clojure and other lisps, destructuring is done with the
WITH clause is very similar:
WITH lets us nicely break up (and push aside) the non-core queries and clauses,
leaving the business logic to speak for itself.
So my point here is really composability. Regardless of whether you’re using
WITH clauses, building queries by composing discrete meaningfully-named
relations will help build clearer, faster, and more maintainable queries.