Re:For Want of a JOIN
by civilized on Thursday December 22, 2022 @10:47AM
(#34094518)
Two clear inefficiencies in the junior's code:
1. It is pushing the entire id table back and forth through the network connection, bit by bit. Replacing with a join completely eliminates this.
2. A query with an IN clause is (probably) doing a hash join under the hood to calculate the result of the IN clause. So the junior's code is effectively submitting a join query over and over, each time with a slightly different tiny chunk of data, rather than asking for the joined data once and processing the result in batches.
It is also worth considering if the entire data processing pipeline can be in SQL, but I can't tell if that's the case from the blog post.
1. It is pushing the entire id table back and forth through the network connection, bit by bit. Replacing with a join completely eliminates this.
2. A query with an IN clause is (probably) doing a hash join under the hood to calculate the result of the IN clause. So the junior's code is effectively submitting a join query over and over, each time with a slightly different tiny chunk of data, rather than asking for the joined data once and processing the result in batches.
It is also worth considering if the entire data processing pipeline can be in SQL, but I can't tell if that's the case from the blog post.