> ## Documentation Index
> Fetch the complete documentation index at: https://docs.narrative.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Performance Patterns

> Optimization recipes for faster, more efficient NQL queries

This cookbook contains patterns for optimizing NQL query performance. Apply these techniques to reduce execution time and costs.

***

## Filter early and aggressively

### Apply filters before joins

Filter data in subqueries or CTEs before joining to reduce the amount of data processed:

```sql theme={null}
-- Better: Filter before joining
WITH active_users AS (
  SELECT user_id, email
  FROM company_data."100"
  WHERE
    status = 'active'
    AND created_at >= CURRENT_DATE - INTERVAL '90' DAY
),
recent_orders AS (
  SELECT user_id, order_id, amount
  FROM company_data."200"
  WHERE order_date >= CURRENT_DATE - INTERVAL '30' DAY
)
SELECT
  u.user_id,
  u.email,
  o.order_id,
  o.amount
FROM active_users u
INNER JOIN recent_orders o ON u.user_id = o.user_id
```

```sql theme={null}
-- Avoid: Filtering after joining
SELECT
  u.user_id,
  u.email,
  o.order_id,
  o.amount
FROM company_data."100" u
INNER JOIN company_data."200" o ON u.user_id = o.user_id
WHERE
  u.status = 'active'
  AND u.created_at >= CURRENT_DATE - INTERVAL '90' DAY
  AND o.order_date >= CURRENT_DATE - INTERVAL '30' DAY
```

### Use price filtering early

Add `_price_cpm_usd` filters to limit data costs:

```sql theme={null}
SELECT user_id, email, category, event_date
FROM company_data."123"
WHERE
  _price_cpm_usd <= 1.00  -- Apply price filter early
  AND category = 'target'
  AND event_date >= CURRENT_DATE - INTERVAL '30' DAY
```

***

## Use QUALIFY instead of subqueries

### Deduplication with QUALIFY

QUALIFY is more efficient than subquery-based deduplication:

```sql theme={null}
-- Better: QUALIFY
SELECT user_id, email, last_seen
FROM company_data."123"
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY last_seen DESC
) = 1
```

```sql theme={null}
-- Avoid: Subquery approach
SELECT user_id, email, last_seen
FROM (
  SELECT
    user_id,
    email,
    last_seen,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY last_seen DESC) AS rn
  FROM company_data."123"
) sub
WHERE rn = 1
```

### Combined filtering with QUALIFY

Apply multiple window conditions efficiently:

```sql theme={null}
SELECT
  user_id,
  email,
  category
FROM company_data."123"
QUALIFY
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) = 1
  AND SUM(1) OVER (PARTITION BY category) >= 100  -- Only categories with 100+ rows
```

***

## Optimize joins

### Use fully qualified column names

Explicit column references help the query optimizer:

```sql theme={null}
SELECT
  company_data."100".user_id,
  company_data."100".email,
  company_data."200".order_id
FROM company_data."100"
INNER JOIN company_data."200"
  ON company_data."100".user_id = company_data."200".customer_id
```

### Avoid OR in join conditions

OR conditions prevent efficient join algorithms:

```sql theme={null}
-- Avoid: OR in join condition
SELECT t1.id, t1.value, t2.related_value
FROM company_data."100" t1
INNER JOIN company_data."200" t2
  ON t1.id = t2.id OR t1.alt_id = t2.alt_id
```

```sql theme={null}
-- Better: Use COALESCE
SELECT t1.id, t1.value, t2.related_value
FROM company_data."100" t1
INNER JOIN company_data."200" t2
  ON COALESCE(t1.id, t1.alt_id) = COALESCE(t2.id, t2.alt_id)
```

```sql theme={null}
-- Or: Union separate queries
SELECT t1.id, t1.value, t2.related_value
FROM company_data."100" t1
INNER JOIN company_data."200" t2 ON t1.id = t2.id
UNION
SELECT t1.id, t1.value, t2.related_value
FROM company_data."100" t1
INNER JOIN company_data."200" t2 ON t1.alt_id = t2.alt_id
WHERE t1.id IS NULL OR t2.id IS NULL
```

### Aggregate before joining

