如何降低MVC应用数据加载时间 实现按姓氏搜索功能

1. 核心逻辑改造，服务端过滤避免全量拉取 #

你现在性能问题的核心是没有把搜索条件传递到数据库层，每次都拉取全表数据再在内存过滤，优化后直接在SQL层面完成过滤，大幅减少数据传输量和处理耗时。

修改后控制器代码如下： #

[HttpGet]
public List<PensionMembers> PensionMembers(string search = null)
{
    var ind = new List<PensionMembers>();
    using (var connection = new SqlConnection(_configuration.GetConnectionString("PENSION")))
    {
        // 动态拼接查询条件，使用参数化查询避免SQL注入
        var baseQuery = @"select * from member 
                        INNER JOIN Fund ON Fund.Fund_Number = Member.Fund";
        var sqlParams = new List<SqlParameter>();
        if (!string.IsNullOrEmpty(search))
        {
            // 精确匹配可直接用=，模糊搜索替换为LIKE @Surname，参数值前后加%即可
            baseQuery += " WHERE Surname = @Surname";
            sqlParams.Add(new SqlParameter("@Surname", search));
            // 如果只需要FirstOrDefault结果，直接加TOP 1，数据库查到第一条就返回，不用遍历全表
            // baseQuery = "SELECT TOP 1 " + baseQuery.Substring(6);
        }
        connection.Open();
        SqlCommand cmd = new SqlCommand(baseQuery, connection);
        if(sqlParams.Any())
        {
            cmd.Parameters.AddRange(sqlParams.ToArray());
        }
        SqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            PensionMembers pm = new PensionMembers()
            {
                Employee_number = reader["Employee_number"].ToString(),
                First_Name = reader["First_Name"].ToString(),
                Other_Names = reader["Other_Names"].ToString(),
                Surname = reader["Surname"].ToString(),
                Name = reader["Name"].ToString() //基金名称
            };
            ind.Add(pm);
            // 如果仅需FirstOrDefault结果的话，读到第一条直接跳出循环即可
            // break;
        }
    }
    return ind;
}

2. 新增数据库索引提升查询速度 #

执行以下SQL创建索引，避免全表扫描，查询速度可以提升10倍以上：

-- Surname搜索字段索引，包含常用查询字段避免回表
CREATE NONCLUSTERED INDEX IX_Member_Surname ON member (Surname)
INCLUDE (Employee_number, First_Name, Other_Names, Fund);
-- 关联表索引优化关联查询速度
CREATE NONCLUSTERED INDEX IX_Fund_FundNumber ON Fund (Fund_Number)
INCLUDE (Name);

3. 低更新频率数据可选缓存方案 #

如果你的养老成员数据更新频率很低，可以直接把全量数据缓存到内存，不用每次请求都查数据库：

// 先在控制器构造函数注入IMemoryCache
private readonly IMemoryCache _cache;
public YourControllerName(IMemoryCache cache)
{
    _cache = cache;
}

[HttpGet]
public List<PensionMembers> PensionMembers(string search = null)
{
    const string cacheKey = "PensionMembersAll";
    // 先从缓存取数据
    if(!_cache.TryGetValue(cacheKey, out List<PensionMembers> allMembers))
    {
        allMembers = new List<PensionMembers>();
        using (var connection = new SqlConnection(_configuration.GetConnectionString("PENSION")))
        {
            var Query1 = "select * from member INNER JOIN Fund ON Fund.Fund_Number = Member.Fund";
            connection.Open();
            SqlCommand cmd = new SqlCommand(Query1, connection);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                PensionMembers pm = new PensionMembers()
                {
                    Employee_number = reader["Employee_number"].ToString(),
                    First_Name = reader["First_Name"].ToString(),
                    Other_Names = reader["Other_Names"].ToString(),
                    Surname = reader["Surname"].ToString(),
                    Name = reader["Name"].ToString()
                };
                allMembers.Add(pm);
            }
        }
        // 缓存1小时，可根据业务更新频率调整过期时间
        _cache.Set(cacheKey, allMembers, TimeSpan.FromHours(1));
    }
    // 直接在内存过滤
    if(!string.IsNullOrEmpty(search))
    {
        return allMembers.Where(x => x.Surname.Equals(search, StringComparison.OrdinalIgnoreCase)).Take(1).ToList();
    }
    return allMembers;
}

4. 视图层适配 #

确保表单提交参数正确绑定：

<form method="get" action="@Url.Action("PensionMembers")">
    @Html.TextBox("search")
    <div class="input-group-btn">
        <button class="btn btn-primary" type="submit">
            Search
        </button>
    </div>
</form>

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