突然心血来潮测试了一下Freesql和SqlSugar的批量插入和批量更新性能，一搜测评一大堆，但是没找到自己想要的结果，自己动手测试一下基本的批量插入和批量更新性能。

环境比较拉跨，N年前的电脑了

I5-5300U

16G内存

国产固态

FreeSql.Provider.MySql 版本 3.2.833

SqlSugarCore.MySqlConnector 版本 5.1.4.92

废话不多说直接贴代码

  1 using FreeSql;
  2 using FreeSql.DataAnnotations;
  3 using SqlSugar;
  4 
  5 namespace ConsoleApp1
  6 {
  7 
  8     [SugarTable("student_sqlsugar")]
  9     [Table(Name = "student_freesql")]
 10     public class Student
 11     {
 12 
 13         [SugarColumn(IsPrimaryKey = true)]
 14         [Column(IsPrimary = true)]
 15         public int Id { get; set; }
 16 
 17         public string? Name { get; set; }
 18 
 19         public int Age { get; set; }
 20 
 21         public string? Number { get; set; }
 22 
 23         public int Sex { get; set; }
 24         
 25         public string? Location { get; set; }
 26 
 27         [SugarColumn(ColumnName = "class_id")]
 28         [Column(Name = "class_id")]
 29         public int ClassId { get; set; }
 30     }
 31 
 32     public class SqlSugarTest<T> where T : class, new()
 33     {
 34         private readonly string connStr = "Data Source=127.0.0.1;Port=3306;User ID=root;Password=zaq12wsxCDE#; Initial Catalog=test;Charset=utf8;SslMode=none;Min Pool Size=20;Max Pool Size=20";
 35         private readonly DbType dbType = DbType.MySql;
 36         private readonly SqlSugarScope db;
 37 
 38         public SqlSugarTest()
 39         {
 40             if (db == null)
 41             {
 42                 db = new SqlSugarScope(new ConnectionConfig
 43                 {
 44                     ConnectionString = connStr,
 45                     DbType = dbType,
 46                     IsAutoCloseConnection = true,
 47                     //ConfigureExternalServices = new ConfigureExternalServices
 48                     //{
 49                     //    EntityNameService = (type, en) =>
 50                     //    {
 51                     //        en.DbTableName = en.DbTableName?.ToLower();
 52                     //    },
 53                     //    EntityService = (type, en) =>
 54                     //    {
 55                     //        en.DbTableName = en.DbTableName?.ToLower();
 56                     //        en.DbColumnName = en.DbColumnName?.ToLower();
 57                     //    }
 58                     //}
 59                 });
 60             }
 61 
 62             db.DbMaintenance.CreateDatabase();
 63             db.CodeFirst.InitTables<T>();
 64         }
 65 
 66         public IEnumerable<T> GetList(int limit = 0)
 67         {
 68             if (limit == 0)
 69                 return db.Queryable<T>().ToList();
 70             else
 71                 return db.Queryable<T>().Take(limit).ToList();
 72         }
 73 
 74         public IEnumerable<T> GetPagerList(int index, int limit)
 75         {
 76             return db.Queryable<T>().ToPageList(index, limit);
 77         }
 78 
 79         public int Insert(T en)
 80         {
 81             return db.Insertable<T>(en).ExecuteCommand();
 82         }
 83 
 84         public int InsertList(IEnumerable<T> ens)
 85         {
 86             int count = ens.Count();
 87             int pz = 512, p = 0;
 88             int res = 0;
 89 
 90             if (count > pz)
 91             {
 92                 while (pz * p < count)
 93                 {
 94                     if (pz * (p + 1) > count)
 95                     {
 96                         res += db.Insertable<T>(ens.Skip(pz * p).Take(count - pz * p).ToArray()).ExecuteCommand();
 97                     }
 98                     else
 99                     {
100                         res += db.Insertable<T>(ens.Skip(pz * p).Take(pz).ToArray()).ExecuteCommand();
101                     }
102                     p++;
103                 }
104             }
105             else
106             {
107                 res += db.Insertable<T>(ens.ToArray()).ExecuteCommand();
108             }
109             return res;
110         }
111 
112         public int Update(T en)
113         {
114             return db.Updateable<T>(en).ExecuteCommand();
115 
116         }
117 
118         public int UpdateList(IEnumerable<T> ens)
119         {
120             int count = ens.Count();
121             int pz = 512, p = 0;
122             int res = 0;
123 
124             if (count > pz)
125             {
126                 while (pz * p < count)
127                 {
128                     if (pz * (p + 1) > count)
129                     {
130                         res += db.Updateable<T>(ens.Skip(pz * p).Take(count - pz * p).ToArray()).ExecuteCommand();
131                     }
132                     else
133                     {
134                         res += db.Updateable<T>(ens.Skip(pz * p).Take(pz).ToArray()).ExecuteCommand();
135                     }
136                     p++;
137                 }
138             }
139             else
140             {
141                 res += db.Updateable<T>(ens.ToArray()).ExecuteCommand();
142             }
143             return res;
144         }
145 
146         public int Delete(T en)
147         {
148             return db.Deleteable<T>(en).ExecuteCommand();
149         }
150 
151         public int Delete(IEnumerable<T> ens)
152         {
153             int count = ens.Count();
154             int pz = 512, p = 0;
155             int res = 0;
156 
157             if (count > pz)
158             {
159                 while (pz * p < count)
160                 {
161                     if (pz * (p + 1) > count)
162                     {
163                         res += db.Deleteable<T>(ens.Skip(pz * p).Take(count - pz * p).ToArray()).ExecuteCommand();
164                     }
165                     else
166                     {
167                         res += db.Deleteable<T>(ens.Skip(pz * p).Take(pz).ToArray()).ExecuteCommand();
168                     }
169                     p++;
170                 }
171             }
172             else
173             {
174                 res += db.Deleteable<T>(ens.ToArray()).ExecuteCommand();
175             }
176             return res;
177         }
178     }
179 
180     public class FreeSqlTest<T> where T : class, new()
181     {
182         private readonly string connStr = "Data Source=127.0.0.1;Port=3306;User ID=root;Password=zaq12wsxCDE#; Initial Catalog=test;Charset=utf8;SslMode=none;Min Pool Size=20;Max Pool Size=20";
183         private readonly DataType dbType = DataType.MySql;
184         private IFreeSql db;
185 
186 
187         public FreeSqlTest()
188         {
189             db = new FreeSqlBuilder()
190                 .UseConnectionString(dbType, connStr)
191                 .UseAutoSyncStructure(true)
192                 //.UseNameConvert(FreeSql.Internal.NameConvertType.ToLower)
193                 .UseLazyLoading(true)
194                 .Build();
195         }
196 
197         public IEnumerable<T> GetList(int limit)
198         {
199             if (limit == 0)
200                 return db.Queryable<T>().ToList();
201             else
202                 return db.Queryable<T>().Take(limit).ToList();
203         }
204 
205         public IEnumerable<T> GetPagerList(int index, int limit)
206         {
207             return db.Queryable<T>().Page(index, limit).ToList();
208         }
209 
210         public int Insert(T entity)
211         {
212             return db.Insert<T>(entity).ExecuteAffrows();
213         }
214 
215         public int InsertList(IEnumerable<T> ens)
216         {
217             int count = ens.Count();
218             int pz = 512, p = 0;
219             int res = 0;
220 
221             if (count > pz)
222             {
223                 while (pz * p < count)
224                 {
225                     if (pz * (p + 1) > count)
226                     {
227                         res += db.Insert<T>(ens.Skip(pz * p).Take(count - pz * p).ToArray()).ExecuteAffrows();
228                     }
229                     else
230                     {
231                         res += db.Insert<T>(ens.Skip(pz * p).Take(pz).ToArray()).ExecuteAffrows();
232                     }
233                     p++;
234                 }
235             }
236             else
237             {
238                 res += db.Insert<T>(ens.ToArray()).ExecuteAffrows();
239             }
240             return res;
241         }
242 
243         public int Update(T en)
244         {
245             return db.Update<T>().SetSource(en).ExecuteAffrows();
246         }
247 
248         public int UpdateList(IEnumerable<T> ens)
249         {
250             int pz = 512, p = 0;
251             int count = ens.Count();
252             int res = 0;
253 
254             if (count > pz)
255             {
256                 while (pz * p < count)
257                 {
258                     if (pz * (p + 1) > count)
259                     {
260                         res += db.Update<T>().SetSource(ens.Skip(pz * p).Take(count - pz * p).ToArray()).ExecuteAffrows();
261                     }
262                     else
263                     {
264                         res += db.Update<T>().SetSource(ens.Skip(pz * p).Take(pz).ToArray()).ExecuteAffrows();
265                     }
266                     p++;
267                 }
268 
269             }
270             else
271             {
272                 res += db.Update<T>(ens.ToArray()).ExecuteAffrows();
273             }
274             return res;
275         }
276 
277         public int Delete(T en)
278         {
279             return db.Delete<T>(en).ExecuteAffrows();
280         }
281 
282         public int Delete(IEnumerable<T> ens)
283         {
284             int count = ens.Count();
285             int pz = 512, p = 0;
286             int res = 0;
287 
288             if (count > pz)
289             {
290                 while (pz * p < count)
291                 {
292                     if (pz * (p + 1) > count)
293                     {
294                         res += db.Delete<T>(ens.Skip(pz * p).Take(count - pz * p).ToArray()).ExecuteAffrows();
295                     }
296                     else
297                     {
298                         res += db.Delete<T>(ens.Skip(pz * p).Take(pz).ToArray()).ExecuteAffrows();
299                     }
300                     p++;
301                 }
302             }
303             else
304             {
305                 res += db.Delete<T>(ens.ToArray()).ExecuteAffrows();
306             }
307             return res;
308         }
309     }
310 
311 }

