SQL Optimization Tips for Better Performance

🚀 12 Rules for High-Performance SQL Stored Procedures When it comes to backend engineering, database bottlenecks can be considered "silent killers" of your application's performance. After years of evaluating execution plans, I’ve identified these twelve optimization strategies as having the most significant impact on improving performance. The basics: 1. SET NOCOUNT ON: Prevent unnecessary "rows affected" messages from communicating on the network. 2. Specify Columns: Never SELECT *, only retrieve the columns you actually need to minimize I/O. 3. Schema Qualification: Use [dbo].[Table]. This eliminates the need for the engine to look through all the schemas during compilation. 4. IF EXISTS > COUNT(): Do not scan the entire table to find out whether or not there is a record. The Architecture Level: 5. Write SARGable Queries: Use WHERE clauses that can make use of an index on the referenced column. Do not create functions on that column name. For example, instead of using the function YEAR(Date) = 2024; you should write the same logic as Date >= '2024-01-01'. 6. Lean Transactions: The longer a transaction is, the more likely you will run into deadlocks or blocks. 7. Prefer UNION ALL to UNION: Do not use the expensive internal Sort/Distinct unless you have to have unique rows. 8. Avoid Scalar Functions: They are just like loops; use Inline Table-Valued Functions instead of scalars to allow for a better execution plan. Pro-Level Tuning: 9. Table Vars vs. Temp Tables: Use @Table for small datasets ($<1000$ rows). They lead to fewer recompiles but lack statistics (the optimizer assumes 1 row). Use #Temp for large datasets or complex joins. They support full statistics and indexing, allowing the engine to generate an accurate execution plan. 10. Manage Parameter Sniffing: Use local variables to prevent the engine from locking into a sub-optimal plan based on one specific input. 11. Set-Based Logic: Ditch the Cursors. SQL is built for sets, not row-by-row looping.. 12. Never use dynamic SQL: it presents significant security vulnerabilities and will also reduce the ability for an execution plan to be reused. #SQLServer #DatabaseOptimization #BackendEngineering #DotNet #CleanCode #ProgrammingTips #SoftwareArchitecture

📒 SQL Performance Tuning — Notes (Backend Devs Should Know) 🧠 What is SQL Performance? How fast your query returns results + How efficiently it uses resources (CPU, Memory, I/O) ⚠️ Common Mistakes: • Using SELECT * • Missing indexes • Writing complex joins without filtering • Ignoring execution plans • Fetching unnecessary data ⚙️ Core Concepts: 👉 Indexing • Speeds up data retrieval • Works like a “table of contents” • Over-indexing can slow down writes 👉 Query Optimization • Filter early (WHERE clause) • Avoid nested subqueries (use joins wisely) • Use proper joins (INNER vs LEFT) 👉 Execution Plan • Shows how SQL actually runs your query • Helps find bottlenecks • Always analyze for slow queries 👉 Normalization vs Denormalization • Normalization → avoids redundancy • Denormalization → improves read performance 🚀 Pro Tips: • Use indexes on frequently searched columns • Avoid functions on indexed columns • Use LIMIT / TOP when needed • Cache frequently used data • Monitor slow queries regularly 💡 Reality Check: Fast code ≠ Fast application 👉 Database performance is the real bottleneck in most systems DotNet #CSharp #SQL #SQLServer #ASPNet #BackendDevelopment #DatabaseDesign

Stop using .Skip().Take() for large datasets I used to think that Offset Pagination (using .Skip() and .Take()) was the ultimate way to page through data in .NET. It works perfectly fine for small tables. But recently, while diving deeper into database performance, I realized it can become a silent performance killer. Here is what happens behind the scenes: The Problem with Offset Pagination: When you query Skip(10000).Take(10), the SQL engine doesn't magically jump to row 10,001. It physically scans and counts the first 10,000 rows, loads them, and then throws them away! Result: The higher the page number, the slower the query ($O(n)$ complexity). Plus, if records are added/deleted while scrolling, users might see duplicate items (Data Drift) The Fix: Cursor Pagination (Keyset Pagination) Instead of skipping, you use a "bookmark" (like the last seen ID) and filter from there: Where(p => p.Id > lastSeenId).Take(10). Result: The database uses an Index Seek to jump directly to that specific ID. The query speed remains flat and blazing fast, whether you have 1,000 or 10,000,000 rows (O(log n) complexity). #DotNet #DotNetCore #CSharp #EntityFrameworkCore #EFCore #SQLServer #LINQ #WebAPI #SystemDesign #Scalability #DatabaseOptimization #PerformanceTuning #SoftwareArchitecture #DataEngineering #Pagination

💥 Stored Procedure vs Query — which one should you use? 🤔 I used to write direct queries everywhere… until I realized where Stored Procedures actually help 😅 🔍 The Confusion: When should you use: 👉 Direct SQL Query 👉 Stored Procedure ✅ Use Stored Procedure when: ✔️ You need better performance (execution plan reuse) ✔️ You want security (no direct table access) ✔️ Logic is complex & reusable ✔️ Multiple operations in one call ✅ Use Direct Query when: ✔️ Simple SELECT/INSERT ✔️ One-time or small logic ✔️ Quick debugging ⚡ Realization: It’s not about “which is better” ❌ It’s about “where to use what” ✅ ⚡ Pro Tip: In large applications → Stored Procedures can make your system more structured and secure 🔐 💬 What do you prefer more—Stored Procedures or direct queries? Let’s discuss 👇 🔖 Save this post for future decisions! #sql #database #developer #coding #backend #tricks

