Md Toufiqul Islam’s Post

Your database is not slow. Your queries are. One of the most common things I hear is: “The database is slow.” But most of the time… it isn’t. A while ago, I had to analyze a query that was taking several minutes to run. At first glance, nothing “too wrong”. But digging deeper, the pattern was clear: * Looping through records * Nested subqueries executed per row * Repeated reads over the same tables Classic row-by-row processing. So instead of trying to “tune” the query… I rewrote the approach. From this mindset: FOREACH record RUN subquery To this: WITH AggregatedData AS ( SELECT EventId, SUM(Value) AS Total FROM Items GROUP BY EventId ) SELECT e.Id, a.Total FROM Events e LEFT JOIN AggregatedData a ON a.EventId = e.Id The result: * Query time dropped from minutes to milliseconds * Massive reduction in IO * Stable performance even with data growth That’s when it becomes very clear: * SQL is not about how to iterate It’s about how to describe the result Another common issue I still see: Developers relying on ORM-generated queries without ever checking what is actually executed. ORMs are great. But the database only understands SQL. The real shift happens when you start looking at: * Execution plans * Index usage * Logical reads Because that’s where performance actually lives. The database is rarely the problem. Data access patterns are. Curious to hear: Have you ever rewritten a query and seen a massive performance gain? #SQLServer #DatabasePerformance #BackendDevelopment #SoftwareEngineering #DotNet #SystemDesign #DataEngineering

🚨 Database Series #17 — Indexing Fundamentals Ever had a query that worked fine… until your table hit millions of rows? 😅 Suddenly: ❌ Slow queries ❌ High CPU usage ❌ Frustrating delays That’s when Indexing becomes critical. 🧠 Core Concept An index is a data structure that helps SQL Server find data faster. Think of it like: 📖 A book index Instead of scanning every page… you jump directly to what you need. ⚙️ Clustered vs Non-Clustered 🔵 Clustered Index Organizes the actual table data Only ONE per table Data is physically sorted Think of it as: 📚 “The book pages are ordered” 🟢 Non-Clustered Index Separate structure from table Stores pointers to data You can have MANY Think of it as: 📑 “A lookup guide pointing to pages” 💻 Code Example -- Clustered Index CREATE CLUSTERED INDEX IX_Employees_Id ON Employees(Id); -- Non-Clustered Index CREATE NONCLUSTERED INDEX IX_Employees_Name ON Employees(Name); 🌳 B-Tree Concept Indexes are built using a B-Tree structure. Visualize: Top Level 🔝 Root node ⬇ Middle Levels 📂 Branch nodes ⬇ Bottom Level 📄 Leaf nodes (actual data or pointers) This structure allows: ⚡ Fast searches ⚡ Efficient inserts ⚡ Scalable performance 📊 Visual Diagram (Concept) Without Index ❌ 📋 Full table scan → row by row With Index ✅ 🌳 Navigate tree → jump directly to data ⚠️ Common Mistake “Index everything” ❌ Too many indexes cause: 🐢 Slower INSERT / UPDATE / DELETE 💾 Increased storage usage 🔧 Maintenance overhead 🚫 When NOT to Index Avoid indexing: ❌ Small tables (scan is faster) ❌ Columns rarely used in queries ❌ Columns with low uniqueness (e.g. Gender) ❌ Frequently updated columns 🎯 Practical Takeaway Use indexes when: ✅ Searching large datasets ✅ Filtering (WHERE) ✅ Joining tables Balance is key: ⚖️ Read performance vs Write performance 💬 Question for developers What’s your biggest indexing mistake so far? Over-indexing… or missing the right index? 👀 ➡️ Next in the series: 🔍 Execution Plans — understanding how SQL Server actually runs your queries?

🔍 SQL Architecture – What Happens Behind Every Query? Ever wondered what actually happens when you run a simple SQL query? It’s not just about fetching data — there’s a powerful architecture working behind the scenes 👇 🧠 **Step-by-step flow:** ➡️ Client sends SQL query (App / API / User) ➡️ Query Processor validates & optimizes it ➡️ Execution Engine runs the best plan ➡️ Storage Engine retrieves data efficiently ➡️ Results are returned to the user ⚙️ **Key Components:** • Parser – Checks syntax & validity • Optimizer – Chooses best execution plan • Execution Engine – Runs the query • Storage Engine – Handles indexing & caching • Transaction Layer – Ensures ACID properties • Security Layer – Manages access & control 💡 **Why this matters?** Understanding SQL architecture helps you: ✅ Write optimized queries ✅ Improve performance ✅ Debug slow queries ✅ Design scalable backend systems 📌 Behind every `SELECT *` is a smart system making decisions in milliseconds! #SQL #Database #SystemDesign #BackendDevelopment #TechLearning #SoftwareEngineering

