🛢️ 19 SQL Anti-Patterns That Are Slowing Down Your Database

SQL performance issues often come from common anti-patterns. Knowing these mistakes helps you write faster, cleaner, and maintainable queries.

1. 🚫 SELECT * (Lazy Column Fetching)

-- BAD
SELECT * FROM users;

-- GOOD
SELECT id, username FROM users;

✅ Always select only the columns you need.

2. 📦 Storing Comma-Separated Values

-- BAD: hobbies column "reading,swimming,gaming"
SELECT * FROM users WHERE hobbies LIKE '%swimming%';

-- GOOD: normalized table
CREATE TABLE user_hobbies (
    user_id INT,
    hobby VARCHAR(50)
);

✅ Normalize data for better performance and maintainability.

3. 🔍 LIKE with Leading Wildcards

-- BAD
SELECT * FROM products WHERE name LIKE '%phone';

-- GOOD
SELECT * FROM products WHERE name LIKE 'phone%';

✅ Avoid leading wildcards; consider full-text search if needed.

4. 🧱 Ignoring Indexes

CREATE INDEX idx_user_id ON orders(user_id);

✅ Index columns used in WHERE, JOIN, and ORDER BY clauses.

5. 🔄 Subqueries Instead of Joins

-- BAD
SELECT * FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE name='Sales');

-- GOOD
SELECT e.* FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name = 'Sales';

✅ Prefer joins for efficiency and readability.

6. 🧮 COUNT(*) on Large Tables

-- BAD
SELECT COUNT(*) FROM large_table;

-- GOOD
SELECT COUNT(id) FROM large_table;

✅ Count indexed columns instead of * on big tables.

7. 🧠 Using NULL for Missing Data

-- BAD
SELECT * FROM users WHERE age IS NULL;

-- GOOD
SELECT * FROM users WHERE age = 0; -- or use 'is_unknown' flag

✅ Use default values or flags when possible.

8. 🔁 Repeated Queries

-- BAD
SELECT * FROM users WHERE status='active';
SELECT * FROM users WHERE status='inactive';

-- GOOD
SELECT * FROM users WHERE status IN ('active','inactive');

✅ Consolidate queries for cleaner, faster code.

9. 🧩 Not Using Constraints

-- GOOD
ALTER TABLE orders ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);

✅ Use primary, foreign keys and unique constraints for integrity.

10. 🧪 Inefficient Transactions

-- BAD
BEGIN TRANSACTION;
UPDATE users SET status='active';
UPDATE orders SET status='processed';
COMMIT;

-- GOOD
BEGIN TRANSACTION;
UPDATE users SET status='active';
COMMIT;

BEGIN TRANSACTION;
UPDATE orders SET status='processed';
COMMIT;

✅ Keep transactions short and commit promptly.

11. 🛑 Overusing DISTINCT

-- BAD
SELECT DISTINCT username FROM users;

-- GOOD
SELECT username FROM users
GROUP BY username;

✅ Avoid DISTINCT when possible; fix duplicates at the data level.

12. ⚖️ JOIN Order & Indexes

-- GOOD
CREATE INDEX idx_customer_id ON orders(customer_id);

✅ Index join columns; analyze execution plans.

13. 🕳 Functions on Indexed Columns

-- BAD
SELECT * FROM users WHERE UPPER(username) = 'ADMIN';

-- GOOD
SELECT * FROM users WHERE username = 'ADMIN';

✅ Avoid functions in WHERE clauses on indexed columns.

14. 🔢 Inefficient Pagination

-- BAD
SELECT * FROM orders ORDER BY created_at LIMIT 50 OFFSET 100000;

-- GOOD
SELECT * FROM orders
WHERE created_at > '2025-09-01'
ORDER BY created_at
LIMIT 50;

✅ Use keyset pagination for large datasets.

15. 🏗 Batch Inserts/Updates

-- BAD
INSERT INTO users (username) VALUES ('user1');
INSERT INTO users (username) VALUES ('user2');

-- GOOD
INSERT INTO users (username) VALUES ('user1'), ('user2'), ('user3');

✅ Batching improves throughput and reduces disk I/O.

16. 🗑 Archive Old Data

-- GOOD PRACTICE
INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < '2023-01-01';
DELETE FROM orders WHERE created_at < '2023-01-01';

✅ Archive or partition old data to keep tables fast.

17. 🔒 Holding Locks Too Long

-- GOOD
BEGIN TRANSACTION;
UPDATE users SET status='active';
COMMIT;

✅ Short transactions prevent deadlocks and improve concurrency.

18. 🌐 Ignoring Execution Plans

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

✅ Use EXPLAIN or EXPLAIN ANALYZE to optimize queries and check index usage.

19. ⚠️ The N+1 Query Problem in JPA & Hibernate

