database-performance

Name: database-performance
Author: wshaddix

65stars10forks

How to install database-performance?

wshaddix/dotnet-skills/database-performance

$npx -y skills add wshaddix/dotnet-skills --skill database-performance

Installs into the current project.

Summary

Database access patterns for performance. Separate read/write models, avoid N+1 queries, use AsNoTracking, apply row limits, and never do application-side joins. Works with EF Core and Dapper. Use when designing data access layers, optimizing slow database queries, choosing betwe

Files · 1

View on GitHub

SKILL.md

1	# Database Performance Patterns
2
3	## When to Use This Skill
4
5	Use this skill when:
6	- Designing data access layers
7	- Optimizing slow database queries
8	- Choosing between EF Core and Dapper
9	- Avoiding common performance pitfalls
10
11	---
12
13	## Core Principles
14
15	1. Separate read and write models - Don't use the same types for both
16	2. Think in batches - Avoid N+1 queries
17	3. Only retrieve what you need - No SELECT *
18	4. Apply row limits - Always have a configurable Take/Limit
19	5. Do joins in SQL - Never in application code
20	6. AsNoTracking for reads - EF Core change tracking is expensive
21
22	---
23
24	## Read/Write Model Separation (CQRS Pattern)
25
26	Read and write models are fundamentally different - they have different shapes, columns, and purposes. Don't create a single "User" entity and reuse it everywhere.
27
28	- Read models are denormalized, optimized for query efficiency, and return multiple projection types (UserProfile, UserSummary, UserDetailForAdmin)
29	- Write models are normalized, validation-focused, and accept strongly-typed commands (CreateUserCommand, UpdateUserCommand)
30
31	### Architecture
32
33	```
34	src/
35	MyApp.Data/
36	Users/
37	# Read side - multiple optimized projections
38	IUserReadStore.cs
39	PostgresUserReadStore.cs
40
41	# Write side - command handlers
42	IUserWriteStore.cs
43	PostgresUserWriteStore.cs
44
45	# Read DTOs - lightweight, denormalized
46	UserProfile.cs
47	UserSummary.cs
48
49	# Write commands - validation-focused
50	CreateUserCommand.cs
51	UpdateUserCommand.cs
52	Orders/
53	IOrderReadStore.cs
54	IOrderWriteStore.cs
55	(similar structure...)
56	```
57
58	### Read Store Interface
59
60	```csharp
61	// Read models: Multiple specialized projections optimized for different use cases
62	public interface IUserReadStore
63	{
64	// Returns detailed profile for single-user view
65	Task<UserProfile?> GetByIdAsync(UserId id, CancellationToken ct = default);
66
67	// Returns lightweight info for lookups
68	Task<UserProfile?> GetByEmailAsync(EmailAddress email, CancellationToken ct = default);
69
70	// Returns paginated summaries - only what the list view needs
71	Task<IReadOnlyList<UserSummary>> GetAllAsync(int limit, UserId? cursor = null, CancellationToken ct = default);
72
73	// Boolean query - no entity needed
74	Task<bool> EmailExistsAsync(EmailAddress email, CancellationToken ct = default);
75	}
76	```
77
78	### Write Store Interface
79
80	```csharp
81	// Write model: Accepts strongly-typed commands, minimal return values
82	public interface IUserWriteStore
83	{
84	// Returns only the created ID - caller doesn't need the full entity
85	Task<UserId> CreateAsync(CreateUserCommand command, CancellationToken ct = default);
86
87	// Update validates command, returns void (success or throws)
88	Task UpdateAsync(UserId id, UpdateUserCommand command, CancellationToken ct = default);
89
90	// Delete is simple and explicit
91	Task DeleteAsync(UserId id, CancellationToken ct = default);
92	}
93	```
94
95	Key structural differences illustrated:
96	- Read store returns multiple different DTOs (UserProfile, UserSummary, bool flag)
97	- Write store returns minimal data (just UserId on create) or void
98	- Read queries are stateless projections - no tracking needed
99	- Write operations focus on command validation, not retrieving data afterwards
100	- Different databases/tables can back read vs write (eventual consistency pattern)
101
102	---
103
104	## Always Apply Row Limits
105
106	Never return unbounded result sets. Every read method should have a configurable limit.
107
108	### Pattern: Limit Parameter
109
110	```csharp
111	public interface IOrderReadStore
112	{
113	// Limit is required, not optional
114	Task<IReadOnlyList<OrderSummary>> GetByCustomerAsync(
115	CustomerId customerId,
116	int limit,
117	OrderId? cursor = null,
118	CancellationToken ct = default);
119	}
120
121	// Implementation
122	public async Task<IReadOnlyList<OrderSummary>> GetByCustomerAsync(
123	CustomerId customerId,
124	int limit,
125	OrderId? cursor = null,
126	CancellationToken ct = default)
127	{
128	await using var connection = await _dataSource.OpenConnectionAsync(ct);
129
130	const string sql = """
131	SELECT id, customer_id, total, status, created_at
132	FROM orders
133	WHERE customer_id = @CustomerId
134	AND (@Cursor IS NULL OR created_at < (SELECT created_at FROM orders WHERE id = @Cursor))
135	ORDER BY created_at DESC
136	LIMIT @Limit
137	""";
138
139	var rows = await connection.QueryAsync<OrderRow>(sql, new
140	{
141	CustomerId = customerId.Value,
142	Cursor = cursor?.Value,
143	Limit = limit
144	});
145
146	return rows.Select(r => r.ToOrderSummary()).ToList()

People who liked this also viewed

6 picks

Type

database-performance

65stars10forks

View on GitHub

How to install database-performance?

wshaddix/dotnet-skills/database-performance

$npx -y skills add wshaddix/dotnet-skills --skill database-performance

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# Database Performance Patterns
2
3	## When to Use This Skill
4
5	Use this skill when:
6	- Designing data access layers
7	- Optimizing slow database queries
8	- Choosing between EF Core and Dapper
9	- Avoiding common performance pitfalls
10
11	---
12
13	## Core Principles
14
15	1. Separate read and write models - Don't use the same types for both
16	2. Think in batches - Avoid N+1 queries
17	3. Only retrieve what you need - No SELECT *
18	4. Apply row limits - Always have a configurable Take/Limit
19	5. Do joins in SQL - Never in application code
20	6. AsNoTracking for reads - EF Core change tracking is expensive
21
22	---
23
24	## Read/Write Model Separation (CQRS Pattern)
25
26	Read and write models are fundamentally different - they have different shapes, columns, and purposes. Don't create a single "User" entity and reuse it everywhere.
27
28	- Read models are denormalized, optimized for query efficiency, and return multiple projection types (UserProfile, UserSummary, UserDetailForAdmin)
29	- Write models are normalized, validation-focused, and accept strongly-typed commands (CreateUserCommand, UpdateUserCommand)
30
31	### Architecture
32
33	```
34	src/
35	MyApp.Data/
36	Users/
37	# Read side - multiple optimized projections
38	IUserReadStore.cs
39	PostgresUserReadStore.cs
40
41	# Write side - command handlers
42	IUserWriteStore.cs
43	PostgresUserWriteStore.cs
44
45	# Read DTOs - lightweight, denormalized
46	UserProfile.cs
47	UserSummary.cs
48
49	# Write commands - validation-focused
50	CreateUserCommand.cs
51	UpdateUserCommand.cs
52	Orders/
53	IOrderReadStore.cs
54	IOrderWriteStore.cs
55	(similar structure...)
56	```
57
58	### Read Store Interface
59
60	```csharp
61	// Read models: Multiple specialized projections optimized for different use cases
62	public interface IUserReadStore
63	{
64	// Returns detailed profile for single-user view
65	Task<UserProfile?> GetByIdAsync(UserId id, CancellationToken ct = default);
66
67	// Returns lightweight info for lookups
68	Task<UserProfile?> GetByEmailAsync(EmailAddress email, CancellationToken ct = default);
69
70	// Returns paginated summaries - only what the list view needs
71	Task<IReadOnlyList<UserSummary>> GetAllAsync(int limit, UserId? cursor = null, CancellationToken ct = default);
72
73	// Boolean query - no entity needed
74	Task<bool> EmailExistsAsync(EmailAddress email, CancellationToken ct = default);
75	}
76	```
77
78	### Write Store Interface
79
80	```csharp
81	// Write model: Accepts strongly-typed commands, minimal return values
82	public interface IUserWriteStore
83	{
84	// Returns only the created ID - caller doesn't need the full entity
85	Task<UserId> CreateAsync(CreateUserCommand command, CancellationToken ct = default);
86
87	// Update validates command, returns void (success or throws)
88	Task UpdateAsync(UserId id, UpdateUserCommand command, CancellationToken ct = default);
89
90	// Delete is simple and explicit
91	Task DeleteAsync(UserId id, CancellationToken ct = default);
92	}
93	```
94
95	Key structural differences illustrated:
96	- Read store returns multiple different DTOs (UserProfile, UserSummary, bool flag)
97	- Write store returns minimal data (just UserId on create) or void
98	- Read queries are stateless projections - no tracking needed
99	- Write operations focus on command validation, not retrieving data afterwards
100	- Different databases/tables can back read vs write (eventual consistency pattern)
101
102	---
103
104	## Always Apply Row Limits
105
106	Never return unbounded result sets. Every read method should have a configurable limit.
107
108	### Pattern: Limit Parameter
109
110	```csharp
111	public interface IOrderReadStore
112	{
113	// Limit is required, not optional
114	Task<IReadOnlyList<OrderSummary>> GetByCustomerAsync(
115	CustomerId customerId,
116	int limit,
117	OrderId? cursor = null,
118	CancellationToken ct = default);
119	}
120
121	// Implementation
122	public async Task<IReadOnlyList<OrderSummary>> GetByCustomerAsync(
123	CustomerId customerId,
124	int limit,
125	OrderId? cursor = null,
126	CancellationToken ct = default)
127	{
128	await using var connection = await _dataSource.OpenConnectionAsync(ct);
129
130	const string sql = """
131	SELECT id, customer_id, total, status, created_at
132	FROM orders
133	WHERE customer_id = @CustomerId
134	AND (@Cursor IS NULL OR created_at < (SELECT created_at FROM orders WHERE id = @Cursor))
135	ORDER BY created_at DESC
136	LIMIT @Limit
137	""";
138
139	var rows = await connection.QueryAsync<OrderRow>(sql, new
140	{
141	CustomerId = customerId.Value,
142	Cursor = cursor?.Value,
143	Limit = limit
144	});
145
146	return rows.Select(r => r.ToOrderSummary()).ToList()

People who liked this also viewed

6 picks

Type