𝐃𝐞𝐜𝐫𝐲𝐩𝐭𝐢𝐧𝐠 𝐒𝐐𝐋 𝐞𝐱𝐞𝐜𝐮𝐭𝐢𝐨𝐧: 𝐇𝐨𝐰 𝐭𝐡𝐞 𝐝𝐚𝐭𝐚𝐛𝐚𝐬𝐞 𝐀𝐂𝐓𝐔𝐀𝐋𝐋𝐘 𝐰𝐨𝐫𝐤𝐬. We all write SQL queries in this "Coding Order": SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... TOP ... (or LIMIT) It feels intuitive, right? We start with what we want (SELECT), then where to get it, and how to filter it. But here's the thing: This is NOT how the SQL database engine executes it. If you want to write optimized queries, you must understand the "Execution Order." It’s fundamentally different. The database engine's logical process is: 1. FROM - First, it needs the data source. 2. WHERE - Then, it filters the base rows (before any grouping). 3. GROUP BY - It groups the remaining rows. 4. HAVING - It filters those groups (not individual rows). 5. SELECT - Finally, it calculates the specific expressions (and aggregates like SUM). 6. ORDER BY - Then, it sorts the result set. 7. TOP / LIMIT - And last, it truncates the final, sorted result. Knowing this order is a game-changer. It explains why you can't use a column alias defined in the SELECT clause within your WHERE clause the WHERE is processed before the SELECT even knows about the alias. Check out this visualization I created that maps the Coding Order (how we write it) to the Execution Order (how the DB processes it), step-by-step. Understanding this will help you: 💡 Write logically sound queries. 💡 Debug performance issues. 💡 Stop making common SQL mistakes . Do you write your queries based on the execution order, or do you still think in coding order? Let me know in the comments! #SQL #Database #Performance #DataScience #Coding #CareerGrowth #Learning #SQLQuery #DataAnalysis

You've been writing SQL queries wrong this whole time. Not the logic. The performance. Here's what most data engineers don't realise until it's too late 👇 A poorly written query on a 10M row table can take 40 seconds. The same query, rewritten properly? 0.3 seconds. That's not an exaggeration. That's production data I've seen with my own eyes. Here's where most of the waste hides: → SELECT * pulling 50 columns when you need 4 → No partition pruning scanning the whole table every single time → Correlated subqueries running once per row instead of once total → Missing indexes on join keys full table scans disguised as "fast queries" → Joining before filtering instead of filtering before joining The worst part? These queries run every hour on a schedule. Nobody notices. The BI dashboard just feels "a bit slow." And the cloud bill quietly grows. Query optimisation isn't a nice-to-have skill. It's how you save your company thousands of dollars a month without writing a single new feature. Start with EXPLAIN ANALYZE. It tells you exactly where the database is struggling. Read more on this: https://lnkd.in/ePTFGj3t 💬 What's the worst query performance issue you've ever inherited from someone else? #DataEngineering #SQL #QueryOptimisation #DataOps #CloudCosts

Slow queries are rarely a SQL problem. Most of the time it's an indexing decision made weeks ago or never made at all. Wrote a breakdown of everything that actually affects query performance: indexes, how the query planner thinks, why the cost-based optimizer sometimes gets it wrong, and what to do when it does. https://lnkd.in/deztdaXm

My query was taking 40 seconds to run. I added one index. It dropped to 0.3 seconds. Here's what I learned about SQL indexing: 1️⃣ Index the columns you filter by If you use a column in WHERE, JOIN, or ORDER BY — it's a candidate for an index. 2️⃣ Don't index everything Too many indexes slow down your INSERT and UPDATE operations. Be selective. Quality over quantity. 3️⃣ Composite indexes follow order An index on (country, city) helps queries filtering by country. It does NOT help queries filtering by city alone. 4️⃣ Use EXPLAIN to see what's happening Before adding an index, run EXPLAIN on your query. It shows exactly where the database is struggling. Indexing is one of the fastest wins in SQL performance. No rewriting. No refactoring. Just smarter structure.

