SqlOptimizerHelper 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.
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
Case 2: N+1 Query Problem
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()
[SQL Optimizer] ⚡ MissingIndex detected
Warning: No index on 'Products.Category'. Consider creating an index.
Suggestion: CREATE INDEX IX_Products_Category ON Products(Category)
📊 Report Generation
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
}
}
⚙️ Configuration Options
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
🛠️ Advanced Usage
Programmatic Access
// 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();
Custom Analysis
// Access the interceptor directly
var interceptor = SqlOptimizerService.GetInterceptor();
if (interceptor != null)
{
var results = interceptor.GetAnalysisResults();
var report = interceptor.GenerateFinalReport();
}
🧪 Testing
The library includes comprehensive unit and integration tests. Run tests using:
dotnet test
📈 Performance Impact
SqlOptimizerHelper is designed to have minimal performance impact:
