Query Optimization
11. Why avoid SELECT *?
Answer: It retrieves unnecessary columns, increasing I/O, network traffic, memory usage, and potentially preventing covering-index benefits.
12. Why should filters be applied as early as practical?
Answer: Reducing rows early can decrease the amount of data processed by joins, aggregations, and sorts.
13. Why can JOIN cause performance problems?
Common reasons:
- Missing indexes
- Large intermediate result sets
- Incorrect join conditions
- Many-to-many joins
- Joining before filtering
14. What is a Cartesian Product?
When every row from one table is matched with every row from another table.
SELECT * FROM A CROSS JOIN B;
If A has 10,000 rows and B has 5,000:
50 million combinations.
15. How do you optimize a JOIN?
Check:
- Join predicates
- Indexes on join columns
- Data types
- Cardinality
- Filtering
- Execution plan
16. Why can functions in WHERE clauses hurt performance?
Example:
WHERE UPPER(Name) = 'ANITA'
The database may be unable to efficiently use an index on Name.
17. What is a SARGable query?
A query whose predicate allows the optimizer to efficiently use an index.
Example:
WHERE OrderDate >= '2026-01-01'
is generally more index-friendly than:
WHERE YEAR(OrderDate) = 2026
18. IN vs EXISTS — which is faster?
Answer: Neither is universally faster. The optimizer, data distribution, indexes, and query shape determine the result.
19. EXISTS vs COUNT(*) > 0?
For an existence check, EXISTS often communicates the intent better and may allow the engine to stop once a qualifying row is found.
WHERE EXISTS ( SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID )
20. Why can DISTINCT be expensive?
Answer: It may require sorting or hashing a large result set to eliminate duplicates.
