做一下mssql的连接，和赋值的测试

最新推荐文章于 2023-04-20 20:34:23 发布

lijt1987

最新推荐文章于 2023-04-20 20:34:23 发布

阅读量847

点赞数 1

分类专栏： C# 文章标签： string 测试 c random float sql

本文链接：https://blog.csdn.net/lijt1987/article/details/5760809

版权

C# 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

机器环境：

前言

最近实体框架很流行，不过缺点也多，我列一下：

1.不支持sql2000

2.增删改很麻烦，增加，带多个外键的，要查数据库，修改与删除也要查，这样对性能影响大打折扣，而且编码也麻烦，至少比代码生成麻烦

3.linq to entity中，有些语句是不支持的，如intArr.Contains等方法，必需用e-sql query.Where("it.ID IN {1,2,3,4}")才行

4.要支持外键值的话，要用ef2.0，即是.net4.0的平台，.net4.0太庞大了，近两三年应该也用不起来。

所以我还是用普通的ado.net算了。时间很紧迫，项目很乱，而且我还只有一个星期对住一堆超乱的代码进行重构，还不知做不做得来。所以为了解决这个难题，要一步一步验证自己未能确定的问题。

现在我的思路是，代码效率+性能+维护方便

如果牺牲10%-20%的性能，能增加1-2倍的生产效率的话，那我很乐意去做。

ef1.0，好像在100万条数据的情况下，不能满足吧。这种查法，不过现在放弃使用，就暂不做相关的测试了。

正题

最近在研究属于自己的架构，所以有必要做一下这方面的性能测试

主要有两个问题要搞清楚

1.在DAL层里，是否需要共用一个Connection?

2.在赋值过程中，能否通过一个规范来实现接口，然后拿数据，像DataReader赋值的操作，用循环就搞店，不用一行一行属性来赋值。

所以就有两个问题

1.用连接池开关连接与不用连接池开关连接，时间上有多大差异？

2.用索引器的方式赋值，与普通手写代码赋值，时间上又有多大差异？

