> First the authors show that for certain use cases, a single query is not ideal.
Reading it closely, this is what I already disagree with. You do a good job of summarizing their two main arguments, so allow me to rebut:
> Either the result set will be larger than it needs to be
Yes, they're using the example of repeating information (denormalization), such as multiple courses taught by the same professor. But it's genuinely hard to see this as a drawback -- that's a feature. Data should be stored as normalized as possible, but queries are supposed to denormalize to present information in the desired format.
(And if you're dealing with what would be an overly-large amount of repeated values, you just run multiple queries yourself instead of one. And if round-trip latency is some kind of issue with running queries sequentially, you can always issue queries in parallel instead.)
> which "discard[s] all schema and relational information on the way."
Again, this is a feature. You're not supposed to retrieve all possible relational information in query results. You write your query to retrieve and differentiate precisely what you need and no more. Discarding irrelevant information is a feature, not a bug. More than that -- you want your query to define and adhere to its output format regardless of the underlying database structure, precisely so you can refactor things in the database and rewrite the query but not need to rewrite the code that uses the query results.
I guess my overall bafflement is that the things they describe as "not ideal" seem to me like features rather than problems, and these features have been highly beneficial in my practical experience of writing a lot of database-driven apps.
denormalize to present information in the desired format
What if my "desired format" is a structure that maintains the relationships specified in the database?
This is an incredibly common pattern, particularly with ORMs.
# psuedocode
for each recipe in Recipe.joins(Ingredient).select_all() do
for each ingredient in recipe.ingredients do
print(f"{recipe.name} requires {ingredient.name}")
Depending on the ORM and the query options specified, this results in one of the following:
1. Everything getting denormalized into a single, wasteful result table and then teased back out into Recipes and Ingredients by the ORM
2. Two SQL queries. One to fetch the recipes, and then one to fetch the ingredients once we know the id's of the recipies.
3. A big ol' N+1 situation with 1 query to fetch recipes and then N additional queries to fetch the ingredients.
They all have various inefficiencies. What if we just returned the relational structures directly?
1. You have given up on any sort of consistency.
2. The database implementation will never be able to optimize a lookup workload which is pretty close to pessimal. Not caring about 99% is only viable if can actually avoid the other 1%.
Don’t use ORM for performance critical DB operations and write SQL by hand might be another answer. Case in point: I recently improved performance of my email app by about 250x by replacing SwiftData with native SQLite database (and manual sql queries). The message data is under 1M rows range plus a lot of blobs/attachments (under 10MB each).
> Data should be stored as normalized as possible, but queries are supposed to denormalize to present information in the desired format.
But the desired format usually isn't a square, flat table. Usually the data has structure and you want to preserve that structure when you're displaying, not just having it hidden in the database. E.g. you might want to show a heading for each professor and then a list of classes taught by that professor. Getting back a flat table with n duplicate copies of the professor's information is a step backwards.
> Data should be stored as normalized as possible, but queries are supposed to denormalize to present information in the desired format.
The desired format depends on the application.
At this point a substantial portion of all SQL queries are generated by (and the results consumed by) ORMs.
For that use case having results that include Products and Categories separately (so you can instantiate Product and Category objects) is more useful than a single table.
If the transfer time was really slow, I could see wanting to return a subset of data without duplication, and then allow the application to query locally among that data for all the various things it needs.
That seems so unlikely that I'd think a distributed sqlite database would be a better idea instead, and just have it sync up when it can, and always query local.
I think the point is if you move that denormalisation to the application (the ORM say) then it's less data on the wire.
Similarly keeping schema information gives the ORM more to work with I suppose? Helpful for typing, foreign key links where it's not necessarily the same original table structure, but in the query result professor_id is still ultimately a foreign key to professor table, etc.
Less data on the wire and a consistent snapshot at a given point in time, without the possible inconsistencies allowed by performing successive queries with intervening writes.
> Data should be stored as normalized as possible, but queries are supposed to denormalize to present information in the desired format.
Nit: data should be stored as normalized as possible, if your use-case prioritizes fast writes over fast reads. There are other benefits to normalization, but in practice they pale in comparison to the performance shift.
Reading it closely, this is what I already disagree with. You do a good job of summarizing their two main arguments, so allow me to rebut:
> Either the result set will be larger than it needs to be
Yes, they're using the example of repeating information (denormalization), such as multiple courses taught by the same professor. But it's genuinely hard to see this as a drawback -- that's a feature. Data should be stored as normalized as possible, but queries are supposed to denormalize to present information in the desired format.
(And if you're dealing with what would be an overly-large amount of repeated values, you just run multiple queries yourself instead of one. And if round-trip latency is some kind of issue with running queries sequentially, you can always issue queries in parallel instead.)
> which "discard[s] all schema and relational information on the way."
Again, this is a feature. You're not supposed to retrieve all possible relational information in query results. You write your query to retrieve and differentiate precisely what you need and no more. Discarding irrelevant information is a feature, not a bug. More than that -- you want your query to define and adhere to its output format regardless of the underlying database structure, precisely so you can refactor things in the database and rewrite the query but not need to rewrite the code that uses the query results.
I guess my overall bafflement is that the things they describe as "not ideal" seem to me like features rather than problems, and these features have been highly beneficial in my practical experience of writing a lot of database-driven apps.