Database Reference
In-Depth Information
'''
GROUP BY 1
ORDER BY 3 DESC
LIMIT 100''', TOP_APPS_ID)
The background task would need to periodically invoke
update_top_apps(jobs)
and the dashboard page will read the cached
results using
read_cache(tabledata, TOP_APPS_ID)
. Following are
three points to note:
• The update is atomic.
• Results pagination will be consistent even with the background update.
(See Chapter 7, “Running Queries.”)
• Accessing the rows from the result table is fast and cheap (free).
The first two points deserve further explanation. In cases in which the
results cannot be fetched in a single request, BigQuery guarantees consistent
pagination even if the table is changing underneath the reader. This means
that no additional coordination (locking, for example) is required between
the background task and the dashboard handler. Also it is worth noting that
the background task does not bother to wait for the completion of the job or
check for success. In the rare event that there is a failure for some reason,
the results will be a little staler than usual but the next scheduled invocation
will fix the problem. Note that the age of the results can be determined
by observing the modification time of the table. If it is appropriate, the
dashboard page can either report an error or directly run the query if the
results in the cache are too old.
The aim of the strategies discussed in this section is to cache the results of
your queries. Often, this can be achieved by simply adjusting the query. In
other cases, you need to control how frequently the query is executed. Let's
go back to the question of how many times this query would need to be run.
With this cron-based scheme, it would be executed roughly twice an hour or
approximately 50 times a day. At the modest usage levels we assumed, that
is a 30-fold reduction in query volume compared to a design that runs the
query for each request. In addition, the latency of the dashboard page will
also be reduced.
