**Performance Characteristics:**

1. Execution Count:

- Correlated: N executions (once per outer row)

- Non-correlated: 1 execution

- Impact: Linear scaling with outer table size

2. Per-Execution Cost:

- Without index: Full table scan (10,000+ rows)

- With index: Index seek (10-100 rows)

- Impact: 100-1000x difference per execution

3. Total Cost:

- No index: N × Full scan = O(N²)

- With index: N × Index seek = O(N log N)

- Window function: Single scan = O(N)

**Real-World Performance Numbers:**

Scenario: 10,000 employees, calculate department averages

Method 1: Correlated subquery without index

- Execution count: 10,000

- Per execution: Full scan (10,000 rows)

- Total operations: 100,000,000

- Query time: 8000ms (8 seconds)

- Status: ❌ Unacceptable for production

Method 2: Correlated subquery with index

- Execution count: 10,000

- Per execution: Index seek (~50 rows)

- Total operations: 500,000

- Query time: 450ms

- Status: ⚠️ Acceptable but not optimal

Method 3: JOIN with pre-aggregation

- Execution count: 1 aggregation + 1 join

- Operations: 10,000 (aggregate) + 10,000 (join)

- Total operations: 20,000

- Query time: 120ms

- Status: ✓ Good performance

Method 4: Window function

- Execution count: 1 scan

- Operations: 10,000 (single pass)

- Total operations: 10,000

- Query time: 95ms

- Status: ✓ Optimal performance

**Optimization Strategies:**