Query Tuning in .NET Applications

Why Tune Queries?

Database queries are often the bottleneck in .NET applications. Even well‑written code can suffer from slow data access if the underlying SQL is inefficient. Tuning improves response time, reduces resource consumption, and provides a better user experience.

Profiling Your Queries

Use Microsoft.Data.SqlClient with EventSource or MiniProfiler to capture execution statistics. Below is a sample snippet that logs query duration.

using System;
using System.Data;
using Microsoft.Data.SqlClient;
using StackExchange.Profiling;

public static async Task<DataTable> ExecuteAsync(string sql, SqlParameter[]? parameters = null)
{
    var sw = Stopwatch.StartNew();
    using var conn = new SqlConnection("YourConnectionString");
    using var cmd = new SqlCommand(sql, conn);
    if (parameters != null) cmd.Parameters.AddRange(parameters);
    var dt = new DataTable();
    await conn.OpenAsync();
    using var reader = await cmd.ExecuteReaderAsync();
    dt.Load(reader);
    sw.Stop();
    MiniProfiler.Current.CustomTiming("sql", $"{sql} – {sw.ElapsedMilliseconds} ms");
    return dt;
}

LINQ Optimizations

Entity Framework Core translates LINQ to SQL, but not all constructs are efficient. Follow these guidelines:

• Avoid .ToList() before applying Where or OrderBy.
• Prefer .Any() over .Count() > 0 for existence checks.
• Use .Select to fetch only required columns.
• Leverage AsNoTracking() for read‑only queries.

var recentOrders = await ctx.Orders
    .AsNoTracking()
    .Where(o => o.CreatedDate > DateTime.UtcNow.AddDays(-30))
    .Select(o => new { o.Id, o.Total, o.Customer.Name })
    .OrderByDescending(o => o.CreatedDate)
    .ToListAsync();

When to Use Raw SQL

Complex joins, window functions, or database‑specific features may be hard to express in LINQ. Use FromSqlRaw or Dapper for such cases.

var sql = @"
    SELECT o.Id, o.Total, c.Name,
           ROW_NUMBER() OVER (ORDER BY o.CreatedDate DESC) AS RowNum
    FROM Orders o
    JOIN Customers c ON o.CustomerId = c.Id
    WHERE o.CreatedDate > @StartDate";
var orders = await ctx.Orders
    .FromSqlRaw(sql, new SqlParameter("@StartDate", DateTime.UtcNow.AddMonths(-1)))
    .ToListAsync();

Caching Strategies

Cache static reference data and frequently requested query results.

public async Task<IReadOnlyList<Product>> GetPopularProductsAsync()
{
    var cacheKey = "popular-products";
    var cached = await _cache.GetAsync p.IsPopular)
        .ToListAsync();

    await _cache.SetAsync(cacheKey, products, TimeSpan.FromHours(1));
    return products;
}

Continuous Monitoring

Integrate Application Insights or OpenTelemetry to capture query latency and failures in production.

using Azure.Monitor.OpenTelemetry.Exporter;
using OpenTelemetry.Trace;

var tracerProvider = Sdk.CreateTracerProviderBuilder()
    .AddSqlClientInstrumentation()
    .AddAzureMonitorTraceExporter(o => o.ConnectionString = "YourAppInsightsConnectionString")
    .Build();