Pre-aggregate large tables to reduce join complexity:

```sql theme={null}
-- Better: Aggregate first
SELECT
  c.customer_id,
  c.name,
  order_stats.total_orders,
  order_stats.total_amount
FROM company_data."100" c
INNER JOIN (
  SELECT
    customer_id,
    COUNT(1) AS total_orders,
    SUM(amount) AS total_amount
  FROM company_data."200"
  GROUP BY customer_id
) order_stats ON c.customer_id = order_stats.customer_id
```

***

## Partitioning strategies

### Partition by date for time-series data

```sql theme={null}
CREATE MATERIALIZED VIEW "daily_events"
PARTITIONED_BY event_date DAY
AS (
  SELECT
    user_id,
    event_type,
    DATE_TRUNC('day', event_timestamp) AS event_date
  FROM company_data."123"
  WHERE event_timestamp >= CURRENT_DATE - INTERVAL '90' DAY
)
BUDGET 100 USD
```

### Monthly partitions for longer retention

```sql theme={null}
CREATE MATERIALIZED VIEW "monthly_summary"
PARTITIONED_BY report_month MONTH
AS (
  SELECT
    DATE_TRUNC('month', event_date) AS report_month,
    category,
    COUNT(1) AS event_count,
    SUM(amount) AS total_amount
  FROM company_data."123"
  GROUP BY DATE_TRUNC('month', event_date), category
)
BUDGET 50 USD
```

***

## Budget management

### Set appropriate budget limits

Match budget to expected data volume:

```sql theme={null}
-- Daily budget for daily refreshes
CREATE MATERIALIZED VIEW "daily_data"
REFRESH_SCHEDULE = '@daily'
AS (SELECT user_id, email, event_date FROM company_data."123" WHERE event_date = CURRENT_DATE - INTERVAL '1' DAY)
LIMIT 10 USD PER CALENDAR_DAY

-- Monthly budget for larger datasets
CREATE MATERIALIZED VIEW "monthly_data"
REFRESH_SCHEDULE = '@monthly'
AS (SELECT user_id, email, created_at FROM company_data."123")
LIMIT 500 USD PER CALENDAR_MONTH
```

### Combine price and date filters

Maximize value by filtering on both price and recency:

```sql theme={null}
SELECT user_id, email, event_timestamp
FROM company_data."123"
WHERE
  _price_cpm_usd <= 0.75
  AND event_timestamp >= CURRENT_DATE - INTERVAL '14' DAY
```

***

## Efficient aggregations

### Use APPROX\_COUNT\_DISTINCT instead of COUNT(DISTINCT ...)

`APPROX_COUNT_DISTINCT` is significantly faster and cheaper than `COUNT(DISTINCT ...)`. It returns exact results for low-cardinality columns and near-exact results at scale. Use it for any query that doesn't require a guaranteed exact count.

```sql theme={null}
-- Better: Use APPROX_COUNT_DISTINCT for unique counts
SELECT
  category,
  APPROX_COUNT_DISTINCT(user_id) AS unique_users
FROM company_data."123"
GROUP BY category
```

```sql theme={null}
-- Avoid: COUNT(DISTINCT ...) is slower and more expensive
SELECT
  category,
  COUNT(DISTINCT user_id) AS unique_users
FROM company_data."123"
GROUP BY category
```

Reserve `COUNT(DISTINCT ...)` for cases where an exact count drives business logic—for example, threshold comparisons in `HAVING` or `CASE WHEN` clauses:

```sql theme={null}
-- Exact count is appropriate here because the threshold matters
SELECT category, COUNT(DISTINCT user_id) AS unique_users
FROM company_data."123"
GROUP BY category
HAVING COUNT(DISTINCT user_id) >= 5
```

### Pre-aggregate for common queries

Create summary views for frequently queried aggregations:

```sql theme={null}
CREATE MATERIALIZED VIEW "daily_category_stats"
REFRESH_SCHEDULE = '@daily'
AS (
  SELECT
    DATE_TRUNC('day', event_timestamp) AS event_date,
    category,
    COUNT(1) AS events,
    APPROX_COUNT_DISTINCT(user_id) AS users,
    SUM(amount) AS total
  FROM company_data."123"
  WHERE event_timestamp >= CURRENT_DATE - INTERVAL '30' DAY
  GROUP BY DATE_TRUNC('day', event_timestamp), category
)
BUDGET 25 USD PER CALENDAR_DAY
```

