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

来源：互联网发布：软件开发的发展前景编辑：程序博客网时间：2024/05/16 17:54

机器环境：

前言

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

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，客户要求，没办法的事）

结果如下：

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

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

次数连接池不用连接池100000:00:00.078125000:00:03.0156250200000:00:0000:00:05.9531250300000:00:00.0156250连接出错400000:00:00.0312500连接出错500000:00:00.0312500连接出错600000:00:00.0468750连接出错700000:00:00.0468750连接出错800000:00:00.0468750连接出错900000:00:00.0468750连接出错1000000:00:00.0625000连接出错1100000:00:00.0625000连接出错1200000:00:00.0625000连接出错1300000:00:00.0781250连接出错1400000:00:00.1093750连接出错1500000:00:00.0937500连接出错1600000:00:00.0937500连接出错1700000:00:00.0937500连接出错1800000:00:00.0937500连接出错1900000:00:00.0937500连接出错2000000:00:00.1093750连接出错

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

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

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

次数判断赋值赋值100000:00:0000:00:00200000:00:0000:00:00.0156250300000:00:00.015625000:00:00400000:00:00.015625000:00:00.0156250500000:00:0000:00:00600000:00:00.031250000:00:00.0156250700000:00:00.015625000:00:00.0156250800000:00:00.015625000:00:00.0156250900000:00:00.031250000:00:00.03125001000000:00:00.015625000:00:00.04687501100000:00:00.031250000:00:00.03125001200000:00:00.031250000:00:00.03125001300000:00:00.031250000:00:00.01562501400000:00:00.031250000:00:00.01562501500000:00:00.031250000:00:00.03125001600000:00:00.031250000:00:00.04687501700000:00:00.031250000:00:00.03125001800000:00:00.093750000:00:00.06250001900000:00:00.078125000:00:00.04687502000000:00:00.062500000:00:00.06250002100000:00:00.078125000:00:00.04687502200000:00:00.078125000:00:00.03125002300000:00:00.046875000:00:00.07812502400000:00:00.062500000:00:00.09375002500000:00:00.046875000:00:00.06250002600000:00:00.093750000:00:00.07812502700000:00:00.078125000:00:00.09375002800000:00:00.078125000:00:00.07812502900000:00:00.078125000:00:00.09375003000000:00:00.093750000:00:00.09375003100000:00:00.156250000:00:00.06250003200000:00:00.125000000:00:00.07812503300000:00:00.093750000:00:00.10937503400000:00:00.093750000:00:00.07812503500000:00:00.156250000:00:00.09375003600000:00:00.109375000:00:00.09375003700000:00:00.125000000:00:00.07812503800000:00:00.109375000:00:00.10937503900000:00:00.140625000:00:00.12500004000000:00:00.171875000:00:00.07812504100000:00:00.125000000:00:00.09375004200000:00:00.125000000:00:00.17187504300000:00:00.125000000:00:00.17187504400000:00:00.125000000:00:00.17187504500000:00:00.125000000:00:00.10937504600000:00:00.203125000:00:00.10937504700000:00:00.203125000:00:00.10937504800000:00:00.156250000:00:00.14062504900000:00:00.171875000:00:00.10937505000000:00:00.218750000:00:00.12500005100000:00:00.140625000:00:00.20312505200000:00:00.140625000:00:00.12500005300000:00:00.156250000:00:00.15625005400000:00:00.171875000:00:00.12500005500000:00:00.187500000:00:00.12500005600000:00:00.156250000:00:00.15625005700000:00:00.156250000:00:00.17187505800000:00:00.171875000:00:00.12500005900000:00:00.187500000:00:00.20312506000000:00:00.187500000:00:00.15625006100000:00:00.171875000:00:00.18750006200000:00:00.203125000:00:00.15625006300000:00:00.234375000:00:00.18750006400000:00:00.203125000:00:00.20312506500000:00:00.265625000:00:00.15625006600000:00:00.203125000:00:00.17187506700000:00:00.203125000:00:00.21875006800000:00:00.281250000:00:00.15625006900000:00:00.218750000:00:00.20312507000000:00:00.218750000:00:00.25000007100000:00:00.234375000:00:00.28125007200000:00:00.250000000:00:00.20312507300000:00:00.281250000:00:00.28125007400000:00:00.234375000:00:00.20312507500000:00:00.250000000:00:00.18750007600000:00:00.265625000:00:00.26562507700000:00:00.296875000:00:00.21875007800000:00:00.234375000:00:00.26562507900000:00:00.281250000:00:00.20312508000000:00:00.281250000:00:00.29687508100000:00:00.328125000:00:00.23437508200000:00:00.343750000:00:00.21875008300000:00:00.296875000:00:00.20312508400000:00:00.296875000:00:00.23437508500000:00:00.250000000:00:00.29687508600000:00:00.328125000:00:00.25000008700000:00:00.328125000:00:00.28125008800000:00:00.312500000:00:00.21875008900000:00:00.312500000:00:00.21875009000000:00:00.343750000:00:00.28125009100000:00:00.328125000:00:00.26562509200000:00:00.406250000:00:00.35937509300000:00:00.328125000:00:00.26562509400000:00:00.296875000:00:00.28125009500000:00:00.390625000:00:00.34375009600000:00:00.390625000:00:00.26562509700000:00:00.359375000:00:00.31250009800000:00:00.343750000:00:00.29687509900000:00:00.390625000:00:00.265625010000000:00:00.328125000:00:00.2656250

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

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

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

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

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