base/tracing/stdlib/chrome/speedometer_2_1.sql

-- Copyright 2024 The Chromium Authors
-- Use of this source code is governed by a BSD-style license that can be
-- found in the LICENSE file.

-- List Speedometer 2.1 test marks. Used to find relevant slices.
CREATE PERFETTO VIEW _chrome_speedometer_2_1_mark_name (
  -- Expected slice name
  name STRING,
  -- Suite name
  suite_name STRING,
  -- Test name
  test_name STRING,
  -- Mark type
  mark_type STRING
) AS
WITH
  data(suite_name, test_name) AS (
    SELECT
      *
    FROM (VALUES
      ('Angular2-TypeScript-TodoMVC', 'Adding100Items'),
      ('Angular2-TypeScript-TodoMVC', 'CompletingAllItems'),
      ('Angular2-TypeScript-TodoMVC', 'DeletingItems'),
      ('AngularJS-TodoMVC', 'Adding100Items'),
      ('AngularJS-TodoMVC', 'CompletingAllItems'),
      ('AngularJS-TodoMVC', 'DeletingAllItems'),
      ('BackboneJS-TodoMVC', 'Adding100Items'),
      ('BackboneJS-TodoMVC', 'CompletingAllItems'),
      ('BackboneJS-TodoMVC', 'DeletingAllItems'),
      ('Elm-TodoMVC', 'Adding100Items'),
      ('Elm-TodoMVC', 'CompletingAllItems'),
      ('Elm-TodoMVC', 'DeletingItems'),
      ('EmberJS-Debug-TodoMVC', 'Adding100Items'),
      ('EmberJS-Debug-TodoMVC', 'CompletingAllItems'),
      ('EmberJS-Debug-TodoMVC', 'DeletingItems'),
      ('EmberJS-TodoMVC', 'Adding100Items'),
      ('EmberJS-TodoMVC', 'CompletingAllItems'),
      ('EmberJS-TodoMVC', 'DeletingItems'),
      ('Flight-TodoMVC', 'Adding100Items'),
      ('Flight-TodoMVC', 'CompletingAllItems'),
      ('Flight-TodoMVC', 'DeletingItems'),
      ('Inferno-TodoMVC', 'Adding100Items'),
      ('Inferno-TodoMVC', 'CompletingAllItems'),
      ('Inferno-TodoMVC', 'DeletingItems'),
      ('Preact-TodoMVC', 'Adding100Items'),
      ('Preact-TodoMVC', 'CompletingAllItems'),
      ('Preact-TodoMVC', 'DeletingItems'),
      ('React-Redux-TodoMVC', 'Adding100Items'),
      ('React-Redux-TodoMVC', 'CompletingAllItems'),
      ('React-Redux-TodoMVC', 'DeletingItems'),
      ('React-TodoMVC', 'Adding100Items'),
      ('React-TodoMVC', 'CompletingAllItems'),
      ('React-TodoMVC', 'DeletingAllItems'),
      ('Vanilla-ES2015-Babel-Webpack-TodoMVC', 'Adding100Items'),
      ('Vanilla-ES2015-Babel-Webpack-TodoMVC', 'CompletingAllItems'),
      ('Vanilla-ES2015-Babel-Webpack-TodoMVC', 'DeletingItems'),
      ('Vanilla-ES2015-TodoMVC', 'Adding100Items'),
      ('Vanilla-ES2015-TodoMVC', 'CompletingAllItems'),
      ('Vanilla-ES2015-TodoMVC', 'DeletingItems'),
      ('VanillaJS-TodoMVC', 'Adding100Items'),
      ('VanillaJS-TodoMVC', 'CompletingAllItems'),
      ('VanillaJS-TodoMVC', 'DeletingAllItems'),
      ('VueJS-TodoMVC', 'Adding100Items'),
      ('VueJS-TodoMVC', 'CompletingAllItems'),
      ('VueJS-TodoMVC', 'DeletingAllItems'),
      ('jQuery-TodoMVC', 'Adding100Items'),
      ('jQuery-TodoMVC', 'CompletingAllItems'),
      ('jQuery-TodoMVC', 'DeletingAllItems')) AS _values
  ),
  mark_type(mark_type) AS (
    SELECT
      *
    FROM (VALUES
      ('start'),
      ('sync-end'),
      ('async-end')) AS _values
  )
SELECT
  suite_name || '.' || test_name || '-' || mark_type AS name,
  suite_name,
  test_name,
  mark_type
FROM data, mark_type;

