Query performance practices Which practice most reliably improves a query's processing time and execution efficiency?
-
AWrite overly complex SQL with many nested expressions to reduce lines of code.
-
BSelf-join a table even when a simpler rewrite exists.
-
CNest one query inside another without indexes or selectivity rationale.
-
DUse compatible and proper data types to enable sargable predicates and good plans.
-
EDisable statistics to avoid optimizer overhead.
Answer
Correct Answer: Use compatible and proper data types to enable sargable predicates and good plans.
Explanation
Introduction / Context:SQL performance depends on the optimizer’s ability to use indexes, statistics, and selective predicates. Data-type mismatches and non-sargable expressions often prevent index seeks and force scans. This question tests a reliable, general practice for better performance.
Given Data / Assumptions:
- The schema provides appropriate indexes and fresh statistics.
- Queries can be written in multiple ways.
- We aim for portable, optimizer-friendly SQL.
Concept / Approach:
Using compatible types (e.g., comparing INTEGER to INTEGER, DATE to DATE) avoids implicit conversions that block index usage. Writing sargable predicates (Search ARGument ABLE), such as column >= ? and column BETWEEN a AND b, lets the optimizer find efficient plans. Type correctness also prevents needless casts and function-wrapped columns.
Step-by-Step Solution:
1) Verify column and parameter types match to avoid implicit casts.2) Use predicates the engine can push to indexes.3) Remove unnecessary nesting and self-joins where simpler joins or CTEs suffice.4) Check statistics so the optimizer estimates cardinalities accurately.Verification / Alternative check:
RDBMS tuning guides consistently highlight data-type compatibility and sargability as universal improvements across engines (PostgreSQL, Oracle, SQL Server, MySQL).
Why Other Options Are Wrong:
- Complexity for its own sake usually harms readability and performance.
- Unnecessary self-joins and nesting add overhead without benefit.
- Disabling statistics cripples the optimizer, degrading performance.
Common Pitfalls:
- Wrapping indexed columns in functions (e.g., UPPER(col)) making predicates non-sargable.
- Comparing text to numbers or dates to strings, causing scans.
Final Answer:
Use compatible and proper data types to enable sargable predicates and good plans.