What's wrong with "select id from table"? (presumably "where" skipped for brevity of the example)
SELECT id FROM table1
SELECT * FROM table2
WHERE table1_id in (...ids from previous query...)
SELECT table2.*
FROM table1
INNER JOIN table2 ON (
table2.table1_id == table1.id
) SELECT * FROM table2
WHERE table1_id in (SELECT ids FROM table1 WHERE ...)
SELECT * FROM table2
WHERE table1_id in (452345, 529872, 120395, ...)
The posters up thread are talking about novice Python / JavaScript / whatever code which issues queries in sequence: first a query to get the IDs. Then once that query comes back to the application, the IDs are passed back to the database for a single query of a different table.
The query optimizer can’t help here because it doesn’t know what the IDs are used for. It just sees a standalone query for a bunch of IDs. Then later, an unrelated query which happens to use those same IDs to query a different table.
I hope there are better ways to design microservices.
it ends up with some pretty terrible performance.
It's often a lot faster to do something like
Insert into #tempTable ids
select from table JOIN #tempTable t on t.id = table.id
For the `IN` query the DB doesn't know how many elements are in the list. In MSSQL, it would default to assuming "well, probably a short list" which blows out performance when that's not the case.
When you first insert into the temp table, the DB can reasonably say "Oh, this table has n elements" and switch the query plan accordingly.
In addition, you can throw an index on the temp table which can also improve performance. Assuming the table you are querying against is indexed on ID, when you have another table with IDs that are indexed it doesn't have to assume random access as it pulls out each id. (Effectively, it just has to navigate the tree nodes in order rather than needing to do a full look into the tree).