【C#】程序和sql速度对比

有空再把缺失的完善

普通的增删改查

增

表中有三个字段，已经有100多万条数据，每次插入10万条数据 时间单位：秒

秒 Dapper批量Model插入时间：40.6165513,Dapper单条Model插入时间：95.9492972,Dapper单条sql插入时间：91.0191095,原生单条sql插入时间：90.5096905

秒 Dapper批量Model插入时间：40.4729053,Dapper单条Model插入时间：99.0270506,Dapper单条sql插入时间：92.7325932,原生单条sql插入时间：91.9713511

秒 Dapper批量Model插入时间：41.0260065,Dapper单条Model插入时间：95.8173737,Dapper单条sql插入时间：90.9012987,原生单条sql插入时间：90.2153092

秒 Dapper批量Model插入时间：41.5675273,Dapper单条Model插入时间：101.9446306,Dapper单条sql插入时间：94.4770289,原生单条sql插入时间：92.9758614

测试时间代码

List<gk_area> lst = new List<gk_area>();for (int i = 0; i < 100000; i++){gk_area tmp = new gk_area();tmp.gk_AreaId = Guid.NewGuid().ToString("N");tmp.gk_AreaName = "测试：" + tmp.gk_AreaId;tmp.gk_DelFlag = "0";lst.Add(tmp);}if (true){Stopwatch stopwatch = new Stopwatch();stopwatch.Start(); //  开始监视代码运行时间new DapperHelper().InsertBulk<gk_area>(lst);         //  需要测试的代码 ....stopwatch.Stop(); //  停止监视TimeSpan timespan = stopwatch.Elapsed; //  获取当前实例测量得出的总时间double seconds = timespan.TotalSeconds;  //  总秒数timeStr += "秒 Dapper批量Model插入时间：" + seconds + ",";}lst = new List<gk_area>();for (int i = 0; i < 100000; i++){gk_area tmp = new gk_area();tmp.gk_AreaId = Guid.NewGuid().ToString("N");tmp.gk_AreaName = "测试：" + tmp.gk_AreaId;tmp.gk_DelFlag = "0";lst.Add(tmp);}if (true){Stopwatch stopwatch = new Stopwatch();stopwatch.Start(); //  开始监视代码运行时间//  需要测试的代码 ....foreach (var item in lst){new DapperHelper().Insert<gk_area>(item);}stopwatch.Stop(); //  停止监视TimeSpan timespan = stopwatch.Elapsed; //  获取当前实例测量得出的总时间double seconds = timespan.TotalSeconds;  //  总秒数timeStr += "Dapper单条Model插入时间：" + seconds + ",";}lst = new List<gk_area>();for (int i = 0; i < 100000; i++){gk_area tmp = new gk_area();tmp.gk_AreaId = Guid.NewGuid().ToString("N");tmp.gk_AreaName = "测试：" + tmp.gk_AreaId;tmp.gk_DelFlag = "0";lst.Add(tmp);}if (true){Stopwatch stopwatch = new Stopwatch();stopwatch.Start(); //  开始监视代码运行时间//  需要测试的代码 ....foreach (var item in lst){new DapperHelper().ExcuteNonQuery("insert into gk_area (gk_AreaId,gk_AreaName) values ('" + item.gk_AreaId + "','" + item.gk_AreaName + "')");}stopwatch.Stop(); //  停止监视TimeSpan timespan = stopwatch.Elapsed; //  获取当前实例测量得出的总时间double seconds = timespan.TotalSeconds;  //  总秒数timeStr += "Dapper单条sql插入时间：" + seconds + ",";}lst = new List<gk_area>();for (int i = 0; i < 100000; i++){gk_area tmp = new gk_area();tmp.gk_AreaId = Guid.NewGuid().ToString("N");tmp.gk_AreaName = "测试：" + tmp.gk_AreaId;tmp.gk_DelFlag = "0";lst.Add(tmp);}if (true){Stopwatch stopwatch = new Stopwatch();stopwatch.Start(); //  开始监视代码运行时间//  需要测试的代码 ....foreach (var item in lst){new DapperHelper().ExecuteNonQuery1("insert into gk_area (gk_AreaId,gk_AreaName) values ('" + item.gk_AreaId + "','" + item.gk_AreaName + "')");}stopwatch.Stop(); //  停止监视TimeSpan timespan = stopwatch.Elapsed; //  获取当前实例测量得出的总时间double seconds = timespan.TotalSeconds;  //  总秒数timeStr += "原始单条sql插入时间：" + seconds + ",";}

插库方法

#region +Insert 新增Model/// <summary>/// 新增 /// </summary>/// <returns></returns>public int Insert<T>(T t) where T : class, new(){int result = 0;try{using (MySqlConnection con = new MySqlConnection(connection)){string strSqlText = GetSqlInsert<T>(t);result = con.Execute(strSqlText, t);}}catch (Exception ex){WriteLog(ex.ToString());}return result;}private string GetSqlInsert<T>(T t){Type type = t.GetType();PropertyInfo[] properties = type.GetProperties();string sqlText = "INSERT INTO {0} ({1}) VALUES ({2})";StringBuilder fileds = new StringBuilder();StringBuilder values = new StringBuilder();foreach (var proper in properties){if (!proper.CustomAttributes.Any(x => x.AttributeType == typeof(AutoKeyAttribute))&& !proper.CustomAttributes.Any(x => x.AttributeType == typeof(DefaultAttribute))){fileds.Append(proper.Name + ",");values.Append("@" + proper.Name + ",");}}sqlText = string.Format(sqlText, type.Name, fileds.ToString().TrimEnd(','), values.ToString().TrimEnd(','));return sqlText;}#endregion#region +InsertBulk 批量新增/// <summary>/// 批量新增 /// </summary>/// <returns></returns>public int InsertBulk<T>(List<T> list) where T : class, new(){int result = 0;try{using (MySqlConnection con = new MySqlConnection(connection)){string strSqlText = GetSqlInsertBulk<T>(list);result = con.Execute(strSqlText, list);}}catch (Exception ex){WriteLog(ex.ToString());}return result;}private string GetSqlInsertBulk<T>(List<T> list){return list.Count() > 0 ? GetSqlInsert(list[0]) : "";}#endregion/// <summary>/// 原始sql/// </summary>/// <param name="sql"></param>/// <returns></returns>public int ExecuteNonQuery1(string sql){using (MySqlConnection conn = new MySqlConnection(connection)){MySqlCommand cmd = new MySqlCommand();if (conn.State != ConnectionState.Open)conn.Open();cmd.Connection = conn;cmd.CommandText = sql;cmd.CommandType = CommandType.Text;return cmd.ExecuteNonQuery();}}

删

改

查

表中存在3029949（300万）条数据 时间单位：秒

反射字段查询时间：22.651388,Dapper查询时间：9.7697742,原生查询1时间：13.8903755,原生查询2时间：16.1955305

反射字段查询时间：21.3172684,Dapper查询时间：9.7593554,原生查询1时间：14.3009033,原生查询2时间：15.085577

反射字段查询时间：21.8932407,Dapper查询时间：8.3832515,原生查询1时间：13.4945138,原生查询2时间：17.7821992

反射字段查询时间：21.9676583,Dapper查询时间：9.3833032,原生查询1时间：13.5412978,原生查询2时间：15.5048999

测试时间代码

if (true){Stopwatch stopwatch = new Stopwatch();stopwatch.Start(); //  开始监视代码运行时间//  需要测试的代码 ....var test1 = new DapperHelper().FindToList<gk_area>("select * from gk_area");stopwatch.Stop(); //  停止监视TimeSpan timespan = stopwatch.Elapsed; //  获取当前实例测量得出的总时间double seconds = timespan.TotalSeconds;  //  总秒数timeStr += "反射字段查询时间：" + seconds + ",";}if (true){Stopwatch stopwatch = new Stopwatch();stopwatch.Start(); //  开始监视代码运行时间//  需要测试的代码 ....var test2 = new DapperHelper().QueryToList<gk_area>("select * from gk_area");stopwatch.Stop(); //  停止监视TimeSpan timespan = stopwatch.Elapsed; //  获取当前实例测量得出的总时间double seconds = timespan.TotalSeconds;  //  总秒数timeStr += "Dapper查询时间：" + seconds + ",";}if (true){Stopwatch stopwatch = new Stopwatch();stopwatch.Start(); //  开始监视代码运行时间//  需要测试的代码 ....var test3 = new DapperHelper().GetDataTable("select * from gk_area");stopwatch.Stop(); //  停止监视TimeSpan timespan = stopwatch.Elapsed; //  获取当前实例测量得出的总时间double seconds = timespan.TotalSeconds;  //  总秒数timeStr += "原始查询时间：" + seconds + ",";}if (true){Stopwatch stopwatch = new Stopwatch();stopwatch.Start(); //  开始监视代码运行时间//  需要测试的代码 ....var test3 = new DapperHelper().ExecuteDataTable("select * from gk_area");stopwatch.Stop(); //  停止监视TimeSpan timespan = stopwatch.Elapsed; //  获取当前实例测量得出的总时间double seconds = timespan.TotalSeconds;  //  总秒数timeStr += "原始查询2时间：" + seconds + ",";}

查库方法

#region +FindToList  查询数据集合 反射字段查询 废弃/// <summary>/// 同步查询数据集合/// </summary>/// <typeparam name="T">实体</typeparam>/// <param name="sql">sql语句</param>/// <param name="param">参数</param>/// <param name="flag">true存储过程，false sql语句</param>/// <returns>t</returns>public List<T> FindToList<T>(string sql, DynamicParameters param = null, bool flag = false) where T : class, new(){IDataReader dataReader = null;using (MySqlConnection con = new MySqlConnection(connection)){if (flag){dataReader = con.ExecuteReader(sql, param, null, null, CommandType.StoredProcedure);}else{dataReader = con.ExecuteReader(sql, param, null, null, CommandType.Text);}if (dataReader == null || !dataReader.Read()) return null;Type type = typeof(T);List<T> tlist = new List<T>();do{T t = new T();foreach (var item in type.GetProperties()){for (int i = 0; i < dataReader.FieldCount; i++){//属性名与查询出来的列名比较if (item.Name.ToLower() != dataReader.GetName(i).ToLower()) continue;var kvalue = dataReader[item.Name];if (kvalue == DBNull.Value) continue;item.SetValue(t, kvalue, null);break;}}if (tlist != null) tlist.Add(t);} while (dataReader.Read());return tlist;}}#endregion#region +QueryToList Dapper 查询数据集合/// <summary>/// 同步查询数据集合/// </summary>/// <typeparam name="T">实体</typeparam>/// <param name="sql">sql语句</param>/// <param name="param">参数</param>/// <param name="flag">true存储过程，false sql语句</param>/// <returns>t</returns>public List<T> QueryToList<T>(string sql, DynamicParameters param = null, bool flag = false) where T : class, new(){List<T> list = null;try{using (MySqlConnection con = new MySqlConnection(connection)){if (flag){list = con.Query<T>(sql, param, null, true, null, CommandType.StoredProcedure) as List<T>;}else{list = con.Query<T>(sql, param, null, true, null, CommandType.Text) as List<T>;}}}catch (Exception ex){WriteLog(ex.ToString());}return list;}#endregion/// <summary>/// 原始查询1/// </summary>/// <param name="sql"></param>/// <returns></returns>public DataTable GetDataTable(string sql){//创建一个MySqlCommand对象 MySqlCommand cmd = new MySqlCommand();//创建一个MySqlConnection对象 MySqlConnection conn = new MySqlConnection(connection);try{if (conn.State != ConnectionState.Open)conn.Open();cmd.Connection = conn;cmd.CommandText = sql;cmd.CommandType = CommandType.Text;MySqlDataAdapter adapter = new MySqlDataAdapter();adapter.SelectCommand = cmd;DataTable ds = new DataTable();adapter.Fill(ds);//清除参数 cmd.Parameters.Clear();conn.Close();return ds;}catch (Exception e){throw e;}}/// <summary>/// 原始查询2/// </summary>/// <param name="SQLString"></param>/// <returns></returns>public DataTable ExecuteDataTable(string SQLString){using (MySqlConnection conn = new MySqlConnection(connection)){DataSet ds = new DataSet();try{conn.Open();MySqlDataAdapter command = new MySqlDataAdapter(SQLString, conn);command.Fill(ds, "ds");}catch (MySql.Data.MySqlClient.MySqlException ex){throw new Exception(ex.Message);}return ds.Tables[0];}}

简单业务处理

计算

我运行了两个案例.

SELECT TOP 100 PERCENT cI1,cI2,cI3 FROM [dbo].[ARL_Mesur] WITH (NOLOCK)  WHERE [dbo].[ARL_Mesur].[cWhen] > @cWhen0;foreach (var Ena in cAllOfThem){// this is the line that I move inside SQL server to see what change on speedvar results = Ena.CI1 + Ena.CI2 + Ena.CI3;sbRender.Append(results);sbRender.Append(Ena.CI2);sbRender.Append(Ena.CI3);}

VS

SELECT TOP 100 PERCENT (cI1+cI2+cI3) as cI1,cI2,cI3 FROM [dbo].[ARL_Mesur] WITH (NOLOCK)  WHERE [dbo].[ARL_Mesur].[cWhen] > @cWhen0;foreach (var Ena in cAllOfThem){sbRender.Append(Ena.CI1);sbRender.Append(Ena.CI2);sbRender.Append(Ena.CI3);}

结果表明速度接近相同.- 所有参数都是double - 读取被优化,我根本没有额外的读取,只是将处理从一个部分移动到另一个部分.

在165,766行,以下是一些结果:

Start  0ms  +0msc# processing  2005ms  +2005ms
sql processing  4011ms  +2006msStart  0ms  +0msc# processing  2247ms  +2247ms
sql processing  4514ms  +2267msStart  0ms  +0msc# processing  2018ms  +2018ms
sql processing  3946ms  +1928msStart  0ms  +0ms
c# processing  2043ms  +2043ms
sql processing  4133ms  +2090ms

如判断赋值if和case

分组统计

排序

引用

SQL代码比C#代码更快吗？
C# Mysql Dapper和原生sql 插入和查询速度比较