10 SQL Anti-Patterns You Must Avoid in Production

[Lomanu4]

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.

10 SQL Anti-Patterns You Must Avoid in Production

“Slow SQL isn’t always about bad servers — it’s often about bad habits.”

As SQL developers and data engineers, we often prioritize functionality and overlook query quality. But poor SQL habits can lead to:

• Long response times
• Bottlenecked applications
• Excessive I/O and CPU
• Poor scalability

In this post, we’ll break down 10 critical SQL anti-patterns and how to fix them.

1. N+1 Query Pattern


Problem: Querying in a loop for each record.


-- BAD: Fetching orders per customer inside loop
SELECT * FROM Customers;
-- For each customer:
SELECT * FROM Orders WHERE customer_id = ?;

Fix: Join and aggregate in one query


SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM Customers c
LEFT JOIN Orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;
2. Wildcard Index Scans


Problem: Leading wildcard disables index usage.


SELECT * FROM Products WHERE name LIKE '%phone';

Fix: Use full-text search or structured LIKE


-- Better (index usable)
SELECT * FROM Products WHERE name LIKE 'phone%';
3. Implicit Data Type Conversions


Problem: Filtering numeric column with a string.


SELECT * FROM Orders WHERE id = '123';

Fix: Match types explicitly


SELECT * FROM Orders WHERE id = 123;

? Use query plans to detect implicit conversion overhead.

4. Scalar Subqueries in SELECT


Problem: Executing subquery for every row.


SELECT id, (SELECT COUNT(*) FROM OrderItems WHERE order_id = o.id)
FROM Orders o;

Fix: Use join and aggregation


SELECT o.id, COUNT(oi.id) AS item_count
FROM Orders o
LEFT JOIN OrderItems oi ON oi.order_id = o.id
GROUP BY o.id;
5. SELECT * in Production


Problem: Fetches unnecessary columns, causes bloat.


SELECT * FROM Transactions;

Fix: Select only what you need


SELECT id, amount, transaction_date FROM Transactions;
6. Redundant DISTINCT


Problem: Using DISTINCT to patch bad joins.


SELECT DISTINCT name FROM Customers c JOIN Orders o ON o.customer_id = c.id;

Fix: Analyze join logic and duplicates instead.

7. Missing WHERE Clause in DELETE/UPDATE


-- Danger zone!
DELETE FROM Users;
UPDATE Orders SET status = 'Shipped';

Fix: Always qualify with a WHERE clause.


DELETE FROM Users WHERE is_deleted = true;
8. No Index on Foreign Keys


Problem: FK lookups scan entire referenced table.


-- Missing index on Orders.customer_id

Fix: Add supporting indexes


CREATE INDEX idx_orders_customer ON Orders(customer_id);
9. Overusing OR instead of UNION ALL


SELECT * FROM Orders WHERE status = 'pending' OR status = 'shipped';

Fix: Separate indexed paths with UNION ALL


SELECT * FROM Orders WHERE status = 'pending'
UNION ALL
SELECT * FROM Orders WHERE status = 'shipped';
10. Not Using ANALYZE or EXPLAIN


Problem: Blindly writing queries without measuring impact.
Fix: Always inspect query plans


EXPLAIN ANALYZE SELECT ...
Final Thoughts: Write It Once, Run It Well


Avoiding anti-patterns isn't just about style — it's about performance, reliability, and cost.

"SQL is declarative. Let the engine help — but don’t tie its hands with bad habits."

#SQL #Performance #AntiPatterns #QueryOptimization #BestPractices #AdvancedSQL #DataEngineering

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.