Tutorials ADO.NET Core Tutorial
Query Optimization — Complete Guide
Query Optimization — Complete Guide: free step-by-step lesson with examples, common mistakes, and interview tips — part of ADO.NET Core Tutorial on Toolliyo Academy.
On this page
ADO.NET Core Tutorial · Lesson 43 of 100
Query Optimization
Foundations ✓ → SQL & safety → Production → Projects
SQL & safety · 2 — Procs, tx, performance · ~6 min · Module 5: Performance Optimization
What is this?
Optimize queries with sargable predicates, covering indexes, and fewer columns — verify with actual plans.
Why should you care?
ShopNest list endpoints die on non-sargable YEAR(OrderDate) filters.
See it live — copy this example
Use a .NET console or API project with SQL Server LocalDB. Run dotnet run after pasting.
// bad: WHERE YEAR(OrderDate)=2026
// good:
cmd.CommandText = @"SELECT Id, Total FROM Orders
WHERE OrderDate >= @From AND OrderDate < @To";
cmd.Parameters.Add("@From", SqlDbType.DateTime2).Value = new DateTime(2026,1,1);
cmd.Parameters.Add("@To", SqlDbType.DateTime2).Value = new DateTime(2027,1,1);What happened?
- Range seeks beat functions on columns.
- Select only needed columns.
- Parameterize.
Practice next
- Rewrite YEAR filter.
- Compare plans.
- Add index on OrderDate.
- Add CustomerId to filter+index.
- Page with keyset.
Remember
Sargable predicates. Narrow selects. Prove with plans.
ShopNest year report fix
Finance query rewritten to range.
Outcome: Plan switches to seek.
Interview prep for this lesson
Practice these questions aloud after reading—each links to a full structured answer.
Sign in to ask a question or upvote helpful answers.
No questions yet — be the first to ask!
