BigQuery Performance

Question

Is there any way to stabilize the response time of queries? For example, this two jobs that execute the same query, have different response times:

JobId: job_gzTkFvGculpLw0tQRMZvj6rskMI Query Time: 00:00:09.2827336

JobId: job_9wXD9ONxDoI87lD6lOXZJSEM1aE Query Time: 00:00:28.1064037

Why does the same query take very different response times?

**EDIT: The projectId is nostrum.eu:nostrum

FYI: If you post a project id along with a job id it is easier for a bigquery engineer to look up what happened in the logs. — Jordan Tigani
Now, this jobId job_KsGKrJuoE9_LvuDBKNnIZnaZn5s has taken more than one minute to response. — RCalaf
FYI I looked into these jobs and this does not seem to be expected behavior. I'll respond with a more complete answer after further diagnosis. — Jordan Tigani
@JordanTigani it's not related to this, but also check this support request (ref is mentioned), Mario looks like cannot handle it. stackoverflow.com/questions/25792224/… — Pentium10

Jabberwockey Jabberwockey · Accepted Answer · 2014-09-15T17:45:04

By default, BigQuery uses Query caching to speed up response times for common queries that may be used multiple times.

This is important to remember if you are loading or streaming data constantly into the database, because your cached query results may not be the most up to date results for the query.

If you want to disable query caching, you can set the parameter in the query body that you send to the API:

{
  "kind": "bigquery#queryRequest",
  "query": query,
  "defaultDataset": {
    "datasetId": string,
    "projectId": string
  },
  "useQueryCache": False
}

This should make it so that your repeat requests of the same query will have relatively the same response time, but you will be billed for each query request, as opposed to being billed for just the one request that it takes to cache the results.

BigQuery Performance

2 Answers