C#使用SqlSuger如何通过优化代码，提高查询效率

前言

本文使用Asp.Net Core和SqlSuger框架技术实现举例。

当数据量特别大时，如何通过优化代码，进而优化sql，从而提高sql查询效率。
sqlsuger示例代码：

DateTime startTime = new DateTime(2024,10,12,12,00,00);
DateTime endTime = new DateTime(2024,10,13,12,00,00);
//在IOLOg中查询，根据时间、状态、序列号集筛选出对应的数据
var IOLogList = _BaseRepository.Context.Queryable<IOLog>()
    .WhereIF(startTime != null, i => i.StartTime >= startTime)
    .WhereIF(endTime != null, i => i.EndTime <= endTime)
    .Where(i => i.State == 1)
    .Where(i => SqNoList.Contains(i.SqNo))
    .ToList();

生成的sql：

SELECT [Id],[seq_no],[qty],[group_code],[process_code],[station_code],[start_time],[end_time],[p_date],[state] 
FROM [IOLog] WITH(NOLOCK)   
WHERE ( [start_time] >= @StartTime0 )  AND ( [State] = @State1 )  AND  ([seq_no] IN ('SEQ202410140003-001','SEQ202410140003-002','SEQ202410140003-003','SEQ202410140003-004','SEQ202410140003-005','SEQ202410140003-006','SEQ202410140003-007','SEQ202410140003-008','SEQ202410140003-009','SEQ202410140003-010','SEQ202410140003-011'))  
--{"@StartTime0":"2024-10-12T12:00:00.000","@State1":1}

一、显示SqlSuger生成的Sql

将sqlsuger语法生成的sql通过命令窗口打印出来，方便使用数据库查询，检查校验sql。

public class DbOperation
{
	public static void AddDb(IConfiguration Configuration)
	{
		//获取配置文件字符串
	    string connStr = Configuration.GetConnectionString("dbConnection");
	    //0表示MySql，1为SqlServer
	    int dbType = Convert.ToInt32(Configuration.GetConnectionString("dbType"));
	
	    SugarIocServices.AddSqlSugar(new List<IocConfig>() {
	    	//默认数据库
	           new IocConfig() {
	            ConfigId = "0",//默认db
	            ConnectionString = connStr,
	            DbType = (IocDbType)dbType,
	            IsAutoCloseConnection = true
	        },
	        //其他数据库
	           new IocConfig() {
	            ConfigId = "1",
	            ConnectionString = "替换成你的字符串",
	            DbType = IocDbType.MySql,
	            IsAutoCloseConnection = true
	        }
	           //...增加其他数据库
	        });
	    SugarIocServices.ConfigurationSugar(db =>
	    {
	        //db0数据过滤
	        FilterData(0);
	
	        #region db0，默认数据库显示
	        //全局去锁
	        db.GetConnectionScope(0).CurrentConnectionConfig.MoreSettings = new ConnMoreSettings()
	        {
	            IsWithNoLockQuery = true
	        };
	
	        db.GetConnectionScope(0).Aop.OnLogExecuting = (sql, pars) =>
	        {
	            var param = db.GetConnectionScope(0).Utilities.SerializeObject(pars.ToDictionary(it => it.ParameterName, it => it.Value));
	
	            logger.Info($"【sql语句】{sql}--，{param}\n");
	        };
	
	        db.GetConnectionScope(0).Aop.OnError = (e) =>
	        {
	            logger.Error(e, $"执行SQL出错：{e.Message}");
	        };
	        //SQL执行完
	        db.GetConnectionScope(0).Aop.OnLogExecuted = (sql, pars) =>
	        {
	            //执行完了可以输出SQL执行时间 (OnLogExecutedDelegate) 
	        };
	        #endregion
	
	        #region 其他数据库读取显示设置
	        
	        #endregion
	    });
	
	}
}

在Program.cs里挂载AddDb()

//初始化db
var builder = WebApplication.CreateBuilder(args);
DbOperation.AddDb(builder.Configuration);

注意：sql注入

SqlSuger支持的数据库类型：

//SqlSuger支持的数据库类型
public enum IocDbType
{
    MySql = 0,
    SqlServer = 1,
    Sqlite = 2,
    Oracle = 3,
    PostgreSQL = 4,
    Dm = 5,
    Kdbndp = 6,
    Oscar = 7,
    MySqlConnector = 8,
    Access = 9,
    OpenGauss = 10,
    QuestDB = 11,
    HG = 12,
    ClickHouse = 13,
    GBase = 14,
    Odbc = 0xF,
    Custom = 900
}

二、优化SqlSuger

1.根据业务场景，增加查询条件