One common and often overlooked anti‑pattern when using Spring Data JPA and Hibernate is the N+1 query problem, a performance issue that can dramatically increase database calls and slow down your application.

The “N+1” problem occurs when your ORM issues one query to load a set of parent entities, and then one additional query per parent to load related child entities — resulting in N+1 SQL statements for a single logical operation. 


// BAD: might lead to N+1
List users = userRepository.findAll();
for (User user : users) {
    System.out.println(user.getRoles().size());
}

With lazy‑loaded associations (the default in JPA), accessing a child collection like user.getRoles() triggers a separate query for each user. If you have 100 users, this results in ~101 queries instead of 1.

📦 How to Prevent It

  • JOIN FETCH – Fetch associated entities in the initial query: 
    
    @Query("SELECT u FROM User u LEFT JOIN FETCH u.roles")
    List findAllWithRoles();
  • @EntityGraph – Declaratively specify associations to fetch: 
    
    @EntityGraph(attributePaths = "roles")
    List findAll();
  • @BatchSize – Tell Hibernate to batch fetch lazy collections: 
    
    @Entity
    @BatchSize(size = 10)
    public class User { … }
  • @Fetch(FetchMode.SUBSELECT) – Fetch collections using a subselect strategy (Hibernate‑specific).

Monitoring SQL logs with spring.jpa.show‑sql and tools like p6spy helps you spot N+1 patterns early.

Fixing N+1 issues is crucial in real applications — especially those with large datasets or microservices where each extra database round‑trip increases latency and resource usage.

Conclusion

Avoid these 19 SQL anti-patterns to improve performance, reduce load, and simplify maintenance. Always focus on **indexes, normalization, and efficient query design**.

Labels: SQL, Database, Performance, Optimization, Best Practices, Queries

Comments

Post a Comment

Popular posts from this blog

🛠️ The Code Hut - Index

🌐 Distributed Systems Series 📖 Distributed Systems with Java (Series Intro) 📖 Intro to Distributed Systems ☁️ Cloud-Native Considerations ⚠️ Distributed System Anti-Patterns 🧪 Testing Strategies for Distributed Systems 🛡️ Advanced Fault Tolerance 🔗 Distributed Transactions Deep Dive 📈 Microservices Scaling Patterns 🔔 Monitoring & Alerting 📡 Event-Driven Architecture 🔐 Security in Distributed Systems 👀 Observability ⚡ Caching Strategies 📊 Consistency Models 🔍 Service Discovery & Load Balancing 💪 Resilience Patterns 💬 Communication Patterns 🧱 Fault Tolerance & Reliability 🔄 Transactions & Sagas 🤝 Consensus & Coordination 🔒 Concurrency & Locking 🏗️ Microservices & Architecture 🧩 Key Microservices Design Patterns 📐 Applying SOLID Principles 🌐 RESTful API Fundamentals — Designing Clean, Resource-Oriented Endpoints ⚡ REST vs gRPC in Java: Choosing the Right Com...
Read more

🛡️ Thread-Safe Programming in Java: Locks, Atomic Variables & LongAdder

Writing thread-safe code in Java requires understanding locks, atomic variables, and concurrent utilities. In this post, we'll explore ReentrantLock, synchronized blocks, AtomicLong, LongAdder , and when to use each. 1. 🔑 Synchronized Blocks The simplest way to make a section of code thread-safe is the synchronized keyword. It locks on an object monitor. public class Counter { private int count = 0; public synchronized void increment() { count++; } public synchronized int getCount() { return count; } } ✅ Easy to use for simple critical sections ❌ Locks the entire object, can reduce concurrency 2. 🔄 ReentrantLock More flexible than synchronized blocks. Supports tryLock , timed locks, and interruptible locks. import java.util.concurrent.locks.ReentrantLock; ReentrantLock lock = new ReentrantLock(); lock.lock(); try { // critical section } finally { lock.unlock(); } ✅ Better for advanced locking strategies, fairnes...
Read more

🛡️ Resilience Patterns in Distributed Systems

Welcome back to The Code Hut Distributed Systems series! In this post, we’ll explore patterns that help distributed systems remain resilient under failure conditions. 💪⚡ 🌐 Why Resilience Patterns Matter In distributed systems, failures are inevitable. Resilience patterns help: ❌ Prevent cascading failures ⚡ Maintain availability 😊 Improve user experience during partial outages 1. 🛑 Circuit Breaker A circuit breaker stops requests to a failing service to prevent overwhelming it and allows the system to recover gracefully. Circuit breaker states: Closed: Requests flow normally. Open: Requests fail immediately; fallback triggers. Half-Open: A few trial requests check if the service has recovered. Example with Resilience4j: // Using Resilience4j circuit breaker CircuitBreaker circuitBreaker = CircuitBreaker.ofDefaults("service"); Supplier decorated = CircuitBreaker .decorateSupplier(circuitBreaker, () -> remoteService.cal...
Read more