When choosing between filtering on a dimension table versus a fact table, which yields faster performance?

• A. Filtering on a dimension table
• B. Filtering on a fact table
• C. Filtering on a calculated column
• D. Filtering on a measure

Answer: (A)

Pruning data early by filtering on dimension tables tends to be faster because dimension tables are smaller and offer selective predicates that quickly narrow down the relevant rows. When you filter on a dimension, the engine can identify the subset of dimension rows that meet the predicate and then use that reduced set to join to the large fact table, cutting down the number of fact rows that must be read and processed. This early reduction often enables the use of indexes and efficient joins, which speeds up the query.

Filtering on the large fact table would typically require scanning many more rows unless there are highly selective indexes or partitions on the filter column. Filtering on a calculated column adds extra per-row computation, which can prevent index usage and slow down the plan. Filtering on a measure is usually applied after aggregation, so it doesn’t prune the rowset efficiently.