3 ms·

In the world of database backed web apps a related pattern is asking the database for some rows for a table, and then asking the database from some more rows,

In the world of database backed web apps a related pattern is asking the database for some rows for a table, and then asking the database from some more rows, based on each row you just got. Rather than joining it together into one query. Makes it into production but falls down eventually!

kbenson 7y ago
There are sometimes reasons why you don't want to join, such as large data parent row sizes that get repeated a lot in if there's a lot of child relations. But that should be two queries. One for all the parents, and one to get the children with a sub-select (or at worst a manually generated parent id list), and then you can join them manually (if actually needed and you can't just use the data sets as they are).
- somurzakov 7y ago
  you are right, APIs often use o(n) algorithms in an individual API call that is meant to execute once. what happens, is that API users start executing that endpoint N times and here it becomes O(n^2). people should remember that APIs are for CRUD calls. If you want batch reports - call a separate reporting endpoints that process data in large batches.
toast0 7y ago
I've actually had a lot of success going the other way. A database query with a join takes down production, so do a client side join, query one: get a bunch of ids from table A, query two: get a bunch of rows from table B, often as get one row from table B UNION get next row from table B etc. You can try using IN on the second query, but usually if that was going to work in a reasonable amount of time, your join would have also worked in a reasonable amount of time. The real problem people run into with the client side join is making it query one get a bunch of ids from table A, query two through N, get one row from B, with each query requiring a round trip. Even a pretty small client to server roundtrip of 1 ms gets nasty quick with repeated queries.
- ht85 7y ago
  After going through every pattern this is also my favorite one. While it can add some perceivable latency if you have many levels of depth, it is usually a lot lighter on CPU and memory than one big query. The reason I really like it is because it is very easy to strongly type results coming from a single table, and processing the data in your application code allows you to keep the typings through the process of stitching everything back together.
- nitwit005 7y ago
  I suspect you're just working around an issue with the query or the DB itself. Previous company had to do something like that because of an Oracle perf bug way back (outer join issue I believe?), but they fixed it eventually and it was all deleted.
- Dylan16807 7y ago
  > You can try using IN on the second query, but usually if that was going to work in a reasonable amount of time, your join would have also worked in a reasonable amount of time. Depends on whether your database is reasonable or not. Sometimes "foo IN (1,2,3,4,5)" will do five index lookups, while a JOIN will check every single "foo" in the table. For bonus points it will also convert "IN ([independent subquery])" into the same JOIN. So just by reminding the database that a thing inside parentheses gets evaluated first, you can make it go a thousand times faster. Not a single other change to the query.
72deluxe 7y ago
I had this on Magento 2.3.3 for a "custom stock" module that was running loops to grab data for a product and its child configurable products. As Magento is written in a modular way, asking a subcomponent for data can involve a database lookup so it is really slow to use and painful to debug. Anyway, it was running 29000 database queries as it was repeatedly using an "in" clause of 1 item across hundreds of queries, instead of 1 query with hundreds of items in the "in" clause. I replaced them with 1 query that fetched all it needed instead of the 29000 queries (and 8000+ which were duplicates). Terrible code.

kbenson 7y ago

There are sometimes reasons why you don't want to join, such as large data parent row sizes that get repeated a lot in if there's a lot of child relations. But that should be two queries. One for all the parents, and one to get the children with a sub-select (or at worst a manually generated parent id list), and then you can join them manually (if actually needed and you can't just use the data sets as they are).

somurzakov 7y ago
you are right, APIs often use o(n) algorithms in an individual API call that is meant to execute once. what happens, is that API users start executing that endpoint N times and here it becomes O(n^2). people should remember that APIs are for CRUD calls. If you want batch reports - call a separate reporting endpoints that process data in large batches.

toast0 7y ago

I've actually had a lot of success going the other way. A database query with a join takes down production, so do a client side join, query one: get a bunch of ids from table A, query two: get a bunch of rows from table B, often as get one row from table B UNION get next row from table B etc. You can try using IN on the second query, but usually if that was going to work in a reasonable amount of time, your join would have also worked in a reasonable amount of time. The real problem people run into with the client side join is making it query one get a bunch of ids from table A, query two through N, get one row from B, with each query requiring a round trip. Even a pretty small client to server roundtrip of 1 ms gets nasty quick with repeated queries.

ht85 7y ago
After going through every pattern this is also my favorite one. While it can add some perceivable latency if you have many levels of depth, it is usually a lot lighter on CPU and memory than one big query. The reason I really like it is because it is very easy to strongly type results coming from a single table, and processing the data in your application code allows you to keep the typings through the process of stitching everything back together.
nitwit005 7y ago
I suspect you're just working around an issue with the query or the DB itself. Previous company had to do something like that because of an Oracle perf bug way back (outer join issue I believe?), but they fixed it eventually and it was all deleted.
Dylan16807 7y ago
> You can try using IN on the second query, but usually if that was going to work in a reasonable amount of time, your join would have also worked in a reasonable amount of time. Depends on whether your database is reasonable or not. Sometimes "foo IN (1,2,3,4,5)" will do five index lookups, while a JOIN will check every single "foo" in the table. For bonus points it will also convert "IN ([independent subquery])" into the same JOIN. So just by reminding the database that a thing inside parentheses gets evaluated first, you can make it go a thousand times faster. Not a single other change to the query.

72deluxe 7y ago

I had this on Magento 2.3.3 for a "custom stock" module that was running loops to grab data for a product and its child configurable products. As Magento is written in a modular way, asking a subcomponent for data can involve a database lookup so it is really slow to use and painful to debug. Anyway, it was running 29000 database queries as it was repeatedly using an "in" clause of 1 item across hundreds of queries, instead of 1 query with hundreds of items in the "in" clause. I replaced them with 1 query that fetched all it needed instead of the 29000 queries (and 8000+ which were duplicates). Terrible code.