-- Augmented slices for Speedometer measurements.
-- These are the intervals of time Speedometer uses to compute the final score.
-- There are two intervals that are measured for every test: sync and async
-- sync is the time between the start and sync-end marks, async is the time
-- between the sync-end and async-end marks.
CREATE PERFETTO TABLE chrome_speedometer_2_1_measure (
  -- Start timestamp of the measure slice
  ts TIMESTAMP,
  -- Duration of the measure slice
  dur DURATION,
  -- Full measure name
  name STRING,
  -- Speedometer iteration the slice belongs to.
  iteration LONG,
  -- Suite name
  suite_name STRING,
  -- Test name
  test_name STRING,
  -- Type of the measure (sync or async)
  measure_type STRING
) AS
WITH
  mark AS (
    SELECT
      s.id AS slice_id,
      rank() OVER (PARTITION BY name ORDER BY ts ASC) AS iteration,
      m.suite_name,
      m.test_name,
      m.mark_type
    -- Join with _chrome_speedometer_2_1_mark_name to look only at slices with expected names.
    FROM slice AS s
    JOIN _chrome_speedometer_2_1_mark_name AS m
      USING (name)
    WHERE
      category = 'blink.user_timing'
  ),
  -- Get the 3 test timestamps (start, sync-end, async-end) in one row. Using a
  -- the LAG window function and partitioning by test. 2 out of the 3 rows
  -- generated per test will have some NULL ts values.
  augmented AS (
    SELECT
      iteration,
      suite_name,
      test_name,
      ts AS async_end_ts,
      lag(ts, 1) OVER (PARTITION BY iteration, suite_name, test_name ORDER BY ts ASC) AS sync_end_ts,
      lag(ts, 2) OVER (PARTITION BY iteration, suite_name, test_name ORDER BY ts ASC) AS start_ts,
      count() OVER (PARTITION BY iteration, suite_name, test_name ORDER BY ts ASC) AS mark_count
    FROM mark
    JOIN slice
      USING (slice_id)
  ),
  filtered AS (
    SELECT
      *
    FROM augmented
    -- This server 2 purposes: make sure we have all the marks (think truncated
    -- trace), and remove the NULL ts values due to the LAG window function.
    WHERE
      mark_count = 3
  ),
  base AS (
    SELECT
      sync_end_ts AS ts,
      async_end_ts - sync_end_ts AS dur,
      iteration,
      suite_name,
      test_name,
      'async' AS measure_type
    FROM filtered
    UNION ALL
    SELECT
      start_ts AS ts,
      sync_end_ts - start_ts AS dur,
      iteration,
      suite_name,
      test_name,
      'sync' AS measure_type
    FROM filtered
  )
SELECT
  ts,
  dur,
  suite_name || '.' || test_name || '-' || measure_type AS name,
  iteration,
  suite_name,
  test_name,
  measure_type
FROM base;

-- Slice that covers one Speedometer iteration.
-- This slice is actually estimated as a default Speedometer run will not emit
-- marks to cover this interval. The metrics associated are the same ones
-- Speedometer would output, but note we use ns precision (Speedometer uses
-- ~100us) so the actual values might differ a bit. Also note Speedometer
-- returns the values in ms these here and in ns.
CREATE PERFETTO TABLE chrome_speedometer_2_1_iteration (
  -- Start timestamp of the iteration
  ts TIMESTAMP,
  -- Duration of the iteration
  dur DURATION,
  -- Iteration name
  name STRING,
  -- Iteration number
  iteration LONG,
  -- Geometric mean of the suite durations for this iteration.
  geomean DOUBLE,
  -- Speedometer score for this iteration (The total score for a run in the
  -- average of all iteration scores).
  score DOUBLE
) AS
SELECT
  min(start) AS ts,
  max(end) - min(start) AS dur,
  'iteration-' || iteration AS name,
  iteration,
  -- Compute geometric mean using LN instead of multiplication to prevent
  -- overflows
  exp(avg(ln(suite_total))) AS geomean,
  1000 / exp(avg(ln(suite_total))) * 60 / 3 AS score
FROM (
  SELECT
    iteration,
    sum(dur / (
      1000.0 * 1000.0
    )) AS suite_total,
    min(ts) AS start,
    max(ts + dur) AS end
  FROM chrome_speedometer_2_1_measure
  GROUP BY
    suite_name,
    iteration
)
GROUP BY
  iteration;

-- Returns the Speedometer 2.1 score for all iterations in the trace
CREATE PERFETTO FUNCTION chrome_speedometer_2_1_score()
-- Speedometer 2.1 score
RETURNS DOUBLE AS
SELECT
  avg(score)
FROM chrome_speedometer_2_1_iteration;

-- Returns the utid for the main thread that ran Speedometer 2.1
CREATE PERFETTO FUNCTION chrome_speedometer_2_1_renderer_main_utid()
-- Renderer main utid
RETURNS LONG AS
SELECT
  utid
FROM thread_track
WHERE
  id IN (
    SELECT
      track_id
    FROM slice
    JOIN _chrome_speedometer_2_1_mark_name
      USING (name)
    WHERE
      category = 'blink.user_timing'
  );