Do we need to SELECT 1 before each query?

[max-sixty]

With the recent clustering release, we're looking at whether we can use BQ for queries which previously required an indexed DB.

For these, the overhead is more important than the throughput. The overhead is high - in the case that the query is cached (if not, add 1s to all these, for a 700 row query on a small cluster):

• 73ms: query time reported by BQ
• 1160ms: query time logged by pandas-gbq
• 3100ms: wall time reported by %timeit -n 1 -r 1 gbq.read_gbq(query)

The latter two are particularly far apart. One of the reasons for this is that we issue a SELECT 1 query before each query the user submits. This happens as part of _try_credentials: https://github.com/pydata/pandas-gbq/blob/e753cc4e61b0089dcea6330eb8c77e93a62984a7/pandas_gbq/auth.py#L281

Is this required? Could we instead attempt the query, and only attempt different auth methods if there's a failure?

[tswast]

Is this required?

The whole auth chain adds quite a few request round-trips. One think I'd like (#161) is to have a credentials argument (or possibly a client argument) which would bypass all of those requests.

[tswast]

Also in #161, I'd like to cache the credentials after the first call in a global Context object to avoid subsequent unnecessary auth calls.

[max-sixty]

Auth takes a long time! googleapis/google-auth-library-python#285

At the moment, the latency overhead basically means we can't use BQ for our shorter queries (5-10k rows) which we've traditionally run off MSSQL.

How much work do you think caching the creds would be? Given that the non-query part is 75% of the query time, it would be impactful

[tswast]

Not much work, honestly. I think we can follow the same model as google.cloud.bigquery.magics with a similar Context class to cache the project_id and credentials.

[max-sixty]

I think we can close this - now it's a single call per session?