Your database is probably slower than it needs to be. Most developers optimize queries last, after the damage is done. By then, you're fighting against schema decisions, missing indexes, and N+1 problems baked into your application logic. The real win happens earlier. Understanding your access patterns before you build saves weeks of refactoring. Things like denormalization, partitioning strategies, and query execution plans aren't exciting, but they're the difference between a system that scales and one that doesn't. Here's what actually moves the needle: profile your queries in development, not production. Use EXPLAIN plans. Test with realistic data volumes. Catch the slow ones before they become someone else's nightmare at 2 AM. What's the worst database performance issue you've inherited and had to fix? #Database #Performance #SQL #SoftwareEngineering #BackendDevelopment

🚀 SQL: The Skill That Quietly Decides Your System’s Performance One thing I’ve learned while working on backend systems it’s not always the code slowing things down it’s the queries. A simple API can become slow if the SQL behind it isn’t optimized. Here are a few things that made a real difference in my work 👇 • Writing queries is easy writing efficient queries is the real skill • Indexing properly can reduce response time from seconds to milliseconds • Avoiding unnecessary joins and selecting only required columns matters • Understanding execution plans helps identify bottlenecks quickly • Database performance directly impacts user experience In one of my projects, optimizing queries and adding proper indexing significantly reduced API latency during peak traffic. 💡 Good backend systems are not just about APIs they are built on strong database design and efficient queries. 💬 What’s one SQL optimization trick that worked for you? #SQL #Database #BackendDevelopment #PerformanceOptimization #SystemDesign #SoftwareEngineering

A week ago, I posted about a small database I was building to understand how databases actually work. This week… it got real. It now survives crashes mid-write and supports transactions. What started as: “let me try storing data in columns” is now: • writing through a WAL (write-ahead log) • replaying state after crashes • supporting multi-step atomic transactions • parsing queries → building an AST → planning → executing • deciding between index vs full scan • running aggregations directly on columnar data Somewhere along the way, it stopped feeling like a project and started feeling like a database. Still a long way to go: • no server / multi-user support yet • no joins • vector search is still brute-force • lots to improve in performance But honestly, building this has been one of the best ways to actually understand databases. Not just use them. If you’ve worked on databases / storage engines / Rust — I’d really value your feedback. Repo: https://lnkd.in/dEVW4aDB

Working on this with him has been one of the most educational things I’ve done. Biggest shift for me: A DB is not just “store + query”. It is mostly about guarantees under failure. Things that changed how I think: Schema validation is easy; preserving correctness after crash/replay is hard. WAL design affects almost everything: write path, recovery, compaction, tests. Query performance is less about syntax, more about planner decisions and data layout. “Fast” means nothing without workload-specific benchmarks and repeatability. Observability matters early. If you can’t measure replay/plan/scan behavior, you’re guessing. What I’m excited about next: better transaction semantics ANN/vector indexes (instead of brute force) stronger concurrency model production-grade benchmarking and recovery testing If anyone has experience with storage engines in Rust, especially around WAL recovery edge-cases and planner heuristics, I would love to learn from your feedback.

Most developers know indexes make queries faster. But if you don't understand the tradeoffs, you'll either index too much and slow your database down or too little and kill your read performance. Here's what's actually happening 👇 When you query a database with no index, it scans every single row in the table. That's fine at 1,000 rows. But at 10 million rows? It's a disaster!!!. An index lets the database jump straight to the data it needs:- like a book index that takes you to the exact page instead of making you read the whole textbook. Under the hood, most databases use a B-tree structure. Instead of checking millions of rows, the database makes roughly 30 decisions and arrives at the answer. That's the difference between a slow app and a fast one. Indexes cost you on writes. Every INSERT, UPDATE, or DELETE forces the database to update the index too, not just the table. The more indexes you have, the more overhead every write carries. So the strategy is simple: - Index columns you filter and search on frequently - Prioritise columns with lots of unique values; IDs, emails, timestamps - Avoid indexing boolean or low-variety columns; they rarely help - Go easy on tables that get written to constantly Indexing is a deliberate decision, not a default setting. Get it right, and your queries fly. Get it wrong, and that performance debt compounds fast at scale. _________________________________________ What's the worst index-related bug you've ever seen? Drop it in the comments 👇 #Database #DatabaseIndexing #SQL #SoftwareEngineering #BackendDevelopment #TechTips #DataEngineering #Programming #SystemDesign #Engineering

💡 A small SQL setting caused a production issue… Yesterday, we ran into a tricky issue that took time to debug — and the root cause was something very small: 👉 SET NOCOUNT ON One of our developers wrote a stored procedure, and as part of the default structure, SET NOCOUNT ON was enabled. At first glance, everything looked fine. But something wasn’t working as expected in our ADO.NET (.NET) application. 🔍 What went wrong? In our application logic, we had a condition: ➡️ If rows affected > 0 → Commit transaction But due to SET NOCOUNT ON, SQL Server was not returning the number of affected rows. So from the application’s perspective: ❌ Rows affected = 0 ❌ Transaction logic failed ⚠️ The tricky part SET NOCOUNT ON improves performance by suppressing messages like: “(1 row affected)” But at the same time, it can silently break logic that depends on row counts. 🚀 Lesson learned Sometimes, small defaults can lead to big issues. ✔️ Be careful when relying on “rows affected” in application logic ✔️ Understand how SQL settings impact your backend code ✔️ Always test stored procedures with real integration scenarios 💬 Have you ever faced a bug caused by something this small? #SQLServer #DotNet #BackendDevelopment #SoftwareEngineering #Debugging #Learning #CleanCode