Standard SQL consistently slower than Legacy SQL?

Question

We noticed that queries executed with standard SQL are usually a bit slower and many times by a factor 5. The following query that gets the count of hits is done in 1.8s with legacy SQL, but takes 10s using standard SQL. The queries are:

Legacy SQL:

SELECT
  max_time,
  COUNT(*) AS cnt
FROM (
  SELECT
    MAX(hits.time) WITHIN RECORD AS max_time,
  FROM
    [google_analytics.ga_sessions_20160601])
GROUP BY 1
ORDER BY 2 DESC
LIMIT 100;

Standard SQL:

SELECT
  (SELECT MAX(time) FROM sessions.hits) AS max_time,
  COUNT(*) AS cnt
FROM
  `google_analytics.ga_sessions_20160601` AS sessions
GROUP BY 1
ORDER BY 2 DESC
LIMIT 100;

We notice that the amount of "compute" in the first stage is usually a bit higher and there's more wait in the final stage.

Other queries like a simple SELECT COUNT(*) also have similar differences.

Is it true that standard SQL is consistently slower than legacy SQL?

Answer 1

Thanks for the report - we will investigate it. Standard SQL is still in Beta, and there is some known performance work that needs to be done before final release - we will check whether it affects your queries as well.