CODEWITHSUNDEEP
google-bigquery
RCalaf
RCalaf

Reputation: 810

BigQuery Performance

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

Upvotes: 4

Views: 2151

Answers (2)

Felipe Hoffa
Felipe Hoffa

Reputation: 59355

BigQuery users execute their queries in shared infrastructure. The benefit is substantially low querying prices (without having to pay hourly prices), but execution times become variable depending on how many concurrent queries from other users are executing at the same time (that's why BQ enforces concurrent queries quota limits for each user).

If consistency is required, BigQuery also offers an exclusive infrastructure mode to customers, where only their queries run on machines dedicated exclusively to them. However, these machines need to be reserved on a monthly basis. As an additional benefit, these customers are not subjected to concurrent queries quotas, since it's their own space to play anyways.

https://developers.google.com/bigquery/pricing#reserved_cap

Upvotes: 1

Jabberwockey
Jabberwockey

Reputation: 493

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.

Upvotes: 2

Related Questions