Entity Framework Core DB Performance Tuning (for SQL Server)

1) Course Introduction

1. Intro
2. Pre-learning materials for beginners
3. Course Introduction
4. Introducing SalesSimple DB for Demo
5. Introducing the Visual Studio Demo Project
6. Visual Studio Configuration Demo and LINQ Expressions (Beginner)
7. Key Elements of DB Performance Tuning

2) Code-First Performance Considerations

1. Database property tuning
2. Data format and length
3. Specify a composite key or index
4. Create an index when specifying a foreign (reference) key

3) Query logging and performance monitoring

1. Tools for query performance monitoring and analysis
2. Simple Logging
3. SQL Server query tracing
4. Dotnet-counters
5. BenchmarkDotNet
6. Overview of Data Access Procedures - Query Response Time (T) Consumption Items

4) DB Connection

1. Connection String Important Items
2. DbContextOptionsBuilder
3. DbContextPool
4. Understanding DB Connection Open and Close Points
5. Multiple Active ResultSets (MARS) connections

5) Data Retrieval - Part 1

1. introduction
2. Synchronous vs. Asynchronous Processing
3. Ad hoc vs. parameter queries
4. ToQueryString()
5. Non-tracking queries
6. SELECT only the necessary columns (Projection)
7. Benchmark - Performance by Data Retrieval Method
8. IQueryable vs. IEnumerable
9. Where(), Find(), First() vs. FirstOrDefault(), SingleOrDefault()
10. FirstOrDefault()/SingleOrDefault() and Subquery Issues
11. SingleOrDefault() and SQL LIKE implementation examples
12. Using EF.Functions, SQL IN Conditions, and the STRING_AGG() Function

6) Data Retrieval - Part 2

1. CROSS/INNER/OUTER JOIN
2. APPLY implementation
3. .Include(), .ThenInclude(), Split Queries, and EF Internal Buffering
4. Lazy Loading vs. Eager Loading and Avoiding Unnecessary Duplicate Queries
5. Set operations (intersection/union/difference) and distinct handling

7) Data Retrieval - Part 3

1. Prerequisites for using the index SARG
2. Understanding Non-SARG Types

8) Advanced queries and more

1. Paging query
2. Dynamic search condition (aka universal lookup) query
3. Compile Query
4. Stopping an asynchronous query
5. Annotating queries with .Tag()

9) Direct use of SQL

1. Classification of functions that support direct use of SQL
2. Adhoc or Parameterization query
3. Calling table-valued functions (TVFs)
4. Calling a scalar type (non-entity) query
5. Entity-based stored procedure call
6. Calling a stored procedure for querying
7. Calling a stored procedure that returns multiple result sets

10) DML operations (INSERT/UPDATE/DELETE), EF 6 vs. EF7

1. Single or Batch INSERT
2. Bulk data changes and batch processing
3. Improved SaveChanges() in EF Core 7
4. Single or Batch UPDATE
5. Single or Batch DELETE
6. ExecuteUpdate() or ExecuteDelete() for bulk updates
7. Direct use of UPDATE or DELETE statements

11) Transactions and Locks

1. Transaction processing considering DB performance
2. Transactions, isolation levels, Commit/Rollback/SavePoint
3. Read Committed Isolation Level and Lock Blocking
4. Repeatable Read isolation level
5. Dormant Transaction Problem
6. Case Study – “Dormant Transaction” Performance Issues
7. Avoiding blocking in read-only queries (NOLOCK or RCS)

12) Distributed Transaction (TransactionScope)

1. TransactionScope class performance issues, remediation, and management

13) Appendix

1. Calling scalar-valued user-defined functions (UDFs)
2. EF Core Optimistic Concurrency Support
3. Inheriting DbCommandInterceptor – Adding Hints to Queries
4. Setting query hints without modifying application code