BigQuery Introduces Short Query Optimized Mode for Faster Results

Google has announced a new feature for BigQuery called "short query optimized mode," designed to significantly reduce latency for small queries. This update, currently in preview, aims to enhance performance for common workloads such as dashboards and data exploration.

Key Features of Short Query Optimized Mode

1. Automatic Optimization: BigQuery automatically determines which queries can be accelerated.
2. Inline Execution: For eligible SELECT statements, results are returned inline without creating a job.
3. Lower Latency: Designed to improve overall response time for short queries.
4. Query Result Cache: Includes a cache to improve performance of repeated queries when data remains unchanged.

How to Enable

• Set the jobCreationMode field to JOB_CREATION_OPTIONAL in the jobs.query request body.

Important Notes

• No jobReference is returned for optimized queries; instead, a queryId is provided.
• Use INFORMATION_SCHEMA.JOBS view to get insights about optimized queries.
• BigQuery may still create a job if necessary, returning a jobReference in such cases.
• The useQueryCache parameter (default: true) can be used alongside this feature for potential additional performance gains.

Impact on Developers

• Developers should check for the presence of jobReference in responses, as it may not always be available.
• The job_creation_reason field in INFORMATION_SCHEMA.JOBS can help understand why a job was created for a query.

This update represents Google's ongoing efforts to improve BigQuery's performance and efficiency, particularly for scenarios involving frequent, small-scale queries.

How to Run a Short Query Optimized Query

1. Go to the BigQuery page.
2. Click SQL QUERY (add_box icon).
3. In the query editor, enter a valid GoogleSQL query
4. Click settings More, and then click Short query optimized under Choose query mode. Click CONFIRM to confirm the choice.