下面是调用的代码

 1 using ConsoleApp1;
 2 using System.Diagnostics;
 3 
 4 
 5 Stopwatch sw = new Stopwatch();
 6 
 7 sw.Restart();
 8 
 9 var list = new List<Student>();
10 int listLength = 65536;
11 
12 for (int i = 1; i < listLength; i++)
13 {
14     list.Add(new Student
15     {
16         Id = i,
17         Name = $"test{(i / 128)}{(i % 128)}",
18         Age = (((i % 16) < 8) ? (i % 16 + 8) : (i % 16)),
19         Sex = ((i % 16) % 2),
20         Location = $"{i / 128}{(i % 128)}号",
21         Number = $"{i}",
22         ClassId = (i % 64),
23     });
24 }
25 
26 
27 SqlSugarTest<Student> sqlSugarTest = new SqlSugarTest<Student>();
28 FreeSqlTest<Student> freeSqlTest = new FreeSqlTest<Student>();
29 
30 sqlSugarTest.Insert(list.First());
31 
32 freeSqlTest.Insert(list.First());
33 
34 
35 sw.Stop();
36 //Console.WriteLine($"prepare data: {sw.Elapsed.TotalMilliseconds}ms");
37 
38 Console.WriteLine("insert start");
39 sw.Restart();
40 freeSqlTest.InsertList(list.Skip(1));
41 sw.Stop();
42 Console.WriteLine($"freeSql insert: {sw.Elapsed.TotalMilliseconds}ms");
43 
44 sw.Restart();
45 sqlSugarTest.InsertList(list.Skip(1));
46 sw.Stop();
47 Console.WriteLine($"sqlSugar insert: {sw.Elapsed.TotalMilliseconds}ms");
48 
49 Console.WriteLine("update start");
50 
51 for (int i = 0; i < 3; i++)
52 {
53     Console.WriteLine($"times {i}");
54     sw.Restart();
55     freeSqlTest.UpdateList(list.Skip(1));
56     sw.Stop();
57     Console.WriteLine($"freeSql update: {sw.Elapsed.TotalMilliseconds}ms");
58 
59     sw.Restart();
60     sqlSugarTest.UpdateList(list.Skip(1));
61     sw.Stop();
62     Console.WriteLine($"sqlSugar update: {sw.Elapsed.TotalMilliseconds}ms");
63 
64 }
65 
66 Console.WriteLine("complete");

看着好像按每页512条往里插入和修改貌似SqlSugar性能更胜一筹

 当开启表名和字段名自动转小写之后的测试结果是这样的

昨天晚上做了多次测试，开启转小写的时候SqlSugar略有优势，但是很微弱，但是确实也是稍微有点优势，上图贴的是几乎不相上下的时候；

如果不开启转小写的话，SqlSugar速度要优于Freesql将近一倍，不知道是不是我的写法导致的，还望看到的大佬指正一下用法。

当然真正的项目中肯定不会这样用，而且如果是用mysql，大概率是会开启大小写转换的，如果是SqlServer的话，按我用MySql这个测试的结论的话，应该是SqlSugar更有优势一些，如果是MySql，不开启大小写自动转换的话，单说插入和更新，SqlSugar更值得推荐，开启大小写转换的话，选哪个都行。

当然查询的性能我没有测试，不晓得哪个查询性能更好一些。

 

最后还要感谢文章作者的这篇文章，地址如下

https://www.cnblogs.com/AprilBlank/p/13696852.html