EF Core SQL optimization helper that detects slow queries, N+1 problems, and missing indexes with detailed analysis and reporting.
$ dotnet add package SqlOptimizerHelperSqlOptimizerHelper is a powerful Entity Framework Core extension that automatically detects and analyzes SQL performance issues in your applications. It provides real-time monitoring, detailed analysis, and actionable suggestions to optimize your database queries.
Install the package via NuGet Package Manager:
dotnet add package SqlOptimizerHelper
Or via Package Manager Console:
Install-Package SqlOptimizerHelper
Add SqlOptimizerHelper to your EF Core configuration:
builder.Services.AddDbContext<AppDbContext>(options =>
options.UseSqlServer(builder.Configuration.GetConnectionString("Default"))
.AddSqlOptimizer()); // 👈 Your optimization helper
builder.Services.AddDbContext<AppDbContext>(options =>
options.UseSqlServer(builder.Configuration.GetConnectionString("Default"))
.AddSqlOptimizer(config =>
{
// Configure slow query threshold (default: 1000ms)
config.SlowQueryThresholdMs = 500;
// Enable/disable specific features
config.EnableN1Detection = true;
config.EnableIndexAnalysis = true;
config.EnableSlowQueryDetection = true;
// Configure logging
config.EnableConsoleOutput = true;
config.EnableJsonReports = true;
config.LogPath = "./logs";
// N+1 detection settings
config.N1DetectionThreshold = 5; // Flag after 5 similar queries
config.N1DetectionTimeWindowSeconds = 30; // Within 30 seconds
// Application identification
config.ApplicationName = "My E-Commerce App";
config.Environment = "Production";
}));{
"ConnectionStrings": {
"DefaultConnection": "Server=...;Database=MyDb;..."
},
"SqlOptimizer": {
"SlowQueryThresholdMs": 1000,
"EnableN1Detection": true,
"EnableIndexAnalysis": true,
"EnableSlowQueryDetection": true,
"EnableConsoleOutput": true,
"EnableJsonReports": true,
"LogPath": "./logs",
"N1DetectionThreshold": 5,
"N1DetectionTimeWindowSeconds": 30,
"ApplicationName": "My Application",
"Environment": "Production"
}
}Then use it in your configuration:
builder.Services.AddDbContext<AppDbContext>(options =>
options.UseSqlServer(builder.Configuration.GetConnectionString("Default"))
.AddSqlOptimizer(builder.Configuration));Your Code:
var products = await _context.Products
.Where(p => p.Name.Contains("phone"))
.ToListAsync();Generated SQL:
SELECT * FROM Products WHERE Name LIKE '%phone%'SqlOptimizerHelper Output:
[SQL Optimizer] ⚠️ Slow query detected (3.4s)
Warning: Slow query detected: 3400ms (threshold: 1000ms)
Suggestion: Consider adding indexes on frequently queried columns; Review query execution plan for optimization opportunities
Your Code:
var orders = await _context.Orders.ToListAsync();
foreach (var order in orders)
{
var customer = await _context.Customers
.FirstOrDefaultAsync(c => c.Id == order.CustomerId);
}SqlOptimizerHelper Output:
[SQL Optimizer] ⚠️ N+1 Query Detected
Warning: N+1 Query Detected: Customers queried 101 times in 30 seconds.
Suggestion: Use 'Include()' to fetch related Customers data in one query. Example: context.Orders.Include(o => o.Customer).ToListAsync()
Your Code:
var products = await _context.Products
.Where(p => p.Category == "Electronics")
.ToListAsync();SqlOptimizerHelper Output:
[SQL Optimizer] ⚡ MissingIndex detected
Warning: No index on 'Products.Category'. Consider creating an index.
Suggestion: CREATE INDEX IX_Products_Category ON Products(Category)
SqlOptimizerHelper automatically generates daily JSON reports in your specified log directory:
File: ./logs/sql-optimizer-report-2024-01-15.json
{
"generatedAt": "2024-01-15T10:30:00Z",
"applicationName": "My E-Commerce App",
"environment": "Production",
"analysisResults": [
{
"query": "SELECT * FROM Products WHERE Name LIKE '%phone%'",
"executionTimeMs": 3400,
"issueType": "SlowQuery",
"severity": "High",
"tableName": "Products",
"columnName": "Name",
"warning": "Slow query detected: 3400ms (threshold: 1000ms)",
"suggestion": "Consider adding indexes on frequently queried columns",
"timestamp": "2024-01-15T10:25:00Z"
},
{
"issueType": "N+1",
"severity": "High",
"tableName": "Customers",
"warning": "N+1 Query Detected: Customers queried 101 times in 30 seconds.",
"suggestion": "Use 'Include()' to fetch related Customers data in one query"
}
],
"summary": {
"totalQueries": 2,
"slowQueries": 1,
"n1Problems": 1,
"missingIndexes": 0,
"averageExecutionTimeMs": 1800,
"slowestQueryMs": 3400,
"totalExecutionTimeMs": 3600
}
}| Property | Type | Default | Description |
|---|---|---|---|
SlowQueryThresholdMs | long | 1000 | Threshold in milliseconds for slow query detection |
EnableN1Detection | bool | true | Enable N+1 query problem detection |
EnableIndexAnalysis | bool | true | Enable missing index analysis |
EnableSlowQueryDetection | bool | true | Enable slow query detection |
EnableConsoleOutput | bool | true | Enable real-time console logging |
EnableJsonReports | bool | true | Enable JSON report generation |
LogPath | string | "./logs" | Directory for log files and reports |
N1DetectionThreshold | int | 5 | Minimum queries to trigger N+1 detection |
N1DetectionTimeWindowSeconds | int | 30 | Time window for N+1 detection |
EnableDetailedSqlLogging | bool | false | Log full SQL queries (security consideration) |
MaxResultsInMemory | int | 1000 | Maximum analysis results to keep in memory |
ApplicationName | string | "Unknown" | Application name for reports |
Environment | string | "Development" | Environment name for reports |
// Get current analysis results
var results = SqlOptimizerService.GetAnalysisResults();
// Generate a report
var report = SqlOptimizerService.GenerateReport();
// Clear stored results
SqlOptimizerService.ClearResults();
// Check if service is registered
var isRegistered = SqlOptimizerService.IsRegistered();// Access the interceptor directly
var interceptor = SqlOptimizerService.GetInterceptor();
if (interceptor != null)
{
var results = interceptor.GetAnalysisResults();
var report = interceptor.GenerateFinalReport();
}The library includes comprehensive unit and integration tests. Run tests using:
dotnet testSqlOptimizerHelper is designed to have minimal performance impact:
Contributions are welcome! Please feel free to submit a Pull Request.
This project is licensed under the MIT License - see the LICENSE file for details.
Made with ❤️ for the .NET community