于是模拟最实际的环境（我想是挺实际的）就用以下在代码来做测试：

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using System.Data; using System.IO; namespace ConsoleApplication52 { class Program { static void Main(string[] args) { string poolStr = "data source=127.0.0.1//sql2005;initial catalog=examjuhai;user id=sa;password=juhaisoft;min pool size=50;max pool size=200;pooling=true;"; string notPoolStr = "data source=127.0.0.1//sql2005;initial catalog=examjuhai;user id=sa;password=juhaisoft;pooling=false"; int baseCount = 1000; StreamWriter log = new StreamWriter("E://testlog.txt", false, Encoding.Default); log.WriteLine("测试用连接池与不用连接池开关连接所用时间:"); log.WriteLine("------------------------------------------------"); log.WriteLine("次数/t连接池/t不用连接池"); for (int i = 1; i <= 20; i++) { int count = baseCount * i; TimeSpan time = TestConn(count, poolStr); TimeSpan time2 = TestConn(count, notPoolStr); log.WriteLine(count + "/t" + time + "/t" + (time2 == TimeSpan.MaxValue ? "连接出错" : time2.ToString())); log.Flush(); } log.WriteLine("------------------------------------------------"); log.WriteLine("------------------------------------------------"); log.WriteLine("------------------------------------------------"); log.WriteLine("测试字符串判断赋值与直接赋值时间比较"); log.WriteLine("------------------------------------------------"); log.WriteLine("次数/t判断赋值/t赋值 "); for (int i = 1; i <= 100; i++) { int count = baseCount * i; TimeSpan time = TestAssign1(count); TimeSpan time2 = TestAssign2(count); log.WriteLine(count + "/t" + time + "/t" + time2); log.Flush(); } log.WriteLine("------------------------------------------------"); log.Close(); } /// <summary> /// 测试开关连接所用时间 /// </summary> /// <param name="count">开关次数</param> /// <param name="connStr">连接字符串</param> /// <returns>所用时间</returns> private static TimeSpan TestConn(int count, string connStr) { //计算数数所用时间 DateTime now = DateTime.Now; for (int i = 0; i < count; i++) { } TimeSpan t1 = DateTime.Now - now; for (int i = 0; i < count; i++) { SqlConnection conn = new SqlConnection(connStr); try { conn.Open(); conn.Close(); } catch { return TimeSpan.MaxValue; } finally { conn.Dispose(); } } TimeSpan t2 = DateTime.Now - now; //减去数数的时间才是正确的时间 return t2 - t1; } /// <summary> /// 用判断的方式赋值 /// </summary> /// <returns></returns> public static TimeSpan TestAssign1(int dataCount) { DataTable dt = PreparedTable(dataCount); DateTime now = DateTime.Now; List<Model> list = new List<Model>(); foreach (DataRow row in dt.Rows) { Model m = new Model(); foreach (DataColumn col in dt.Columns) { m[col.ColumnName] = row[col]; } list.Add(m); } return DateTime.Now - now; } /// <summary> /// 赋值 /// </summary> /// <returns></returns> public static TimeSpan TestAssign2(int dataCount) { DataTable dt = PreparedTable(dataCount); DateTime now = DateTime.Now; List<Model> list = new List<Model>(); foreach (DataRow row in dt.Rows) { Model m = new Model(); m.ID = Convert.ToInt32(row["ID"]); m.C1abcdefghijkl = Convert.ToString(row["C1abcdefghijkl"]); m.C2abcdefghijkl = Convert.ToSingle(row["C2abcdefghijkl"]); m.C3abcdefghijkl = Convert.ToDouble(row["C3abcdefghijkl"]); m.C4abcdefghijkl = Convert.ToDecimal(row["C4abcdefghijkl"]); m.C5abcdefghijkl = Convert.ToInt64(row["C5abcdefghijkl"]); m.C6abcdefghijkl = Convert.ToDateTime(row["C6abcdefghijkl"]); m.C7abcdefghijkl = Convert.ToString(row["C7abcdefghijkl"]); list.Add(m); } return DateTime.Now - now; } /// <summary> /// 正常的表，一般有八个列 /// </summary> /// <param name="dataRowCount">数据的数量</param> static DataTable PreparedTable(int dataRowCount) { DataTable dt = new DataTable(); dt.Columns.Add("ID", typeof(int)); dt.Columns.Add("C1abcdefghijkl", typeof(string)); dt.Columns.Add("C2abcdefghijkl", typeof(float)); dt.Columns.Add("C3abcdefghijkl", typeof(double)); dt.Columns.Add("C4abcdefghijkl", typeof(decimal)); dt.Columns.Add("C5abcdefghijkl", typeof(long)); dt.Columns.Add("C6abcdefghijkl", typeof(DateTime)); dt.Columns.Add("C7abcdefghijkl", typeof(string)); for (int i = 0; i < dataRowCount; i++) { DataRow row = dt.NewRow(); row[0] = rnd.Next(); row[1] = rnd.Next().ToString(); row[2] = (float)rnd.NextDouble(); row[3] = rnd.NextDouble(); row[4] = rnd.Next(); row[5] = rnd.Next(); row[6] = DateTime.Now.AddSeconds(rnd.Next(int.MinValue, int.MaxValue)); row[7] = DateTime.Now.Ticks.ToString() + "_" + rnd.Next() + "_" + rnd.NextDouble(); dt.Rows.Add(row); } return dt; } static Random rnd = new Random(); /// <summary> /// 测试字符串判断赋值比较 /// </summary> class Model { public int ID { get; set; } public string C1abcdefghijkl { get; set; } public float C2abcdefghijkl { get; set; } public double C3abcdefghijkl { get; set; } public decimal C4abcdefghijkl { get; set; } public long C5abcdefghijkl { get; set; } public DateTime? C6abcdefghijkl { get; set; } public string C7abcdefghijkl { get; set; } public object this[string propertyName] { get { switch (propertyName) { case "ID": return ID; case "C1abcdefghijkl": return C1abcdefghijkl; case "C2abcdefghijkl": return C2abcdefghijkl; case "C3abcdefghijkl": return C3abcdefghijkl; case "C4abcdefghijkl": return C4abcdefghijkl; case "C5abcdefghijkl": return C5abcdefghijkl; case "C6abcdefghijkl": return C6abcdefghijkl; case "C7abcdefghijkl": return C7abcdefghijkl; default: return null; } } set { //value不为空时 if (value != null && !(value is DBNull)) { switch (propertyName) { case "ID": ID = Convert.ToInt32(value); break; case "C1abcdefghijkl": C1abcdefghijkl = Convert.ToString(value); break; case "C2abcdefghijkl": C2abcdefghijkl = Convert.ToSingle(value); break; case "C3abcdefghijkl": C3abcdefghijkl = Convert.ToDouble(value); break; case "C4abcdefghijkl": C4abcdefghijkl = Convert.ToDecimal(value); break; case "C5abcdefghijkl": C5abcdefghijkl = Convert.ToInt64(value); break; case "C6abcdefghijkl": C6abcdefghijkl = Convert.ToDateTime(value); break; case "C7abcdefghijkl": C7abcdefghijkl = Convert.ToString(value); break; } } } } } } }

是一个控制台应用程序，连接sql2005数据库（开始向sql2005转了，不过现在还在用sql2000，客户要求，没办法的事）

结果如下：

测试用连接池与不用连接池开关连接所用时间:

------------------------------------------------

次数	连接池	不用连接池
1000	00:00:00.0781250	00:00:03.0156250
2000	00:00:00	00:00:05.9531250
3000	00:00:00.0156250	连接出错
4000	00:00:00.0312500	连接出错
5000	00:00:00.0312500	连接出错
6000	00:00:00.0468750	连接出错
7000	00:00:00.0468750	连接出错
8000	00:00:00.0468750	连接出错
9000	00:00:00.0468750	连接出错
10000	00:00:00.0625000	连接出错
11000	00:00:00.0625000	连接出错
12000	00:00:00.0625000	连接出错
13000	00:00:00.0781250	连接出错
14000	00:00:00.1093750	连接出错
15000	00:00:00.0937500	连接出错
16000	00:00:00.0937500	连接出错
17000	00:00:00.0937500	连接出错
18000	00:00:00.0937500	连接出错
19000	00:00:00.0937500	连接出错
20000	00:00:00.1093750	连接出错

------------------------------------------------

测试字符串判断赋值与直接赋值时间比较

------------------------------------------------

次数	判断赋值	赋值
1000	00:00:00	00:00:00
2000	00:00:00	00:00:00.0156250
3000	00:00:00.0156250	00:00:00
4000	00:00:00.0156250	00:00:00.0156250
5000	00:00:00	00:00:00
6000	00:00:00.0312500	00:00:00.0156250
7000	00:00:00.0156250	00:00:00.0156250
8000	00:00:00.0156250	00:00:00.0156250
9000	00:00:00.0312500	00:00:00.0312500
10000	00:00:00.0156250	00:00:00.0468750
11000	00:00:00.0312500	00:00:00.0312500
12000	00:00:00.0312500	00:00:00.0312500
13000	00:00:00.0312500	00:00:00.0156250
14000	00:00:00.0312500	00:00:00.0156250
15000	00:00:00.0312500	00:00:00.0312500
16000	00:00:00.0312500	00:00:00.0468750
17000	00:00:00.0312500	00:00:00.0312500
18000	00:00:00.0937500	00:00:00.0625000
19000	00:00:00.0781250	00:00:00.0468750
20000	00:00:00.0625000	00:00:00.0625000
21000	00:00:00.0781250	00:00:00.0468750
22000	00:00:00.0781250	00:00:00.0312500
23000	00:00:00.0468750	00:00:00.0781250
24000	00:00:00.0625000	00:00:00.0937500
25000	00:00:00.0468750	00:00:00.0625000
26000	00:00:00.0937500	00:00:00.0781250
27000	00:00:00.0781250	00:00:00.0937500
28000	00:00:00.0781250	00:00:00.0781250
29000	00:00:00.0781250	00:00:00.0937500
30000	00:00:00.0937500	00:00:00.0937500
31000	00:00:00.1562500	00:00:00.0625000
32000	00:00:00.1250000	00:00:00.0781250
33000	00:00:00.0937500	00:00:00.1093750
34000	00:00:00.0937500	00:00:00.0781250
35000	00:00:00.1562500	00:00:00.0937500
36000	00:00:00.1093750	00:00:00.0937500
37000	00:00:00.1250000	00:00:00.0781250
38000	00:00:00.1093750	00:00:00.1093750
39000	00:00:00.1406250	00:00:00.1250000
40000	00:00:00.1718750	00:00:00.0781250
41000	00:00:00.1250000	00:00:00.0937500
42000	00:00:00.1250000	00:00:00.1718750
43000	00:00:00.1250000	00:00:00.1718750
44000	00:00:00.1250000	00:00:00.1718750
45000	00:00:00.1250000	00:00:00.1093750
46000	00:00:00.2031250	00:00:00.1093750
47000	00:00:00.2031250	00:00:00.1093750
48000	00:00:00.1562500	00:00:00.1406250
49000	00:00:00.1718750	00:00:00.1093750
50000	00:00:00.2187500	00:00:00.1250000
51000	00:00:00.1406250	00:00:00.2031250
52000	00:00:00.1406250	00:00:00.1250000
53000	00:00:00.1562500	00:00:00.1562500
54000	00:00:00.1718750	00:00:00.1250000
55000	00:00:00.1875000	00:00:00.1250000
56000	00:00:00.1562500	00:00:00.1562500
57000	00:00:00.1562500	00:00:00.1718750
58000	00:00:00.1718750	00:00:00.1250000
59000	00:00:00.1875000	00:00:00.2031250
60000	00:00:00.1875000	00:00:00.1562500
61000	00:00:00.1718750	00:00:00.1875000
62000	00:00:00.2031250	00:00:00.1562500
63000	00:00:00.2343750	00:00:00.1875000
64000	00:00:00.2031250	00:00:00.2031250
65000	00:00:00.2656250	00:00:00.1562500
66000	00:00:00.2031250	00:00:00.1718750
67000	00:00:00.2031250	00:00:00.2187500
68000	00:00:00.2812500	00:00:00.1562500
69000	00:00:00.2187500	00:00:00.2031250
70000	00:00:00.2187500	00:00:00.2500000
71000	00:00:00.2343750	00:00:00.2812500
72000	00:00:00.2500000	00:00:00.2031250
73000	00:00:00.2812500	00:00:00.2812500
74000	00:00:00.2343750	00:00:00.2031250
75000	00:00:00.2500000	00:00:00.1875000
76000	00:00:00.2656250	00:00:00.2656250
77000	00:00:00.2968750	00:00:00.2187500
78000	00:00:00.2343750	00:00:00.2656250
79000	00:00:00.2812500	00:00:00.2031250
80000	00:00:00.2812500	00:00:00.2968750
81000	00:00:00.3281250	00:00:00.2343750
82000	00:00:00.3437500	00:00:00.2187500
83000	00:00:00.2968750	00:00:00.2031250
84000	00:00:00.2968750	00:00:00.2343750
85000	00:00:00.2500000	00:00:00.2968750
86000	00:00:00.3281250	00:00:00.2500000
87000	00:00:00.3281250	00:00:00.2812500
88000	00:00:00.3125000	00:00:00.2187500
89000	00:00:00.3125000	00:00:00.2187500
90000	00:00:00.3437500	00:00:00.2812500
91000	00:00:00.3281250	00:00:00.2656250
92000	00:00:00.4062500	00:00:00.3593750
93000	00:00:00.3281250	00:00:00.2656250
94000	00:00:00.2968750	00:00:00.2812500
95000	00:00:00.3906250	00:00:00.3437500
96000	00:00:00.3906250	00:00:00.2656250
97000	00:00:00.3593750	00:00:00.3125000
98000	00:00:00.3437500	00:00:00.2968750
99000	00:00:00.3906250	00:00:00.2656250
100000	00:00:00.3281250	00:00:00.2656250

------------------------------------------------

就上面来看，我可以得出结论了：

1.DAL不用共享连接，因为开关连接的时间太短可以忽略不计

2.用代码生成的方式生成索引器的代码，然后从dr拿值时，直接while(reader.Read()),再加一循环来拿值就行

因为对比的时间，相差也不大，那1ms，10万次的赋值，不算什么。

lijt1987

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
做一下mssql的连接，和赋值的测试

最近实体框架很流行，不过缺点也多，我列一下：1.不支持sql20002.增删改很麻烦，增加，带多个外键的，要查数据库，修改与删除也要查，这样对性能影响大打折扣，而且编码也麻烦，至少比代码生成麻烦3.linq to entity中，有些语句是不支持的，如intArr.Contains等方法，必需用e-sql query.Where("it.ID IN {1,2,3,4}")才行4.要支持外键值的话，要用ef2.0，即是.net4.0的平台，.net4.0太庞大了，近两三年应该也用不起来。所以我还是用普通的ad
复制链接

扫一扫