5 ms·
but queries are supposed to denormalize Well, that's how they've always worked, yes. Might there be a better way for some use cases? denormalize to prese
by JohnBooty 3y ago
but queries are supposed to denormalize
Well, that's how they've always worked, yes.
Might there be a better way for some use cases?
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?
- lsh123 3y agoDon’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).
- mikey_p 3y agoWhat's wrong with option 2? This seems very effective, more than enough performant for 99% of use cases, and it seems the most straightforward to me.
- JohnBooty 3y agoIt's usually fine, but could it be better? I feel like this entire discussion, and the linked article, are overflowing with answers to your question.
- scott_meyer 3y agoTwo things: 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%.