BI Performance Optimization Cheat Sheet

Updated 2026-05-26

Next Topic: BI Semantic Layer and Headless BI Cheat Sheet

🧠Study flashcards on this topic114 cards · spaced repetition→

Business Intelligence (BI) performance optimization encompasses the systematic improvement of data retrieval, processing, and presentation across the entire analytics stack—from source databases to end-user dashboards. In modern BI environments, query response times, data refresh rates, and dashboard load times directly impact decision-making velocity and user adoption. Effective BI performance optimization requires understanding the complete data flow: how data is extracted, transformed, stored, indexed, queried, and visualized. The most impactful optimization occurs at the earliest stages—poor data modeling or missing indexes at the source can nullify downstream optimizations, making architectural decisions at the data warehouse layer more consequential than frontend tuning.

What This Cheat Sheet Covers

This topic spans 20 focused tables and 120 indexed concepts, 114 flashcards. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.

Table 1: Query Optimization FundamentalsTable 2: Indexing StrategiesTable 3: Data Modeling for PerformanceTable 4: Partitioning SchemesTable 5: Caching StrategiesTable 6: Compression TechniquesTable 7: ETL and Data Pipeline OptimizationTable 8: Execution Plan AnalysisTable 9: Statistics MaintenanceTable 10: Connection and Resource ManagementTable 11: Join OptimizationTable 12: Subquery and Query StructureTable 13: BI Tool-Specific OptimizationsTable 14: Advanced Optimization TechniquesTable 15: Database Maintenance OperationsTable 16: Monitoring and DiagnosticsTable 17: Power BI and Tableau SpecificTable 18: OLAP and MDX OptimizationTable 19: Lakehouse and Open Table Format OptimizationTable 20: Cloud Data Warehouse Platform-Specific Optimizations

Quick IndexSubscribe to unlock

A jump-to index of every table row in this cheat sheet.

Mind MapSubscribe to unlock

An interactive map of every table and concept in this topic.

Table 1: Query Optimization Fundamentals

Writing queries that return the right data while scanning as little of it as possible is the highest-leverage optimization in any BI stack; the techniques here apply universally across SQL databases and columnar warehouses alike.

Technique	Example	Description
WHERE clause filtering	`SELECT * FROM sales` `WHERE order_date >= '2026-01-01'`	Reduces rows processed by applying filters before joins and aggregations — the most fundamental optimization technique
SELECT only needed columns	`SELECT customer_id, total` `FROM orders`	Reduces data transferred and memory usage by avoiding `SELECT ` — critical for columnar storage* where column pruning dramatically reduces I/O
LIMIT result sets	`SELECT * FROM transactions` `ORDER BY date DESC` `LIMIT 1000`	Restricts rows returned to prevent memory overflow and network bottlenecks — especially important for pagination and dashboard previews
Avoid SELECT DISTINCT when possible	`GROUP BY customer_id` instead of `SELECT DISTINCT customer_id`	GROUP BY often performs better than DISTINCT as it allows index-based operations rather than full result set deduplication
Snowflake micro-partition pruning	Filter on cluster key column: `WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31'`	Snowflake stores metadata min/max per micro-partition — filtering on the cluster key column eliminates entire micro-partitions from the scan, typically reducing I/O by 90%+
Use query hints judiciously	`SELECT * FROM orders WITH (INDEX(idx_date))`	Forces optimizer to use specific execution paths when statistics are misleading — use sparingly as hints bypass cost-based optimization

Table 2: Indexing Strategies

Indexes are the single biggest lever for reducing query scan cost; choosing the right index type and column order directly determines whether a query performs a fast index seek or a slow full-table scan.

Technique	Example	Description
Clustered index on primary key	`CREATE CLUSTERED INDEX` `ON orders(order_id)`	Physically orders table rows by key value — only one per table, provides fastest lookup for primary key queries

The rest of this section is part of the full cheat sheet

Table 3: Data Modeling for Performance

Schema design decisions at the modeling stage compound through every downstream query; star schemas, pre-aggregations, and surrogate keys eliminate the join overhead that no amount of tuning can fully recover.

Technique	Example	Description
Star schema denormalization	Central fact table with surrounding dimension tables joined on keys	Minimizes joins by storing dimensions separately — industry standard for analytic databases optimizing read performance

The rest of this section is part of the full cheat sheet

Table 4: Partitioning Schemes

Partitioning physically divides large tables into prunable segments so queries scan only the slices they need — the most effective I/O reduction strategy for time-series and event-based BI workloads.

Technique	Example	Description
Range partitioning by date	`PARTITION BY RANGE (order_date)` `(P2025 VALUES LESS THAN ('2026-01-01'),` `P2026 VALUES LESS THAN ('2027-01-01'))`	Divides table into date ranges — enables partition pruning where queries scan only relevant partitions reducing I/O

The rest of this section is part of the full cheat sheet

Table 1: Query Optimization Fundamentals

Technique	Example	Description
WHERE clause filtering	`SELECT * FROM sales` `WHERE order_date >= '2026-01-01'`	Reduces rows processed by applying filters before joins and aggregations — the most fundamental optimization technique
SELECT only needed columns	`SELECT customer_id, total` `FROM orders`	Reduces data transferred and memory usage by avoiding `SELECT ` — critical for columnar storage* where column pruning dramatically reduces I/O
LIMIT result sets	`SELECT * FROM transactions` `ORDER BY date DESC` `LIMIT 1000`	Restricts rows returned to prevent memory overflow and network bottlenecks — especially important for pagination and dashboard previews
Avoid SELECT DISTINCT when possible	`GROUP BY customer_id` instead of `SELECT DISTINCT customer_id`	GROUP BY often performs better than DISTINCT as it allows index-based operations rather than full result set deduplication
Snowflake micro-partition pruning	Filter on cluster key column: `WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31'`	Snowflake stores metadata min/max per micro-partition — filtering on the cluster key column eliminates entire micro-partitions from the scan, typically reducing I/O by 90%+
Use query hints judiciously	`SELECT * FROM orders WITH (INDEX(idx_date))`	Forces optimizer to use specific execution paths when statistics are misleading — use sparingly as hints bypass cost-based optimization

Table 2: Indexing Strategies

Technique	Example	Description
Clustered index on primary key	`CREATE CLUSTERED INDEX` `ON orders(order_id)`	Physically orders table rows by key value — only one per table, provides fastest lookup for primary key queries

Table 3: Data Modeling for Performance

Technique	Example	Description
Star schema denormalization	Central fact table with surrounding dimension tables joined on keys	Minimizes joins by storing dimensions separately — industry standard for analytic databases optimizing read performance

Table 4: Partitioning Schemes

Technique	Example	Description
Range partitioning by date	`PARTITION BY RANGE (order_date)` `(P2025 VALUES LESS THAN ('2026-01-01'),` `P2026 VALUES LESS THAN ('2027-01-01'))`	Divides table into date ranges — enables partition pruning where queries scan only relevant partitions reducing I/O