3 ms·
Without a join, you are sending the entirety of Table B over the wire and processing it in Python. Then you send that back to the database, inline in a query, t
by scott_s 4y ago
Without a join, you are sending the entirety of Table B over the wire and processing it in Python. Then you send that back to the database, inline in a query, to do an ad-hoc join on Table A. The results are then sent back over the wire to the Python side. Notably, the result set may be small, and it may always be small. Table B may grow very large, and all of it will always be sent over the wire and processed in Python.
With a join, the database is able to do the join on Table A and Table B in place, using whatever indexes it already has built up. The only thing sent over and processed by the Python is the result set. Even if Table B becomes very large, only the result set is sent over the wire and processed in Python.
Without a join in the query, you're essentially having to replicate the kind of logic that already exists in the database engine, in Python. That is, the database engine is already doing chunking and parallelization for you.
- nightpool 4y agoSure, I mean, I want to be clear—there's certainly a lot of inefficiency here. But my point is that the OP is talking about their data processing pipeline taking HOURS to handle queries for 50,000 IDs (50 parallel queries—1,000 IDs per query). When it comes down to it, I just don't believe that the inefficiency in serializing 1,000 numbers in python and then deserializing them in BigQuery has anything to do with the problems OP was experiencing. Remember—OP is probably going to be getting back 50,000 rows from the database anyway, the additional work here is linear with respect to the amount of rows retrieved. Sending 50,000 numbers over the wire to get back 50,000 rows worth of data is not an "hours long" processing cost, in the scheme of things.