DataAdapter.Fill性能缓慢，如何用DataReader替代并优化查询速度？

差异原因 #

• 参数嗅探差异：SQL Server会根据首次执行的参数生成执行计划并缓存，若程序传入的参数与SSMS测试时的参数分布差异大，缓存的计划会导致执行变慢；而SSMS每次可能用不同参数或重新编译计划，不会受低效缓存计划影响。
• 会话设置不一致：SSMS默认启用ARITHABORT、ANSI_NULLS等选项，程序连接的会话设置若不同，数据库会生成不同的执行计划，甚至触发低效计划。
• 数据加载的额外开销：DataSet/DataTable需要构建内存中的数据结构、关系映射等，而SSMS仅做数据展示，无这些对象初始化和序列化的开销；DataReader.Load本质也是填充DataTable，开销和DataAdapter接近。
• 连接初始化延迟：程序中连接可能从连接池新建或初始化，而SSMS保持长期连接，省去了连接建立的时间成本。

解决办法 #

1. 解决参数嗅探问题 #

• 强制重新编译：在存储过程的查询语句末尾添加OPTION (RECOMPILE)，让SQL Server每次执行都生成适配当前参数的计划，适合参数值分布波动大的场景。
• 指定优化参数：使用OPTIMIZE FOR (@Param = '目标值')，让计划针对常用的参数值生成，平衡多数场景的性能。
• 局部变量隔离：在存储过程内部将输入参数赋值给局部变量，用局部变量执行查询，避免SQL Server嗅探外部参数。

2. 统一会话设置 #

在程序执行存储过程前，先执行与SSMS一致的会话配置，确保执行环境相同：

// 在打开连接后、创建执行命令前添加
using (var setupCmd = cn.CreateCommand())
{
    setupCmd.CommandText = @"
SET ARITHABORT ON;
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;";
    setupCmd.ExecuteNonQuery();
}

3. 优化数据读取逻辑 #

• 改用轻量级实体映射：如果不需要DataSet的更新、关系等功能，直接用DataReader映射到自定义实体类，减少DataTable的内存开销：

public List<T> ExecuteReader<T>(string connName, string sp, List<SqlParameter> parameters, Func<SqlDataReader, T> mapper)
{
    var result = new List<T>();
    using (SqlConnection cn = GetConnection(connName))
    {
        cn.Open();
        // 先执行会话设置
        using (var setupCmd = cn.CreateCommand())
        {
            setupCmd.CommandText = @"SET ARITHABORT ON; SET ANSI_NULLS ON;";
            setupCmd.ExecuteNonQuery();
        }

        using (SqlCommand cmd = cn.CreateCommand())
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = sp;
            cmd.Parameters.AddRange(parameters.ToArray());
            
            using (var reader = cmd.ExecuteReader())
            {
                do
                {
                    while (reader.Read())
                    {
                        result.Add(mapper(reader));
                    }
                } while (reader.NextResult()); // 处理多个结果集
            }
        }
    }
    return result;
}

// 调用示例
var data = ExecuteReader<MyEntity>("conn", "sp_GetData", paramList, reader => new MyEntity
{
    Id = reader.GetInt32(reader.GetOrdinal("Id")),
    Name = reader.GetString(reader.GetOrdinal("Name"))
});

• 避免不必要的类型转换：确保参数和读取字段的类型与数据库定义一致，减少隐式转换开销。

4. 优化连接与执行细节 #

• 提前打开连接：手动调用cn.Open()，避免DataAdapter.Fill自动打开连接时的额外延迟。
• 检查参数定义：确保传入的SqlParameter的SqlDbType、Size与存储过程参数完全匹配，避免因类型不匹配导致的隐式转换或计划异常。
• 验证连接池配置：确认连接字符串中Pooling=true（默认启用），避免频繁创建新连接的开销。

优化后的示例代码 #

public DataSet ExecuteQuery(string connName, string sp, List<SqlParameter> parameters)
{
    DataSet ds = new DataSet();
    using (SqlConnection cn = GetConnection(connName))
    {
        cn.Open();
        
        // 统一会话设置
        using (var setupCmd = cn.CreateCommand())
        {
            setupCmd.CommandText = @"
SET ARITHABORT ON;
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET QUOTED_IDENTIFIER ON;
SET NUMERIC_ROUNDABORT OFF;";
            setupCmd.ExecuteNonQuery();
        }

        using (SqlCommand cmd = cn.CreateCommand())
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = sp;
            cmd.CommandTimeout = 600;
            
            // 正确添加参数，避免隐式类型问题
            foreach (var param in parameters)
            {
                var sqlParam = new SqlParameter(param.ParameterName, param.SqlDbType, param.Size)
                {
                    Value = param.Value ?? DBNull.Value
                };
                cmd.Parameters.Add(sqlParam);
            }

            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                da.Fill(ds);
            }
        }
    }
    return ds;
}

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