GoogleSQL query performance best practices

Supported in:

This document helps security analysts, threat hunters, and detection engineers write and optimize GoogleSQL queries in Google Security Operations.

GoogleSQL is a flexible and powerful query language that lets you construct queries in many different ways to achieve the same result. However, your query structure directly affects execution speed and resource consumption. To prevent queries from hitting system resource guardrails and getting terminated, structure your queries to be as efficient as possible.

Use the best practices in this document to write efficient queries and formulate optimization patterns for both Standard SQL and Piped SQL syntax variations.

Before you begin

Run queries in Google SecOps SQL search under strict resource guardrails to ensure platform stability. Follow these initial steps:

  • Narrow the time range: Always limit your query's search time window to the minimum necessary range. Searching over 24 hours executes faster and consumes fewer resources than searching over 30 days.
  • Filter specifically, avoid broad scopes: When searching over larger time windows, make sure your filters are highly specific. Instead of running broad searches, explicitly scope your query to specific user IDs, IP addresses, or unique hostnames to limit the volume of data you retrieve

Optimize self-joins

Joining the massive events table with itself to detect sequential event patterns (for example, finding an account creation followed immediately by deletion) is one of the most resource-intensive operations you can perform.

Optimize self-join queries by filtering out NULL or empty join keys to prevent Cartesian explosions, and by using primitive integer arithmetic on epoch seconds (metadata.event_timestamp.seconds) instead of TIMESTAMP_DIFF to evaluate sliding time windows.

Scenario: Account creation and deletion within 4 hours

The following examples compare unoptimized and optimized methods for querying account creation and deletion events within 4 hours.

Unoptimized self-join

This approach is expensive because it joins the events table to itself without filtering out empty or NULL usernames, creating a Cartesian product that can exhaust memory:

SELECT c.user_id, c.timestamp AS created_at, d.timestamp AS deleted_at
FROM events c
INNER JOIN events d ON c.user_id = d.user_id
WHERE c.event_type = 'USER_CREATION'
  AND d.event_type = 'USER_DELETION'
  AND c.timestamp <= d.timestamp
  AND TIMESTAMP_DIFF(d.timestamp, c.timestamp, HOUR) <= 4;

Optimized Standard SQL join

This approach is efficient because it explicitly filters out NULL and empty userid values (IS NOT NULL and != '') to prevent a Cartesian explosion across events that lack a user ID. Additionally, evaluating the 4-hour (14,400-second) sliding window using primitive integer subtraction on metadata.event_timestamp.seconds executes much faster than calling TIMESTAMP_DIFF:

SELECT 
  c.principal.user.userid AS user_id, 
  TIMESTAMP_SECONDS(c.metadata.event_timestamp.seconds) AS created_at, 
  TIMESTAMP_SECONDS(d.metadata.event_timestamp.seconds) AS deleted_at
FROM events AS c
INNER JOIN events AS d 
  ON c.principal.user.userid = d.principal.user.userid
WHERE c.metadata.event_type = 'USER_CREATION'
  AND d.metadata.event_type = 'USER_DELETION'
  -- CRITICAL OPTIMIZATION: Prevent Cartesian explosion on empty/null userIDs
  AND c.principal.user.userid IS NOT NULL 
  AND c.principal.user.userid != ''
  -- Strict Sliding window: Deletion happens after creation
  AND c.metadata.event_timestamp.seconds <= d.metadata.event_timestamp.seconds
  -- 4 hours = 14,400 seconds (Primitive math is much faster than TIMESTAMP_DIFF)
  AND (d.metadata.event_timestamp.seconds - c.metadata.event_timestamp.seconds) <= 14400;

Optimized Piped SQL join

This approach applies the same join optimizations using Piped SQL syntax, chaining the WHERE filter and SELECT projection stages after the INNER JOIN:

FROM events AS c
INNER JOIN events AS d 
  ON c.principal.user.userid = d.principal.user.userid
|> WHERE c.metadata.event_type = 'USER_CREATION'
  AND d.metadata.event_type = 'USER_DELETION'
  -- Filter invalid IDs
  AND c.principal.user.userid IS NOT NULL 
  AND c.principal.user.userid != ''
  -- Sliding Window Constraint
  AND c.metadata.event_timestamp.seconds <= d.metadata.event_timestamp.seconds
  AND (d.metadata.event_timestamp.seconds - c.metadata.event_timestamp.seconds) <= 14400
|> SELECT 
  c.principal.user.userid AS user_id, 
  TIMESTAMP_SECONDS(c.metadata.event_timestamp.seconds) AS created_at, 
  TIMESTAMP_SECONDS(d.metadata.event_timestamp.seconds) AS deleted_at;

Optimize filtering and column selection

To reduce query execution time and resource consumption, structure your queries to filter data early, select only the necessary columns, and leverage indexed fields.

  • Apply filters early: Apply WHERE predicates as early as possible in your query. This reduces the number of rows that must be processed in subsequent stages, such as aggregations, joins, or sorting.
  • Select specific columns: Avoid using SELECT * in custom analytics queries. Select and project only the columns you actually need. In custom queries, selecting specific columns significantly improves performance compared to retrieving full Unified Data Model (UDM) objects.
  • Filter on indexed fields: Filter on UDM columns that are indexed for fast lookup, such as:
    • principal.ip, target.ip
    • principal.asset.hostname, target.hostname
    • principal.user.userid, principal.user.email_addresses
    • principal.file.md5, principal.file.sha256
    • network.dns.questions.name

Optimize repeated fields (arrays)

Optimize queries that search for values within arrays or repeated fields.

Prefer EXISTS over ARRAY_INCLUDES or IN UNNEST

To check if a value exists within an array or repeated UDM field (for example, principal.ip), you can use several syntax options. Testing in Google SecOps shows that the EXISTS (SELECT 1 FROM UNNEST(...) ...) pattern is the most efficient query structure for searching repeated fields:

-- Recommended: Most Performant
SELECT *
FROM events
WHERE EXISTS(
  SELECT 1
  FROM UNNEST(principal.ip) AS single_ip
  WHERE single_ip = '192.168.1.100'
);

While standard constructs like value IN UNNEST(array) or ARRAY_INCLUDES(array, value) are valid and concise, they may not optimize as well as EXISTS on large datasets.

Use subnet and CIDR matching

When searching for IP addresses within a specific Classless Inter-Domain Routing (CIDR) network range, use the optimized NET.IP_IN_NET(ip, cidr) function instead of executing complex string manipulations or bitwise shifts.

Keep in mind that many UDM IP fields (such as principal.ip) are repeated arrays, you must check them within an EXISTS subquery or unwind them with UNNEST so that the function evaluates each individual scalar string.

Example: Scalar IP column

SELECT principal.hostname, principal.ip
FROM events
WHERE EXISTS(
  SELECT 1 
  FROM UNNEST(principal.ip) AS single_ip 
  WHERE NET.IP_IN_NET(single_ip, '192.168.1.0/24')
);

What's next

For more information about GoogleSQL queries and search optimization in Google Security Operations, see the following:

Need more help? Get answers from Community members and Google SecOps professionals.