Improving Query Performance for Large Tables Using CTAS (Performance Mode)

Overview

When a query processes a very large table (for example, billions of records), it can sometimes fail or take an unusually long time to complete due to system resource limits.

Amperity provides a built-in optimization feature called Performance Mode (CTAS) — short for Create Table As Select. Enabling this mode can significantly improve query performance for large outputs and reduce the chances of query failure.

 

Why This Happens

If your table (such as Campaign_Recipients) has accumulated a very large number of records, queries that read or join from it may:

  • Take an extended time to execute

  • Fail with a “system resource limitation” error

  • Delay or block downstream orchestrations

In such cases, enabling CTAS can optimize how the data is processed and improve stability.

Solution: Enable Performance Mode (CTAS)

You can enable Performance Mode directly from the Amperity Query Editor.

Steps to Enable Performance Mode

  1. Open the Query in Amperity.

  2. Click Query Details in the upper-right corner.

  3. Toggle Enable Performance Mode (CTAS).

  4. Save and re-run your query.

Expected Results

After enabling CTAS, queries that previously took hours can typically complete in a few minutes.

✅ Example improvement observed:

  • Original runtime: ~1 hour

  • Optimized runtime: ~45 seconds

  • Rows returned: ~116 million

These improvements vary by query complexity and dataset size but are usually consistent across runs.

Best Practices

  • Review your query results after enabling CTAS to ensure data accuracy.

  • Use CTAS for queries that return large result sets or feed into orchestrations.

  • Avoid applying it to lightweight queries that already complete quickly.

  • Periodically monitor table growth (especially tables like Campaign_Recipients) to prevent performance bottlenecks.

Summary

Enabling Performance Mode (CTAS) is a reliable and low-impact way to optimize large queries in Amperity.
It improves stability, speeds up runtime, and prevents resource-related failures — without affecting data freshness or accuracy.

Applies To

  • Amperity Query Editor

  • Orchestration Workflows

  • Large Table Optimization