database-performance

Optimize EF Core and Dapper database access with query strategies.

Updated Dec 4, 2022

Search Tags:#performance #database #optimization #sql #cqrs #ef core #dapper

Ready to run

npx skills add https://github.com/devingoble/CloudOStat --skill database-performance-devingoble

💬 Prompt for ChatGPT / Claude Chat▼

Please help me install this Agent Skill.
Skill: database-performance
Source: https://github.com/devingoble/CloudOStat/tree/main/.github/skills/database-performance
Command: npx skills add https://github.com/devingoble/CloudOStat --skill database-performance-devingoble

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve?

This Skill addresses common performance bottlenecks in database interactions, helping developers write more efficient and scalable data access layers.

Core Features & Use Cases

Read/Write Separation: Implements CQRS patterns for distinct read and write models.
Query Optimization: Provides strategies to avoid N+1 queries, Cartesian explosions, and application-side joins.
Efficient Data Retrieval: Emphasizes using AsNoTracking, row limits, and precise column selection.
Use Case: Improve the loading speed of a user profile page by ensuring only necessary user data and their associated orders are fetched efficiently, avoiding redundant database calls.

Quick Start

Apply the AsNoTracking pattern to EF Core read queries to improve performance.

Frequently Asked Questions about database-performance

High-intent search queries and answers about installing and using this skill.

FAQPage Schema

How do I fix N+1 queries and Cartesian explosions in EF Core?▼

EF Core query optimization uses SQL-based joins and precise column selection to eliminate N+1 queries and Cartesian explosions. This approach fetches associated data efficiently through a single database call, preventing redundant queries and improving loading speed.

What is the best way to speed up database reads using EF Core?▼

Applying the AsNoTracking pattern to EF Core read queries bypasses change tracking overhead, directly speeding up database reads. Combining this with row limits and precise column selection ensures efficient data retrieval without unnecessary processing.

How does CQRS improve database optimization in .NET applications?▼

CQRS improves database optimization by implementing read/write model separation for distinct operations. This pattern allows specific query strategies for reads and writes, ensuring scalable data access layers that avoid generic repository anti-patterns.

When should I use Dapper instead of EF Core for query optimization?▼

Dapper is ideal for query optimization when you need direct SQL-based joins and precise control over data retrieval. It complements EF Core by handling performance-critical read operations, avoiding the generic repository anti-pattern.

Why does the generic repository pattern cause performance bottlenecks?▼

The generic repository pattern causes performance bottlenecks by enforcing uniform data access that ignores specific query needs. This leads to inefficient data retrieval and prevents read/write separation, causing N+1 queries and application-side joins.