假设IOLog表中有字段year,格式为yyyy，如果需要查询2024-10-12的数据，可新增条件year = ‘2024’

DateTime startTime = new DateTime(2024,10,12,12,00,00);
DateTime endTime = new DateTime(2024,10,13,12,00,00);
//在IOLOg中查询，根据时间、状态、序列号集筛选出对应的数据
var IOLogList = _BaseRepository.Context.Queryable<IOLog>()
	.Where(i =>i.Year == "2024" && i.State == 1)
    .WhereIF(startTime != null, i => i.StartTime >= startTime)
    .WhereIF(endTime != null, i => i.EndTime <= endTime)
    .Where(i => SqNoList.Contains(i.SqNo))
    .ToList();

2.SqlSuger语法优化

1、通过模板字符串，进行字符串、时间比较，减少sql参数替换，如$“input_time >= ‘{param}’”；
2、在sqlsuger语法中，Contains转为in，这不会影响sql效率。

//在IOLOg中查询，根据时间、状态、序列号集筛选出对应的数据
var IOLogList = _BaseRepository.Context.Queryable<IOLog>()
    .WhereIF(startTime != null, $"start_time >= '{startTime}'")
    .WhereIF(endTime != null, $"end_time <= '{endTime}'")
    .Where($"state = 1")
    .Where(i => SqNoList.Contains(i.SqNo))
    .ToList();

生成的sql：

SELECT [Id],[seq_no],[qty],[group_code],[process_code],[station_code],[start_time],[end_time],[p_date],[state] 
FROM [IOLog] WITH(NOLOCK)   
WHERE start_time >= '2024/10/12 12:00:00'   AND state = 1   AND  ([seq_no] IN ('SEQ202410140003-001','SEQ202410140003-002','SEQ202410140003-003','SEQ202410140003-004','SEQ202410140003-005','SEQ202410140003-006','SEQ202410140003-007','SEQ202410140003-008','SEQ202410140003-009','SEQ202410140003-010','SEQ202410140003-011')) -- ，{}

3.使用纯Sql执行

1、通过sql查询返回DataTable数据结构解析。

var startTime = "2024/10/12 12:00:00"
var sql = "SELECT * FROM IOLog WITH(NOLOCK) WHERE start_time >= '"+ startTime +"'   AND state = 1 AND  seq_no IN ('SEQ202410140003-001','SEQ202410140003-002','SEQ202410140003-003','SEQ202410140003-004','SEQ202410140003-005','SEQ202410140003-006','SEQ202410140003-007','SEQ202410140003-008','SEQ202410140003-009','SEQ202410140003-010','SEQ202410140003-011')";

var SQLResult = _BaseRepository.Context.Ado.GetDataTable(sql);
foreach (DataRow perRow in SQLResult.Rows)
{
    var item = new IOLog
    {
    	Id = perRow["Id"].ToString(),
    	SeqNo = perRow["seq_no"].ToString(),
    	Qty = perRow["qty"].ToString(),
    	GroupCode = perRow["group_code"].ToString(),
    	ProcessCode = perRow["process_code"].ToString(),
    	StationCode = perRow["station_code"].ToString(),
    	StartTime = perRow["start_time"].ToString(),
    	EndTime= perRow["end_time"].ToString(),
        PDate = perRow["p_date"].ToString(),
        State = perRow["state"].ToString(),
    });
}

2、通过sql查询返回dynamic动态数据结构解析。

var startTime = "2024/10/12 12:00:00"
var sql = "SELECT * FROM IOLog WITH(NOLOCK) WHERE start_time >= '"+ startTime +"'   AND state = 1 AND  seq_no IN ('SEQ202410140003-001','SEQ202410140003-002','SEQ202410140003-003','SEQ202410140003-004','SEQ202410140003-005','SEQ202410140003-006','SEQ202410140003-007','SEQ202410140003-008','SEQ202410140003-009','SEQ202410140003-010','SEQ202410140003-011')";

var result = Context.SqlQueryable<dynamic>(sql)
    .ToList();
foreach (var io in result)
{
    var item = new IOLog
    {
    	Id = io.Id.ToString(),
    	SeqNo = io.seq_no.ToString(),
    	Qty = io.qty.ToString(),
    	GroupCode = io.group_code.ToString(),
    	ProcessCode = io.process_code.ToString(),
    	StationCode = io.station_code.ToString(),
    	StartTime = io.start_time.ToString(),
    	EndTime= io.end_time.ToString(),
        PDate = io.p_date.ToString(),
        State = io.state.ToString(),
    });
}

总结

sqlsuger实现sql语句，在where条件里的处理，主要是通过参数替换，当查询数据量过大时，可通过上述 二-2 描述的优化。