Fix AMC SQL Query Performance Before Timeout

Run the slow Amazon Marketing Cloud (AMC) query through a query-performance Skill that reads the live query context with the Amazon Ads MCP, checks each change against Amazon Agent Atlas, and returns a smaller query you can test before another long run. The answer is not one clever SQL trick. It is a fixed sequence: filter earlier, reduce rows before joins, sample exploratory work correctly, and shrink the test window until you know whether the problem is the SQL or the workload.

The question that kicked this off was blunt: "Our AMC queries either time out or run for an hour. What do we actually change?" I gave our agent the SQL, the AMC instance, and the date range. It came back with the exact expensive operations, a rewritten query, and a validation plan instead of a generic list of database tips.

A plain ChatGPT or Claude chat hits three walls here. It has no access to your data, so it cannot inspect the live instance, query, or selected dates unless you paste them in. It has no way to take action, so it cannot validate or run the correction in AMC. And it has generic knowledge, not Amazon's, so it misses the execution and sampling rules Amazon documents for AMC. The MCP supplies live data and the tools to act, Atlas supplies the rule book, and Skills preserve the safe sequence. The Selling Partner MCP plays the same role when a related workflow needs catalog or retail inputs.

Early, sargable filters remove work before joins begin

AMC reads sources, applies WHERE filters, performs joins and aggregations, then applies HAVING. That makes early filtering the first place I look. A filter that wraps a field in a function can force the engine to evaluate far more records than a narrow, sargable predicate.

Atlas retrieved this documented before-and-after pair from Optimize AMC SQL queries and How to optimize AMC SQL queries. The first query is functionally valid, but ARRAY_CONTAINS makes the filter non-sargable:

SELECT
  campaign_id_string,
  COUNT(DISTINCT user_id) AS users
FROM
  amazon_attributed_events_by_traffic_time
WHERE
  ARRAY_CONTAINS(ARRAY [ '1234567', '2345678' ], campaign_id_string)
  AND user_id IS NOT NULL
GROUP BY
  1

The documented correction defines the campaign scope once and joins to it:

WITH campaigns (campaign_id_string) AS (
  VALUES
    ('1234567'),
    ('2345678')
)
SELECT
  a.campaign_id_string,
  COUNT(DISTINCT user_id) AS users
FROM
  amazon_attributed_events_by_traffic_time a
  INNER JOIN campaigns c ON (a.campaign_id_string = c.campaign_id_string)
WHERE
  user_id IS NOT NULL
GROUP BY
  1

The Skill does not declare victory because the SQL looks cleaner. The Amazon Ads MCP runs the corrected block, the Skill verifies that the output grain is still campaign-level, and the run record shows whether it completed on the same test window.

Smaller CTEs and stricter joins prevent row multiplication

CTEs are useful structure, not a performance guarantee. The gain comes from reducing each intermediate result to the smallest useful grain before the next join. An unaggregated CTE can carry duplicate rows forward, and a later join can turn that extra work into wrong metrics.

The agent's audit uses four checks:

Amazon's example is a useful warning: joining 10 impressions to two purchases at the wrong grain can report 20 conversions and 20 impressions. That is not merely slow SQL. It is a fast route to a confident, wrong decision. The Skill checks selected columns, grouping grain, join predicates, and whether compatible metric streams can use UNION ALL before it proposes a rewrite.

Random sampling speeds exploration only after deduplication

Random sampling belongs in exploratory analysis, before you spend hours testing a full population. The order matters: define the eligible population, deduplicate to one row per user, and only then apply random(). Sampling event rows first gives frequently active users more chances to enter the sample.

This complete Atlas-reconstructed query samples 10% of unique users, then applies the same user filter to impressions and clicks:

-- Instructional Query: Join Impressions and Clicks using UNION ALL with random sampling--
WITH
  user_filter AS (
    SELECT
      user_id
    FROM
      (
        SELECT
          user_id
        FROM
          dsp_impressions
        WHERE
          campaign_id IN (111111111111)
          AND impressions > 0
          AND user_id IS NOT NULL
        GROUP BY
          1
      )
    WHERE
      random() <= 0.1
  ),
  imp AS (
    SELECT
      campaign_id,
      campaign,
      SUM(impressions) AS impressions,
      0 AS clicks
    FROM
      dsp_impressions
    WHERE
      campaign_id IN (111111111111)
      AND user_id IN (
        SELECT
          user_id
        FROM
          user_filter
      )
    GROUP BY
      1,
      2
  ),
  clicks AS (
    SELECT
      campaign_id,
      campaign,
      0 AS impressions,
      SUM(clicks) AS clicks
    FROM
      dsp_clicks
    WHERE
      campaign_id IN (111111111111)
      AND user_id IN (
        SELECT
          user_id
        FROM
          user_filter
      )
    GROUP BY
      1,
      2
  ),
  combined AS (
    SELECT
      campaign_id,
      campaign,
      impressions,
      clicks
    FROM
      imp
    UNION ALL
    SELECT
      campaign_id,
      campaign,
      impressions,
      clicks
    FROM
      clicks
  )
SELECT
  campaign_id,
  campaign,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks,
  /*
  ------- Customization Instructions -------
  Adding CTR calculation
  */
  (SUM(clicks) / SUM(impressions)) AS CTR
FROM
  combined
GROUP BY
  1,
  2

The validation rule is practical. On a 10% sample, counts and sums should land near 10% of the full run, while averages and rates should remain similar. I run both versions over the same small window first. Sampling is a diagnostic accelerator; the final decision still gets the full-population run.

A shorter window separates SQL problems from capacity limits

I test a compute-heavy query on one day before asking it to scan weeks of data. If it still struggles, the Skill narrows the advertiser, campaign set, ad product, or geography. Amazon's optimization guide then recommends reducing a one-month run to two weeks or one week when the query continues to time out.

That sequence produces a useful escalation instead of "AMC is slow." If the reduced query still fails, the Skill assembles the SQL ID, Instance ID, screenshots, and the query for AMC support. One documented compute-heavy use case calls out a six-hour timeout window, but the workflow treats that as context for that example, not a promise that every AMC workload gets the same limit.

What happens next

The output is a query-performance review: the expensive operations, the exact rewrite, the test window, the validation result, and the escalation packet if the problem remains. I run the corrected SQL through the Amazon Ads MCP, compare its grain and core rates with the original, then widen the date range one step at a time.

Once the query holds, I save the audit as a recurring Skill and attach its run evidence to the Amazon Agent Data layer. That turns a late-night timeout hunt into the same review every time. It also fits the broader platform: the Amazon Ads MCP handles AMC, the Selling Partner MCP can supply adjacent retail context, Skills preserve the workflow, and Atlas keeps the rules current. If you are still building the query itself, start with the first AMC SQL queries before optimizing it.

A slow AMC query becomes manageable when the agent can show what is expensive, prove the correction on live data, and preserve the reasoning for the next run.

Next in the series: validate the corrected query safely in the AMC SQL sandbox before you widen the window.