3 ms·
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
by civilized 4y ago
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.