3 ms·
I can‘t believe this is a thing in 2023! If this is faster, shouldn‘t the database do that transparently in the background?!
by WanderPanda 3y ago
I can‘t believe this is a thing in 2023! If this is faster, shouldn‘t the database do that transparently in the background?!
- cogman10 3y agoAt least in our case, it comes down to expectations. 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).