SQL Performance and Anti Patterns Questions
Recognition and remediation of common SQL performance anti patterns and pitfalls, such as accidental cartesian joins, N plus one query patterns, inefficient correlated subqueries, using functions in WHERE clauses that prevent index use, SELECT star usage, lack of appropriate indexes, large unbounded sorts or aggregations, and poor join ordering. Covers methods to diagnose problems using execution plans, explain analyze, and rewriting queries for better performance and scalability.
EasyTechnical
0 practiced
Describe the N+1 query anti-pattern in the context of analyzing data or fetching data for dashboards. Provide a simple ORM-style pseudocode example (fetch list of orders then for each load customer) and show how you'd rewrite it in SQL to avoid N+1. How would you detect N+1 issues from query logs?
MediumTechnical
0 practiced
ORDER BY RANDOM() or ORDER BY RAND() is commonly used to get random rows but performs poorly on large tables. Explain why and propose alternative SQL techniques for random sampling at scale (TABLESAMPLE, reservoir sampling, random id range sampling), including pros/cons.
HardSystem Design
0 practiced
You ingest 1TB of new data daily and need to maintain aggregated KPIs via materialized views without full refresh every day. Propose an incremental maintenance architecture using delta tables or CDC (change data capture), outline how you would ensure correctness in the presence of late-arriving data, and discuss recovery strategies for failed refreshes.
HardTechnical
0 practiced
A production stored procedure compiled with an uncommon parameter generates an inefficient plan for typical values (parameter sniffing). Propose a robust mitigation strategy for SQL Server and for Postgres environments, mentioning dynamic SQL, recompilation hints, plan guides, and the trade-offs involved (compilation cost vs plan generality).
HardTechnical
0 practiced
A highly-skewed column is causing bad planner choices. Explain how you would use histogram statistics, extended multivariate statistics, and statistics target tuning to improve cardinality estimates. Provide step-by-step commands or actions (e.g., ANALYZE, ALTER TABLE SET STATISTICS, CREATE STATISTICS) and how you'd validate improvements.
Unlock Full Question Bank
Get access to hundreds of SQL Performance and Anti Patterns interview questions and detailed answers.
Sign in to ContinueJoin thousands of developers preparing for their dream job.