在C#项目中处理大数据量查询时，性能问题往往会影响系统的响应速度和用户体验，需要从多个层面采取优化措施，降低资源消耗，提升查询效率。
一、数据库查询层面优化
1. 仅查询需要的字段
避免使用SELECT *语句，只查询业务需要的字段，减少数据传输量和内存占用。下面是使用ADO.NET执行指定字段查询的示例：
using System;
using System.Data;
using System.Data.SqlClient;
public class QueryHelper
{
private string _connStr = "Server=127.0.0.1;Database=TestDB;Uid=sa;Pwd=123456;";
// 只查询用户ID和名称，不查询其他无关字段
public DataTable QueryUserBasicInfo(int minId, int maxId)
{
string sql = "SELECT UserId, UserName FROM UserInfo WHERE UserId BETWEEN @MinId AND @MaxId";
using (SqlConnection conn = new SqlConnection(_connStr))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@MinId", minId);
cmd.Parameters.AddWithValue("@MaxId", maxId);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
return dt;
}
}
}
}
2. 合理使用分页查询
一次性查询全量大数据会导致内存溢出和查询超时，采用分页方式分批获取数据是常用方案。下面是使用OFFSET FETCH进行分页的示例：
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
public class PagedQueryHelper
{
private string _connStr = "Server=127.0.0.1;Database=TestDB;Uid=sa;Pwd=123456;";
// 分页查询用户数据，pageIndex从0开始，pageSize为每页条数
public List<UserInfo> QueryUserByPage(int pageIndex, int pageSize)
{
List<UserInfo> result = new List<UserInfo>();
string sql = @"SELECT UserId, UserName, CreateTime
FROM UserInfo
ORDER BY UserId
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";
using (SqlConnection conn = new SqlConnection(_connStr))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@Offset", pageIndex * pageSize);
cmd.Parameters.AddWithValue("@PageSize", pageSize);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
result.Add(new UserInfo
{
UserId = reader.GetInt32(0),
UserName = reader.GetString(1),
CreateTime = reader.GetDateTime(2)
});
}
}
}
}
return result;
}
}
public class UserInfo
{
public int UserId { get; set; }
public string UserName { get; set; }
public DateTime CreateTime { get; set; }
}
二、代码执行层面优化
1. 使用异步查询避免阻塞
大数据量查询耗时较长，使用异步方法可以避免阻塞主线程，提升接口的并发处理能力。下面是异步查询的示例：
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
public class AsyncQueryHelper
{
private string _connStr = "Server=127.0.0.1;Database=TestDB;Uid=sa;Pwd=123456;";
// 异步查询用户列表
public async Task<List<UserInfo>> QueryUserAsync(int minId, int maxId)
{
List<UserInfo> result = new List<UserInfo>();
string sql = "SELECT UserId, UserName FROM UserInfo WHERE UserId BETWEEN @MinId AND @MaxId";
using (SqlConnection conn = new SqlConnection(_connStr))
{
await conn.OpenAsync();
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@MinId", minId);
cmd.Parameters.AddWithValue("@MaxId", maxId);
using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
result.Add(new UserInfo
{
UserId = reader.GetInt32(0),
UserName = reader.GetString(1)
});
}
}
}
}
return result;
}
}
2. 避免不必要的内存分配
处理大量数据时，频繁创建对象会增加垃圾回收压力，可以使用复用对象、使用值类型等方式减少内存开销。下面是使用结构体替代类减少内存分配的示例：
using System;
using System.Collections.Generic;
public struct UserBasicStruct
{
public int UserId { get; set; }
public string UserName { get; set; }
}
public class MemoryOptHelper
{
// 使用结构体存储基础数据，减少堆内存分配
public List<UserBasicStruct> ProcessUserData(List<UserBasicStruct> sourceData)
{
List<UserBasicStruct> result = new List<UserBasicStruct>(sourceData.Count);
foreach (var item in sourceData)
{
// 直接复用数据，不做额外对象创建
if (item.UserId > 0)
{
result.Add(item);
}
}
return result;
}
}
三、缓存与索引配合优化
1. 热点数据使用内存缓存
对于查询频繁、变动不频繁的大数据量结果，可以使用内存缓存减少数据库查询次数。下面是使用MemoryCache的示例：
using System;
using System.Collections.Generic;
using Microsoft.Extensions.Caching.Memory;
public class CacheQueryHelper
{
private readonly IMemoryCache _cache;
private readonly QueryHelper _queryHelper;
public CacheQueryHelper(IMemoryCache cache, QueryHelper queryHelper)
{
_cache = cache;
_queryHelper = queryHelper;
}
// 查询用户数据，优先从缓存获取
public DataTable GetUserBasicInfoWithCache(int minId, int maxId)
{
string cacheKey = $"UserBasic_{minId}_{maxId}";
if (!_cache.TryGetValue(cacheKey, out DataTable result))
{
result = _queryHelper.QueryUserBasicInfo(minId, maxId);
// 缓存有效期设置为5分钟
_cache.Set(cacheKey, result, TimeSpan.FromMinutes(5));
}
return result;
}
}
2. 配合数据库索引提升查询速度
在C#中编写查询语句时，要配合数据库表的索引设计，避免索引失效。比如查询条件中不要对字段使用函数、不要使用隐式类型转换，确保查询能够命中索引，减少全表扫描的概率。
四、其他实用技巧
- 批量操作替代单条操作：如果需要插入或更新大量数据，使用批量插入方法，比如SqlBulkCopy，比单条执行效率高很多。
- 及时释放资源：使用using语句确保数据库连接、DataReader等资源及时释放，避免资源泄露导致的性能下降。
- 预编译查询语句：对于重复执行的查询，使用参数化预编译语句，减少SQL语句编译的开销。