Answer: a materialized view

## Detailed Analysis ### Scenario Requirements: - Complex SELECT query with multiple JOIN and CASE statements - Data transformation for inventory reports - Reports run once daily with additional WHERE clauses - Goal: Minimize query execution time - Environment: Azure Synapse Analytics dedicated SQL pool ### Option Analysis: **B. Materialized View - CORRECT** - **Optimal Choice**: Materialized views store precomputed results of complex queries, making them ideal for this scenario. - **Performance Benefits**: Since the reports run once daily, the materialized view can be refreshed daily to capture the transformed data, then serve multiple reports with different WHERE clauses efficiently. - **Flexibility**: Materialized views support query rewrite, allowing the optimizer to use the materialized view even when reports apply additional WHERE clauses. - **Complex Query Handling**: Materialized views are specifically designed to optimize complex queries with JOINs and transformations. **A. Ordered Clustered Columnstore Index - NOT OPTIMAL** - While columnstore indexes improve query performance for analytical workloads, they don't precompute complex transformations. - Each report would still need to execute the full complex query with JOINs and CASE statements. - Doesn't address the core requirement of making pre-transformed data available. **C. Result Set Caching - NOT SUITABLE** - Result set caching requires exact query matches to use cached results. - Since reports will have different WHERE clauses, the cached result from the base query won't be reusable. - Only beneficial for identical repetitive queries, not for queries with varying filters. **D. Replicated Table - NOT APPROPRIATE** - Replicated tables are designed for small dimension tables that are frequently joined with large fact tables. - Doesn't help with complex data transformations or precomputing query results. - Inappropriate for storing the output of complex SELECT queries with transformations. ### Why Materialized View is the Best Solution: 1. **Precomputation**: The complex JOINs and CASE statements are executed once during materialized view refresh, then the results are stored. 2. **Daily Refresh Alignment**: Since reports are produced once daily, the materialized view can be refreshed on the same schedule. 3. **Query Performance**: Reports with different WHERE clauses can query the materialized view directly, avoiding expensive JOIN operations and transformations. 4. **Synapse Optimization**: Materialized views in dedicated SQL pools are specifically designed for this type of analytical workload optimization. This approach provides the best balance of performance optimization and flexibility for the reporting requirements.