CODEWITHSUNDEEP
sql-updategoogle-bigquery
Lev Savranskiy
Lev Savranskiy

Reputation: 430

Google Bigquery UPDATE row takes too long - need a improve performance solution

this update takes 194 seconds for 220mln rows. Is there a way to improve that?

#standardSQL
UPDATE dataset.people SET CBSA_CODE = '54620' where substr(zip,1,5) = '99047'

Upvotes: 0

Views: 1713

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33745

When asking for performance help, it is useful to include a screenshot of the Execution Plan from the BigQuery UI to see which stages are the most intensive, and where the time was spent. Without that, though, I suspect that this small optimization should help:

UPDATE dataset.people SET CBSA_CODE = '54620' WHERE zip LIKE '99047%'

BigQuery should be able to push this filter down to its storage system, since it's a more natural way to express string containment, so if you see a high "Read" time in the Execution Plan for the original query, this might reduce it.

Upvotes: 3

Related Questions