3 ms·
Are you sure about this? Let's say you run a webshop and have two tables, one for orders with 5 fields, one for customers, with 20 fields. Let's say you have
by torginus 1y ago
Are you sure about this?
Let's say you run a webshop and have two tables, one for orders with 5 fields, one for customers, with 20 fields.
Let's say you have 10k customers, and 1m orders.
A query performing a full join on this and getting all the data would result in 25 million fields transmitted, while 2 separate queries and a client side manual join would be just 5m for orders, and 200k for customers.
- dondraper36 1y agoWhat I particularly like about the comments in this thread is how it proves that everything is a trade-off :)
- digitalPhonix 1y agoWhat sort of application is regularly doing a query for “all data”?
- aembleton 1y agoClient report generation.
- tremon 1y ago1) As soon as reporting requirements get serious, you build a data warehouse. Because odds are, the client will want to combine data from multiple systems in their reports anyway. If not today, then they will tomorrow. 2) such reports never need all the data, it's mostly about top N volume queries or month-over-month performance data. When a reporting application does query all the data, it's because it's building its own data warehouse so the query usually happens only once per day, at a specific time, which means the load is entirely predictable.
- digitalPhonix 1y agoEven then, DB side aggregation and joins would be beneficial. I’m asking specifically about “all data” unfiltered, un-aggregated, un-joined as the parent’s analysis was on.
- jameshart 1y agoIf you need all the orders and all the customers sure. But usually you need some of the orders and you need the customer info associated with them. Often the set of orders you’re interested in might even be filtered by attributes of the customers they belong to. The decision of whether to normalize our results of a database query into separate sets of orders and customers, or to return a single joined dataset of orders with customer data attached, is completely orthogonal to the decision of whether to join data in the database.
- nicoburns 1y agoThese days you can use JSON aggregation in the database to avoid returning duplicate data in what would otherwise be large joins.
- valiant55 1y agoMy rule of thumb is if it's a 1:1 relationship, use a join. If it's 1:M, separate queries.
- deleted 1y ago[deleted]
- bambax 1y agoOne way to think about 1-to-many relationships is to think in the other way, "many-to-one". You don't join the orders to the customers, you join the customers to the orders (enrich the orders with customer information). It's very natural to want customer information when querying an order, and if you have a view like orders_with_customer_info, you get that with zero effort when querying that view by order id. You also get consolidated data (orders by customer) by doing select count(*), sum(amount) from orders_with_customer_info group by customer_id which I think is pretty straightforward.