如何为SQL Server存储过程设置并行执行或提升执行效率？

Hey there! Let's tackle this scenario where you're pulling data from a 1.5M+ row SQL Server table in your ASP.NET MVC 5 project—great call on sticking to parameterized queries and stored procedures to block SQL injection, that's a critical foundation here.

Below are practical, performance-focused solutions tailored to your large dataset:

1. Never Pull All Data at Once—Implement Pagination #

Fetching 1.5 million rows in one go will crush your app's memory and network bandwidth. Use pagination to retrieve only the data your users need right now. Two solid approaches:

Option 1: OFFSET/FETCH (Simple for Most Cases) #

Here's a stored procedure example for basic pagination:

CREATE PROCEDURE GetPagedRecords
    @PageNumber INT = 1,
    @PageSize INT = 25
AS
BEGIN
    SET NOCOUNT ON;
    -- Only select columns you actually need (avoid SELECT *)
    SELECT Id, ColumnA, ColumnB, CreatedDate
    FROM YourLargeTable
    ORDER BY Id -- Stable sort key is mandatory for consistent pagination
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END

Corresponding C# code in your service/repository:

using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["YourDb"].ConnectionString))
{
    SqlCommand cmd = new SqlCommand("GetPagedRecords", connection);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@PageNumber", currentPage);
    cmd.Parameters.AddWithValue("@PageSize", pageSize);

    connection.Open();
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        var records = new List<YourModel>();
        while (reader.Read())
        {
            records.Add(new YourModel
            {
                Id = reader.GetInt32(0),
                ColumnA = reader.GetString(1),
                ColumnB = reader.GetDateTime(2)
            });
        }
        return records;
    }
}

Option 2: Keyset Pagination (Better for Very Large Datasets) #

For datasets where OFFSET starts to slow down at high page numbers, use a "last seen value" to filter results:

CREATE PROCEDURE GetKeysetPagedRecords
    @LastSeenId INT,
    @PageSize INT = 25
AS
BEGIN
    SET NOCOUNT ON;
    SELECT Id, ColumnA, ColumnB, CreatedDate
    FROM YourLargeTable
    WHERE Id > @LastSeenId -- Use the last ID from the previous page
    ORDER BY Id
    FETCH NEXT @PageSize ROWS ONLY;
END

2. Add Targeted Indexes #

Without proper indexes, even pagination queries will trigger full table scans. Create non-clustered indexes tailored to your query patterns:

• Include filter/sort columns in the index key
• Add required output columns with INCLUDE to avoid expensive key lookups

Example index for the above pagination queries:

CREATE NONCLUSTERED INDEX IX_YourLargeTable_Id_IncludeColumns
ON YourLargeTable (Id)
INCLUDE (ColumnA, ColumnB, CreatedDate);

3. Use Async/Await for Better Concurrency #

In ASP.NET MVC 5, async queries prevent thread blocking and improve your app's ability to handle concurrent requests. Update your C# code to use async methods:

public async Task<List<YourModel>> GetPagedRecordsAsync(int pageNumber, int pageSize)
{
    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["YourDb"].ConnectionString))
    {
        await connection.OpenAsync();
        SqlCommand cmd = new SqlCommand("GetPagedRecords", connection);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@PageNumber", pageNumber);
        cmd.Parameters.AddWithValue("@PageSize", pageSize);

        using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
        {
            var records = new List<YourModel>();
            while (await reader.ReadAsync())
            {
                records.Add(new YourModel
                {
                    Id = reader.GetInt32(0),
                    ColumnA = reader.GetString(1),
                    ColumnB = reader.GetDateTime(2)
                });
            }
            return records;
        }
    }
}

Then call it from your controller with async/await:

public async Task<ActionResult> Index(int page = 1)
{
    var records = await _yourRepository.GetPagedRecordsAsync(page, 25);
    return View(records);
}

4. For Bulk Operations: Stream Data Instead of Loading to Memory #

If you need to process/export all 1.5M rows, don't load everything into a list. Use SqlDataReader to stream records one by one, processing each row immediately:

public async Task ProcessBulkDataAsync()
{
    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["YourDb"].ConnectionString))
    {
        await connection.OpenAsync();
        SqlCommand cmd = new SqlCommand("SELECT Id, ColumnA FROM YourLargeTable", connection);
        
        using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync())
            {
                // Process single row here (e.g., write to a file, send to another service)
                int id = reader.GetInt32(0);
                string value = reader.GetString(1);
                await ProcessSingleRecordAsync(id, value);
            }
        }
    }
}

5. Monitor and Tune Query Performance #

• Use SQL Server Management Studio's Execution Plan to spot bottlenecks like full table scans or key lookups.
• Enable SET STATISTICS TIME, IO ON in your query to measure logical reads and execution time.
• Consider updating statistics with UPDATE STATISTICS YourLargeTable to ensure SQL Server uses optimal query plans.

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