Stop rewriting queries blindly. The execution plan is telling you exactly what's wrong. A common reaction to a slow query is to immediately start refactoring the SQL or adding indexes based on a hunch. This is like trying to fix an engine without looking under the hood. Before you change a single line, run `EXPLAIN ANALYZE` (in Postgres) or your database's equivalent. This command doesn't just guess what will happen; it executes the query and shows you the actual plan, including timing and row counts for each step. The output is your roadmap to the bottleneck. Often, you'll spot a `Seq Scan` (Sequential Scan) on a massive table where you expected an `Index Scan`. This means the database is reading the entire table from disk, row by row, because it couldn't use an index effectively. Why does this happen? Maybe the `WHERE` clause is on an unindexed column. Or perhaps you're using a function on an indexed column, like `WHERE UPPER(name) = '...'`, which prevents the database from using the standard B-tree index on `name`. The fix isn't to rewrite the whole query, but to create a function-based index or adjust the query to be index-friendly. Analyzing the plan turns optimization from guesswork into a precise, data-driven process. What's the most surprising bottleneck you've found using a query planner?

🚀 Struggling with complex SQL queries that are hard to debug? You don’t always need one giant query… 👉 Sometimes you need Temporary Tables 👇 --- 💡 What are Temporary Tables? Temporary tables store intermediate results for a short time. 👉 Created in "tempdb" 👉 Automatically deleted after session ends --- 📌 Local Temp Table (#) Visible only in your session Example: SELECT customer_id, SUM(total) AS total_spent INTO #customer_spend FROM orders GROUP BY customer_id --- 📌 Use it later easily SELECT * FROM #customer_spend WHERE total_spent > 500 --- 🌍 Global Temp Table (##) Visible across sessions Example: CREATE TABLE ##shared_data (id INT, value NVARCHAR(100)) --- ⚖️ Temp Table vs CTE vs Subquery 🔹 Subquery • Inline • Not reusable 🔹 CTE • More readable • Still limited to one query 🔹 Temp Table ✅ • Reusable across multiple steps • Can be indexed • Great for debugging --- 🔥 When should you use Temp Tables? ✔ Complex multi-step transformations ✔ Reusing intermediate results ✔ Breaking large queries into smaller steps ✔ Improving performance with indexing --- ⚠️ Common Mistake Using CTEs everywhere ❌ 👉 If you're reusing the same data multiple times 👉 Temp tables are a better choice --- 🔥 Real Insight (Important): Good SQL developers don’t write long queries… 👉 They break problems into steps --- 🧠 One-Line Takeaway: Temporary tables help you simplify, reuse, and optimize complex SQL workflows. --- #SQL #DataEngineering #SQLServer #LearnSQL #DataAnalytics #ETL #TechLearning #Analytics

Stop writing SQL for the database engine. Start writing it for the human who has to maintain it (probably you). We’ve all inherited that query. You know the one: 1,000 lines of monolithic code, nested subqueries seven levels deep, and zero comments. It runs, but modifying it feels like playing Jenga with production data. The engine doesn't care about your messy code, but your team's agility does. The shift every Data Analyst needs to make is toward Modular SQL. Modular code is readable code. Readable code is enhanceable code. Here is the blueprint for SQL that survives schema changes and business logic updates: ✅ DO: 1. Use CTEs (Common Table Expressions) to break complex logic into isolated steps. 2. Select explicit columns, never SELECT * in production. 3. Leverage Window Functions over messy self-joins. 4. Comment on WHY the logic exists, not how it works. ❌ DON'T: 1. Nest subqueries deeper than three levels. (Convert them to CTEs!) 2. Use SELECT * (protect your query from table schema evolution). 3. Perform raw date manipulation in WHERE clauses (isolate it in a CTE). 4. Adopt modular SQL. Save future-you hours of debugging. Less firefighting = More analysis. Check out the cheat sheet below. What’s the worst SQL anti-pattern you've encountered in code review? Share your pain below. 👇 #SQL #DataAnalytics #DataEngineering #CodingBestPractices #Analytics #DataScience #CareerGrowth