Faster results with Short query optimized mode in BigQuery
Google Cloud BigQuery is best in class for querying petabytes of data in a handful of seconds. Of course, you can query small data as well, and get your results in 7or 8 seconds.
You may think 7 seconds to process 141 MB of indexed data is not very fast. This latency is in fact not proportional to the amount of data in your dataset. It mostly consists of the time it takes to create an asynchronous job, launch a distributed computation, and merge the results.
If, like me, you query small datasets more often than large datasets, you will appreciate the new Short query optimized mode (in preview). We’re not alone! I know a lot of people who actually use BigQuery for “small” queries.
This new mode skips the creation of an async job, for short queries.
I was curious to find out the real gains in response times! So I launched my favorite query many times in Standard mode and in Short query optimized mode.
When using the Short query optimized mode, the UI works a bit differently: no job info, no execution details. Also, the duration of the current query execution is not displayed. No big deal, I can still measure the duration using high-tech instruments.
Results
My query was taking on average 8.1s in standard mode, and is now taking on average 5.4s in the new mode. This is a noticeable 33% speed improvement.
Your mileage may vary, so don’t take my word for it. Try it interactively on your own dataset, in BigQuery Studio.
To leverage the optimized mode in your app, have a look at the code samples.
