4 ms·
4000 records with a one-to-many join (the many part capped at 240 records). The aggregations are done mostly on that join table. In our original ruby process,
by phereford 10y ago
4000 records with a one-to-many join (the many part capped at 240 records). The aggregations are done mostly on that join table.
In our original ruby process, it would return just that, 240 records per join and aggregate on that. Our SQL process aggregates that in a stored procedure and returns the flat 4000 records.
We really didn't want to introduce another piece of technology to this stack, hence we didn't look at R or Python or Elixir. We did consider storing all of this inside elasticsearch for a hot second but keeping things in sync at the correct times seemed sub optimal and would add a layer of complexity we were all uncomfortable with.
- jstimpfle 10y agoONE join? A join is usually just a linear scan through one table, doing hashtable lookups on the other table. If there is no hash table, at the worst both tables have to be sorted on the join key and scanned linearly. The absolute worst case of a "join" is a cartesian product which runs in quadratic time. 6000^2 = 36000000, which should still easily finish in ~1 second.
- rtpg 10y agoThis really sounds like you're having an issue linked to the ORM. I don't know the Rails equivalent, but Django's ORM has a way of prefetching related fields on a model in a single query. My bet is that your code wasn't 1 query, but actually 4000 * 240 queries, which will always take a while. EDIT: I just saw in a sibling comment that the actual data fetching was only taking 30s. I imagine you were doing that part mostly right