突然心血来潮测试了一下Freesql和SqlSugar的批量插入和批量更新性能，一搜测评一大堆，但是没找到自己想要的结果，自己动手测试一下基本的批量插入和批量更新性能。
废话不多说直接贴代码
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性能更胜一筹
然而令我没想到的是当开启表名和字段名自动转小写之后的测试结果是这样的
昨天晚上做了多次测试，开启转小写的时候Freesql的性能略优于SqlSugar一丢丢，优势很微弱，但是确实也是稍微有点优势；
如果不开启转小写的话，SqlSugar速度要优于Freesql将近一倍，不知道是不是我的写法导致的，还望看到的大佬指正一下用法。
当然真正的项目中肯定不会这样用，而且如果是用mysql，大概率是会开启大小写转换的，如果是SqlServer的话，按我用MySql这个测试的结论的话，应该是SqlSugar更有优势一些，如果是MySql，不开启大小写自动转换的话，单说插入和更新，SqlSugar更值得推荐，开启大小写转换的话，FreeSql有微弱优势。
当然查询的性能我没有测试，不晓得哪个查询性能更好一些。
最后还要感谢文章作者的这篇文章，地址如下
https://www.cnblogs.com/AprilBlank/p/13696852.html
标签：count,有感,pz,Freesql,db,int,ens,res,SqlSugar From： https://www.cnblogs.com/CrazyRisk/p/18543201