现有两个数据库操作类:通用数据库操作类DbHelper,专用数据库操作类OracleHelper
内部代码基本相同,差别只在数据类型不一样
例如:DbConnection / OracleConnection DbParameter / OracleParameter
创建4个页面,分别为DbHelper_Param , DbHelper_NoParam , OracleHelper_Param , OracleHelper_NoParam
在Oracle中建立表,无索引,无依赖,每次测试用事务插入1000条数据
Name |
Type |
ID |
NUMBER |
TITLE |
VARCHAR2(500) |
CONTENT |
VARCHAR2(500) |
LINKS |
VARCHAR2(50) |
COMMENTS |
VARCHAR2(100) |
CREATOR |
NUMBER |
CREATEDATE |
DATE |
EFFECT |
CHAR(2) |
COMMEND |
CHAR(2) |
REMARK |
VARCHAR2(100) |
DbHelper_Param中的代码片段为
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
DbHelper_Param
1
private readonly string INSERT = "insert into dw_jkdn.GTEST (ID,TITLE,CONTENT,LINKS,COMMENTS,CREATOR,CREATEDATE,EFFECT,COMMEND,REMARK) values (:ID,:TITLE,:CONTENT,:LINKS,:COMMENTS,:CREATOR,sysdate,:EFFECT,:COMMEND,:REMARK)";
2![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
protected void Page_Load(object sender, EventArgs e)
4![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
5
DateTime Start = DateTime.Now; //起始时间
6![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
7
using (DbConnection connection = DBHelper.DbFactory.CreateConnection())
8![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
9
connection.ConnectionString = DBHelper.ConnectionStringSettings.ConnectionString;
10![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
11
connection.Open();
12![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
13
using (DbTransaction trans = connection.BeginTransaction())
14![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
15
try
16![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
17
for (int i = 0; i < 1000; i++)
18![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
19
DbParameter[] Params =
20![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
21
DBHelper.MakeParam(":ID",i),
22
DBHelper.MakeParam(":TITLE","this is Title"),
23
DBHelper.MakeParam(":CONTENT","this is Content"),
24
DBHelper.MakeParam(":LINKS","artile.aspx?id=0"),
25
DBHelper.MakeParam(":COMMENTS","this is Comments"),
26
DBHelper.MakeParam(":CREATOR",i),
27
//DBHelper.MakeParam(":CREATEDATE","sysdate"),
28
DBHelper.MakeParam(":EFFECT",'0'),
29
DBHelper.MakeParam(":COMMEND",'1'),
30
DBHelper.MakeParam(":REMARK","this is Remark")
31
};
32![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
33
DBHelper.ExecuteNonQuery(trans, CommandType.Text, INSERT, Params);
34
}
35![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
36
trans.Commit();
37
}
38
catch (Exception ex)
39![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
40
trans.Rollback();
41
throw ex;
42
}
43
}
44
}
45![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
46
DateTime End = DateTime.Now; //终止时间
47![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
48
TimeSpan span = (End - Start);
49![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
50
Comm.alert(this, span.TotalMilliseconds.ToString(), true);
51
}
DbHelper_NoParam中的代码片段为
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
DbHelper_NoParam
1
DateTime Start = DateTime.Now; //其实时间
2![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
using (DbConnection connection = DBHelper.DbFactory.CreateConnection())
4![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
5
connection.ConnectionString = DBHelper.ConnectionStringSettings.ConnectionString;
6![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
7
connection.Open();
8![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
9
using (DbTransaction trans = connection.BeginTransaction())
10![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
11
try
12![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
13
for (int i = 0; i < 1000; i++)
14![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
15
INSERT = "insert into dw_jkdn.GTEST (ID,TITLE,CONTENT,LINKS,COMMENTS,CREATOR,CREATEDATE,EFFECT,COMMEND,REMARK) values (" + i + ",'" + "this is Title" + "','" + "this is Content" + "','" + "artile.aspx?id=0" + "','" + "this is Comments" + "'," + i + ",sysdate,'0','1','this is Remark')";
16![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
17
DBHelper.ExecuteNonQuery(trans, CommandType.Text, INSERT, null);
18
}
19![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
20
trans.Commit();
21
}
22
catch (Exception ex)
23![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
24
trans.Rollback();
25
throw ex;
26
}
27
}
28
}
29![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
DateTime End = DateTime.Now; //终止时间
31![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
TimeSpan span = (End - Start);
33![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
Comm.alert(this, span.TotalMilliseconds.ToString(), true);
以此类推OracleHelper_Param,OracleHelper_NoParam
以下为测试结果,单位毫秒
DbHelper_Param |
DbHelper_NoParam |
OracleHelper_Param |
OracleHelper_NoParam |
2781.25 |
1828.125 |
2812.5 |
1875 |
2703.125 |
1859.375 |
2750 |
1828.125 |
2781.25 |
1828.125 |
2468.75 |
1781.25 |
2625 |
1843.75 |
2687.5 |
1843.75 |
3015.625 |
2328.125 |
2468.75 |
1687.5 |
2640.625 |
1890.625 |
2453.125 |
1796.875 |
2718.75 |
1906.25 |
2484.375 |
2078.125 |
2671.875 |
1859.375 |
2453.125 |
1687.5 |
2937.5 |
1875 |
2453.125 |
1687.5 |
2640.625 |
1937.5 |
2531.25 |
1781.25 |
2625 |
1890.625 |
2468.75 |
1640.625 |
2703.125 |
1843.75 |
2656.25 |
1671.875 |
去掉一个最大值,和一个最小值,平均值为
2720.3125 |
1873.4375 |
2542.1875 |
1764.0625 |
通过测试可以看出,时间差主要体现在创建参数数组上
DbHelper :2720.3125 - 1873.4375 = 846.875
OracleHelper :2542.1875 - 1764.0625 = 778.125
而原以为时间差主要体现在延迟实例化和数据类型转换,但实际上该时间约等于
(2720.3125 - 2542.1875) - (1873.4375 - 1764.0625) = 178.125 - 109.375 = 68.75
相比于创建参数数组的时间,小很多。
据此判断,造成公司软件添加数据缓慢的原因,是由于插入大表(平均约40-50,最多174个字段)的参数数组引起的,而非数据库操作类。
而对于不同数据库操作类所带来的性能损失,我认为就目前的情况来说可以忽略不计。
对于追求<50毫秒级别的性能来说,还是提高软件整体质量来的划算一些。
由此引发一个问题:
对于拼SQL语句,貌似大家都很鄙视。
但是对于没有用户输入,不存在注入风险的程序来说,以上数据为例,单纯的无参数SQL语句比使用参数传值的SQL插入速度快约31.8%的速度。
并且随着参数的增多,该数值会越来越大。
对于拼写SQL语句,大家的观点如何呢?