sql

Query optimization involves analyzing execution plans, rewriting inefficient logic, and adding strategic indexes. Start by examining your query's plan to identify bottlenecks, then apply techniques like join reordering, filtering earlier in the query, and materializing intermediate results to improve speed on large datasets.

Analytics indexing differs from transactional indexing. Create composite indexes on frequently filtered columns, use covering indexes to avoid table lookups, and consider columnstore indexes for aggregation-heavy queries. Match index design to your query patterns rather than indexing everything.

Denormalization trades update complexity for query speed in analytics. Denormalize when read volume far exceeds writes, queries span many joins, or aggregations repeat across dashboards. Keep normalized dimensions separate and denormalize fact tables strategically to balance maintainability with performance.

Slow dashboards usually stem from missing indexes, inefficient joins, or scanning unnecessary data. Enable query execution plans, check index usage statistics, and profile wall-clock time per query section. Prioritize fixing full table scans and unindexed joins first.

Yes. ETL pipelines benefit from the same optimization principles: indexed staging tables, batch processing with efficient joins, and denormalized intermediate results. Structure transformations to minimize data movement and leverage database-side aggregation before extracting.

Schema design determines performance ceiling. Partition large tables by date or region, choose appropriate data types to reduce storage and I/O, and design grain carefully—fact tables should atomize at your finest measurement level. Poor grain forces expensive aggregations downstream.