Great question—this is a super common pain point with report-heavy applications, especially when users get impatient with slow loads and start refreshing repeatedly. Let's break down the core problem and walk through proven solutions, starting with the specific implementation you asked for (canceling stored procedures when users leave the page) and covering other best practices too.

Your two proposed solutions are solid, and we can expand them with additional industry-standard approaches to tackle the root cause and mitigate the problem:

• Cancel in-flight database requests when users leave the page
• Cache and reuse stored procedure results to avoid duplicate executions
• Add frontend safeguards to prevent accidental repeated requests
• Optimize the stored procedures themselves to reduce execution time

To stop orphaned stored procedure executions when users navigate away or refresh, we need coordination between the frontend (detecting page unload) and backend (tracking and canceling active requests). Here's a concrete implementation using ASP.NET and JavaScript:

Backend Code (Async with Cancellation Support) #

We'll track cancellation tokens per user session using a thread-safe dictionary, modify our stored procedure execution to support async cancellation, and add an endpoint to trigger cancellation:

using System.Collections.Concurrent;
using System.Data;
using System.Data.SqlClient;
using Microsoft.AspNetCore.Http;

// Thread-safe storage for user-specific cancellation tokens
private static readonly ConcurrentDictionary<string, CancellationTokenSource> _userCancellationTokens = 
    new ConcurrentDictionary<string, CancellationTokenSource>();

// Async stored procedure execution with cancellation support
public async Task<DataSet> ExecuteReportProcedureAsync(string procedureName, params SqlParameter[] parameters)
{
    var sessionId = HttpContext.Session.Id;
    
    // Cancel any existing in-flight request for this user first
    if (_userCancellationTokens.TryRemove(sessionId, out var oldTokenSource))
    {
        oldTokenSource.Cancel();
        oldTokenSource.Dispose();
    }

    var newTokenSource = new CancellationTokenSource();
    _userCancellationTokens.TryAdd(sessionId, newTokenSource);

    try
    {
        using (var cmd = CreateCommand(procedureName, CommandType.StoredProcedure, parameters))
        {
            cmd.CommandTimeout = 30; // Set a reasonable timeout to avoid permanent orphaned processes
            var adapter = new SqlDataAdapter(cmd);
            var dataSet = new DataSet();

            // Use async execution to support reliable cancellation
            await Task.Run(() => adapter.Fill(dataSet), newTokenSource.Token);
            return dataSet;
        }
    }
    catch (OperationCanceledException)
    {
        // Handle cancellation gracefully (return empty or null as needed)
        return null;
    }
    finally
    {
        // Clean up tokens to prevent memory leaks
        _userCancellationTokens.TryRemove(sessionId, out _);
        newTokenSource.Dispose();
    }
}

// Endpoint to trigger cancellation (called from frontend)
[HttpPost("/api/cancel-report-request")]
public IActionResult CancelActiveReportRequest()
{
    var sessionId = HttpContext.Session.Id;
    if (_userCancellationTokens.TryRemove(sessionId, out var tokenSource))
    {
        tokenSource.Cancel();
        tokenSource.Dispose();
        return Ok("Active report request canceled");
    }
    return NotFound("No active report request found to cancel");
}

Frontend Code #

Add an event listener to detect page unload/refresh and call the cancellation endpoint:

// Listen for page unload events
window.addEventListener('beforeunload', async () => {
    try {
        // Send cancellation request to backend (include session credentials)
        await fetch('/api/cancel-report-request', {
            method: 'POST',
            credentials: 'include'
        });
    } catch (error) {
        // Ignore errors here—page is unloading anyway
        console.debug('Failed to cancel report request:', error);
    }
});

Critical Notes #

• Session Configuration: Ensure your app uses user sessions correctly so each user gets a unique SessionId.
• Memory Cleanup: Add a background task to periodically remove expired tokens from the dictionary to avoid memory leaks.
• Async Requirement: Synchronous Fill calls can't be canceled reliably, so switching to async execution is mandatory here.

For reports that don't need real-time data, caching results drastically reduces database load. We'll use a distributed cache (like Redis) and add a lock to prevent duplicate executions for the same report parameters:

using IDistributedCache _cache; // Inject via dependency injection
using IDistributedLock _distributedLock; // Use a library like RedLock.net for cross-server locks

public async Task<DataSet> GetCachedReportDataAsync(string procedureName, params SqlParameter[] parameters)
{
    // Generate a unique cache key based on procedure name and parameters
    var cacheKey = GenerateCacheKey(procedureName, parameters);
    
    // Check cache first
    var cachedDataSet = await _cache.GetAsync<DataSet>(cacheKey);
    if (cachedDataSet != null)
    {
        return cachedDataSet;
    }

    // Use a distributed lock to ensure only one request runs the procedure for this key
    using (var lockHandle = await _distributedLock.AcquireAsync(cacheKey, TimeSpan.FromSeconds(10)))
    {
        // Double-check cache to avoid redundant execution after waiting for the lock
        cachedDataSet = await _cache.GetAsync<DataSet>(cacheKey);
        if (cachedDataSet != null)
        {
            return cachedDataSet;
        }

        // Execute the procedure and cache the result
        var freshDataSet = await ExecuteReportProcedureAsync(procedureName, parameters);
        await _cache.SetAsync(cacheKey, freshDataSet, TimeSpan.FromMinutes(5)); // Adjust TTL based on data freshness needs
        return freshDataSet;
    }
}

// Helper to generate a unique cache key
private string GenerateCacheKey(string procedureName, SqlParameter[] parameters)
{
    var paramString = string.Join("|", parameters.Select(p => $"{p.ParameterName}:{p.Value}"));
    return $"Report_{procedureName}_{paramString}";
}

Key Points #

• Cache TTL: Set a time-to-live that balances freshness and performance (e.g., 5 minutes for non-critical reports, shorter for frequently updated data).
• Distributed Locks: Use these instead of in-memory locks if your app runs on multiple servers to avoid duplicate executions across instances.
• Cache Invalidation: Invalidate relevant cache keys when underlying data changes to ensure users get fresh results.

Add simple frontend logic to prevent repeated requests:

• Disable the refresh button or report load button until the current request completes
• Add a debounce function to ignore rapid consecutive refresh attempts

Don't overlook the root cause—if your stored procedures are slow, fix their performance first:

• Analyze execution plans to identify missing indexes
• Split large procedures into smaller, focused ones
• Avoid long-running transactions that block other queries

内容的提问来源于stack exchange，提问作者b15