***

## Limit result sets

### Always use LIMIT during exploration

```sql theme={null}
-- While exploring data
SELECT user_id, email, category
FROM company_data."123"
WHERE category = 'test'
LIMIT 100 ROWS

-- Or with offset for pagination
LIMIT 100 OFFSET 100 ROWS
```

***

## Efficient date handling

### Use DATE\_TRUNC for grouping

```sql theme={null}
SELECT
  DATE_TRUNC('day', event_timestamp) AS day,
  COUNT(1) AS events
FROM company_data."123"
GROUP BY DATE_TRUNC('day', event_timestamp)
```

### Avoid functions on indexed columns in WHERE

```sql theme={null}
-- Better: Compare directly
WHERE event_date >= '2024-01-01'

-- Avoid: Function on column
WHERE EXTRACT(YEAR FROM event_date) = 2024
```

***

## CTEs for complex queries

### Use CTEs for readability and potential optimization

```sql theme={null}
WITH
  base_data AS (
    SELECT user_id, event_date, amount
    FROM company_data."123"
    WHERE
      event_date >= CURRENT_DATE - INTERVAL '30' DAY
      AND _price_cpm_usd <= 1.00
  ),
  user_metrics AS (
    SELECT
      user_id,
      COUNT(1) AS event_count,
      SUM(amount) AS total_amount
    FROM base_data
    GROUP BY user_id
  ),
  ranked_users AS (
    SELECT
      user_id,
      event_count,
      total_amount,
      RANK() OVER (ORDER BY total_amount DESC) AS rank
    FROM user_metrics
  )
SELECT user_id, event_count, total_amount, rank
FROM ranked_users
WHERE rank <= 100
```

***

## Anti-patterns to avoid

### Use explicit column lists (wildcards not supported)

NQL requires explicit column lists—`SELECT *` and `COUNT(*)` are not supported. See [Explicit Column Selection](/nql/general/explicit-columns) for why this design choice improves cost transparency, governance, and query optimization.

```sql theme={null}
-- Not supported in NQL
SELECT * FROM company_data."123"

-- Required: Select only needed columns
SELECT user_id, email, created_at FROM company_data."123"

-- Not supported: COUNT(*)
SELECT COUNT(*) FROM company_data."123"

-- Required: Use COUNT(1) for row counts
SELECT COUNT(1) FROM company_data."123"
```

### Don't use LIKE with leading wildcards when possible

```sql theme={null}
-- Slow: Leading wildcard
WHERE email LIKE '%@gmail.com'

-- Better: Use REGEXP_EXTRACT or suffix column
WHERE REGEXP_EXTRACT(email, '@(.+)$') = 'gmail.com'
```

### Don't nest too many subqueries

```sql theme={null}
-- Avoid: Deeply nested subqueries
SELECT user_id, email FROM (
  SELECT user_id, email FROM (
    SELECT user_id, email FROM (
      SELECT user_id, email FROM company_data."123"
    ) a
  ) b
) c

-- Better: Use CTEs
WITH step1 AS (SELECT user_id, email FROM company_data."123"),
     step2 AS (SELECT user_id, email FROM step1),
     step3 AS (SELECT user_id, email FROM step2)
SELECT user_id, email FROM step3
```

***

## Related content

<CardGroup cols={2}>
  <Card title="Query Optimization Guide" icon="gauge-high" href="/guides/nql/query-optimization">
    Detailed optimization techniques
  </Card>

  <Card title="Join Performance" icon="link" href="/concepts/nql/join-performance">
    Understanding join performance implications
  </Card>

  <Card title="Common Queries" icon="copy" href="/cookbooks/nql/common-queries">
    Copy-paste query patterns
  </Card>

  <Card title="Complex Joins" icon="diagram-project" href="/cookbooks/nql/complex-joins">
    Advanced join recipes
  </Card>
</CardGroup>