Reza Bashiri’s Post

Day 25: Execution Plans — Reading the Database’s Mind 🧠 "Your SQL query is just a suggestion. The database decides how to actually do the work." When you hit 'Execute,' the database doesn't just start running. It hands your code to a Query Optimizer, which looks at your indexes, table sizes, and join types to create a "map" of the fastest way to get your results. This map is called an Execution Plan. If your query is slow, the Execution Plan is where the "crime scene" evidence is hidden. Think of an Execution Plan like a GPS Navigation App 🗺️: The Destination: Your SELECT statement (what you want). The Possible Routes: The database could use an Index (The Highway) or a Full Table Scan (The Side Streets). The Final Plan: The database chooses the route with the "lowest cost" (least amount of CPU and memory usage). Why you should care about "Cost": In an execution plan, every step has a "Cost %." If you see one step taking up 90% of the effort, you’ve found your bottleneck. Usually, it's a missing index or a "Nested Loop" that is spiraling out of control. SQL: -- For PostgreSQL or MySQL EXPLAIN ANALYZE SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_date > '2024-01-01'; #30DaysOfSQL #LearningInPublic #DataChallenge #DataAnalysis #CareerDevelopment #DataCommunity #innovation #technology #creativity #Future #futurism. #DataAnalytics #DataScience #DataEngineering #BusinessIntelligence

The Advanced SQL Course - A Production Support Perspective 📊🔍 Recently completed The Advanced SQL Course, and it reinforced how critical strong SQL skills are - especially in production support environments. In real-world systems, data issues are often at the heart of incidents, and advanced SQL knowledge can make a huge difference in response time and accuracy. Here’s how it connects to production support: 1. Faster incident resolution by writing efficient queries to trace data issues 2. Ability to analyze large datasets and identify anomalies quickly 3. Improved performance tuning to handle slow queries in live systems 4. Better understanding of joins, indexing, and query optimization for critical fixes At the same time, it highlights a few realities: 1. A small query mistake in production can have big consequences 2. Understanding the data model is just as important as writing queries 3. Precision and validation are key when working with live data Strong SQL skills aren’t just for developers — they’re essential for anyone involved in keeping systems stable and reliable. Grateful for the deeper insights, and looking forward to applying them in real-world scenarios. #SQL #ProductionSupport #DataAnalysis #DatabaseManagement #PerformanceTuning #ContinuousLearning

One of the most underrated skills in working with databases is writing clean and efficient queries. It’s not just about getting the correct result… It’s about how you get it. I’ve seen cases where a query works perfectly, but causes performance issues because it’s not optimized. Small improvements like: Avoiding unnecessary joins Using proper indexes Filtering data early Can make a huge difference. 👉 A good query gives results. 👉 A great query gives results efficiently. #SQL #Database #Performance #Backend #Engineering

