C# Mysql Dapper和原生sql 插入和查询速度比较
1、表中有三个字段，已经有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(); } }
2、表中存在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]; } }