Day 4: Speaking SQL — Structure, Constraints, and the Query Lifecycle Today marked a significant shift as I transitioned from understanding the "Why" of SQL to exploring the "How." I focused on the commands that build and manipulate data, revealing the intricacies of SQL beyond just SELECT *. Key Takeaways from Today: - The 5 SQL Categories: SQL encompasses more than one language. It includes DDL (Data Definition Language) for building structures, DML (Data Manipulation Language) for moving data, DQL (Data Query Language) for retrieving information, and DCL/TCL (Data Control Language/Transaction Control Language) for security and safety. - Constraints are King: I revisited the importance of PRIMARY KEYS, FOREIGN KEYS, and CHECK constraints, which serve as the strict enforcers of data integrity, preventing "dirty data" from entering the system. - NULL is not 0: A critical distinction in SQL. NULL represents an unknown or absence of value, while 0 is a numeric value, and '' is an empty string. Understanding these differences is essential for maintaining data integrity. - The Query Lifecycle: I explored what happens when executing a query. The database undergoes three stages: Parse (Can I read this?), Plan (What's the fastest way?), and Execute (Carrying out the command). Additionally, I delved into Indexing—the "Table of Contents" that enables efficient data retrieval without scanning every row. #SQL #Day4 #Database #Backend #SoftwareEngineering #DataIntegrity #SystemDesign #FullStack I’ve summarized these core foundations in my Day 4 Medium post:

I used to think indexes were the 𝗲𝗮𝘀𝗶𝗲𝘀𝘁 way to 𝗳𝗶𝘅 𝘀𝗹𝗼𝘄 𝗾𝘂𝗲𝗿𝗶𝗲𝘀. Query slow? 👉 Add an index. Another query slow? 👉 Add another index. For a while, it actually works. ⚡ Queries become faster. 📊 Dashboards load quickly. Everyone is happy. But something interesting starts happening later. 🐢 Writes begin to slow down. INSERT, UPDATE, and DELETE operations take longer than expected. And the reason is simple: Every time data changes, the database must also update every related index. So if a table has too many indexes, each write operation becomes heavier. ⚖️ That’s the 𝘁𝗿𝗮𝗱𝗲-𝗼𝗳𝗳 many developers discover a bit late. Indexes are powerful, but creating them blindly can introduce new problems. Some common side effects: ✅ 𝗣𝗿𝗼𝘀 of adding indexes 🔎 Faster search and filtering (WHERE) 🔗 Faster joins between tables 📈 Better performance for sorting and grouping 🗂️ Large datasets become manageable ⚠️ 𝗖𝗼𝗻𝘀 of adding indexes 𝗯𝗹𝗶𝗻𝗱𝗹𝘆 🐌 Slower inserts, updates, and deletes 💾 Extra disk space for each index ⚙️ More work for the database to maintain them ❓ Some indexes may never even get used That’s why indexing is less about adding more, and 𝘮𝘰𝘳𝘦 𝘢𝘣𝘰𝘶𝘵 𝘢𝘥𝘥𝘪𝘯𝘨 𝘵𝘩𝘦 𝘳𝘪𝘨𝘩𝘵 𝘰𝘯𝘦𝘴. 𝘼 𝙜𝙤𝙤𝙙 𝙞𝙣𝙙𝙚𝙭 𝙪𝙨𝙪𝙖𝙡𝙡𝙮 𝙘𝙤𝙢𝙚𝙨 𝙛𝙧𝙤𝙢 𝙪𝙣𝙙𝙚𝙧𝙨𝙩𝙖𝙣𝙙𝙞𝙣𝙜 𝙝𝙤𝙬 𝙩𝙝𝙚 𝙙𝙖𝙩𝙖 𝙞𝙨 𝙖𝙘𝙩𝙪𝙖𝙡𝙡𝙮 𝙦𝙪𝙚𝙧𝙞𝙚𝙙. 🧠 Databases reward thoughtful design. Blind optimization rarely stays optimal for long. #realMoneyLearnings #Databases #MySQL #SQL #DatabasePerformance #BackendEngineering #SoftwareEngineering #SystemDesign #PerformanceOptimization #DatabaseIndexes #LearningInPublic

One SQL Transaction Can Save Your Data… or Destroy It ⚠ SQL transactions look simple… until you face real scenarios 😅 While working with SQL Server, I came across some situations that completely changed how I write queries: 💡 BEGIN → UPDATE → ROLLBACK ✔ Changes are safely undone 💡 UPDATE without BEGIN ❌ Auto-committed → No rollback possible 💡 BEGIN → UPDATE → COMMIT → ROLLBACK ❌ Once committed, rollback won’t work 💡 BEGIN → UPDATE → BEGIN again ⚠ Previous transaction gets committed automatically 💡 ROLLBACK ✔ Only affects changes after BEGIN ❌ Old data remains unchanged 📌 Key Learning: Transactions are not just commands — they are your safety net. One wrong query without control = permanent data loss ⚠ Now I always follow: 👉 BEGIN → VERIFY → EXECUTE → VERIFY → COMMIT / ROLLBACK Small habit… big impact 🚀 #SQL #SQLServer #Database #BackendDevelopment #TechLearning #Developers #CodingLife

Query Optimization Mistakes (Final Synthesis) Most database performance problems are self-inflicted. Not because databases are slow. But because queries are poorly designed. After working with production systems, the same mistakes appear repeatedly. ❌ Fetching more data than needed SELECT * everywhere ❌ Missing or wrong indexes ❌ Ignoring execution plans ❌ N+1 query patterns ❌ Using OFFSET pagination at scale ❌ Long-running transactions Individually, each mistake seems small. Combined, they destroy performance. Real scenario. An API with: • inefficient queries • no indexing strategy • excessive joins Works fine in development. Fails under production load. Here’s the truth: Databases don’t get slower. Workloads get heavier. Optimization is not about tricks. It’s about: • reducing I/O • minimizing round trips • understanding execution plans The biggest shift happens when you stop asking: “Why is this query slow?” And start asking: “What unnecessary work is happening?” That’s where real performance gains come from. #Databases #SQL #Performance #BackendEngineering #SystemDesign

Most developers blame slow queries on missing indexes. The real culprit is usually hidden inside the execution plan. After years of tuning SQL Server workloads, I have learned that reading execution plans is the single highest-leverage skill a data engineer can develop. It tells you exactly where SQL Server is spending its time — no guessing required. Here is what I look for first when a query is underperforming: 1. Thick arrows between operators — wide data flows signal excessive row estimates and memory pressure 2. Key Lookups — these often mean a nonclustered index is missing one or two covering columns 3. Hash Matches on large tables — usually a sign of outdated statistics or a missing join index 4. Parallelism warnings — CXPACKET waits visible in the plan indicate skewed data distribution or MAXDOP misconfiguration 5. Estimated vs actual row counts — a significant gap almost always points to stale statistics or parameter sniffing Once you identify the bottleneck operator, the fix is usually surgical. Update statistics, add a covering index, rewrite the predicate, or force a plan hint where justified. You rarely need to rewrite the entire query. Execution plan analysis is not reserved for DBAs. Every engineer who writes T-SQL should be comfortable opening an actual execution plan before escalating a performance issue. Build that habit early and you will resolve most slow query tickets in under thirty minutes. #SQLServer #QueryOptimization #DataEngineering #PerformanceTuning #DatabaseAdministration

Database normalization can seem complicated at first, but it follows a simple goal: organize data so it is accurate, efficient, and easy to maintain. This visual shows how a poorly designed table evolves step by step: 🔹 1NF (First Normal Form) Remove repeating groups and ensure each column contains only one value. 🔹 2NF (Second Normal Form) Eliminate partial dependencies so every non-key column depends on the entire primary key. 🔹 3NF (Third Normal Form) Remove transitive dependencies so each non-key column depends only on the key. The result? A cleaner database design with less redundancy, better consistency, and easier maintenance. Good database design isn't just about storing data—it's about storing it the right way. #Database #SQL #DatabaseDesign #Normalization #FirstNormalForm #SecondNormalForm #ThirdNormalForm #DataModeling #SQLServer #SoftwareEngineering #BackendDevelopment #LearningJourney

When NOT to Normalize Your Database Normalization is good. Until it isn’t. Database normalization reduces redundancy. It keeps data clean. It enforces consistency. That’s why it’s taught as best practice. But at scale, normalization can hurt performance. Highly normalized schemas require: • multiple joins • more queries • more I/O Each join adds cost. Real scenario. An analytics system joins 6 tables for every request. Each query becomes expensive. Latency increases. Throughput drops. Denormalization solves this: • duplicate data intentionally • reduce joins • improve read performance But now you introduce: • data duplication • update complexity • consistency challenges Normalization favors correctness. Denormalization favors performance. The mistake is treating normalization as a rule. It’s not. It’s a starting point. Good engineers normalize first. Then denormalize strategically based on real performance needs. Database design is not theory. It’s trade-offs under load. #Databases #SQL #Performance #BackendEngineering #SystemDesign