关于SQL 参数的一次测试(重新又测了一次)
园里的henry前些天提醒我在封装SQL语句的时候需要注意参数名尽量重复使用,不要每次都用不一样的参数名,他说如果每次参数名都用不一样SQL Server内存会越占越大,他曾经试过占到1G内存。我没有做过这方面的试验,光听henry讲心里没什么底,询问了厦门.NET具乐部里的朋友们,他们都以为我是在说SqlParameter没有被CG回收。。。一个劲的说Clear,Dispose,using。。。这也不能怪他们,是我表达的不清楚。最后没办法,我自己做了个测试。
测试所用的表结构很简单:
测试分两次,一次向数据库插入10W条记录,在同一次数据库连接和事物中完成,第一次测试每次插入用的SQL参数是用Guid生成的不重复的参数名,第二次测试每次插入用的参数名都是一样的。
测试结果如下:
从测试结果可以看出两次测试的结果差别巨大,使用不重复的参数名不光吃了很多的内存还执行缓慢!
小弟不材,测试会做,但是不知道为什么SQL Server会有这样的问题,希望路过的大虾可以帮小弟解答下为什么会有这样的情况。
我测试用的代码(请各位用自己的数据层或者单纯ADO.NET替换我的数据层部分)
这是第二次测试用的代码,和第一次测试唯一区别就是用固定的参数名代替了原来的Guid。
介于大家对字符串操作影响代码效率的问题,我重新设计了一次测试,这次测试的结果是:
使用重复的参数名,插入100000条记录,使用了40多秒的时间,刚开始运行时SQL Server内存占用12M,运行时最高峰占29M,结束时占内存26M。
使用不重复的参数名,插入100000条记录,使用了190多秒的时间,刚开始运行时SQL Server内存占用26M,运行时最高峰为305M,结束时占内存96M。
测试代码如下:
第二次的测试可以看出字符串操作并不是影响SQL Server执行的关键,也更不可能会影响SQL Server的内存占用,应为字符串是.NET程序域内存的一部分而不是SQL Server的
测试所用的表结构很简单:
CREATE TABLE [dbo].[MyTable] (
[Code] [char] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[CreateTime] [datetime] NULL
) ON [PRIMARY]
GO
[Code] [char] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[CreateTime] [datetime] NULL
) ON [PRIMARY]
GO
测试分两次,一次向数据库插入10W条记录,在同一次数据库连接和事物中完成,第一次测试每次插入用的SQL参数是用Guid生成的不重复的参数名,第二次测试每次插入用的参数名都是一样的。
测试结果如下:
从测试结果可以看出两次测试的结果差别巨大,使用不重复的参数名不光吃了很多的内存还执行缓慢!
小弟不材,测试会做,但是不知道为什么SQL Server会有这样的问题,希望路过的大虾可以帮小弟解答下为什么会有这样的情况。
我测试用的代码(请各位用自己的数据层或者单纯ADO.NET替换我的数据层部分)
这是第二次测试用的代码,和第一次测试唯一区别就是用固定的参数名代替了原来的Guid。
DateTime _t1 = DateTime.Now;
using (DatabaseSession _seesion = new DatabaseSession("TestDB"))
{
for (int i = 0; i < 100000; i++)
{
string _par1 = "par1"; //Guid.NewGuid().ToString().Replace("-", "");
string _par2 = "par2"; //Guid.NewGuid().ToString().Replace("-", "");
string _sql = string.Format("Insert into MyTable (Code,CreateTime) values(@{0},@{1})", _par1, _par2);
IDataParameter _param1 = _da.CreateDataParameter(string.Format("@{0}", _par1),
DbType.String, Guid.NewGuid().ToString());
IDataParameter _param2 = _da.CreateDataParameter(string.Format("@{0}", _par2),
DbType.DateTime, DateTime.Now);
_da.ExecuteNonQuery(_sql, _seesion.DbConnection, _seesion.DbTransaction, _param1, _param2);
}
}
using (DatabaseSession _seesion = new DatabaseSession("TestDB"))
{
for (int i = 0; i < 100000; i++)
{
string _par1 = "par1"; //Guid.NewGuid().ToString().Replace("-", "");
string _par2 = "par2"; //Guid.NewGuid().ToString().Replace("-", "");
string _sql = string.Format("Insert into MyTable (Code,CreateTime) values(@{0},@{1})", _par1, _par2);
IDataParameter _param1 = _da.CreateDataParameter(string.Format("@{0}", _par1),
DbType.String, Guid.NewGuid().ToString());
IDataParameter _param2 = _da.CreateDataParameter(string.Format("@{0}", _par2),
DbType.DateTime, DateTime.Now);
_da.ExecuteNonQuery(_sql, _seesion.DbConnection, _seesion.DbTransaction, _param1, _param2);
}
}
介于大家对字符串操作影响代码效率的问题,我重新设计了一次测试,这次测试的结果是:
使用重复的参数名,插入100000条记录,使用了40多秒的时间,刚开始运行时SQL Server内存占用12M,运行时最高峰占29M,结束时占内存26M。
使用不重复的参数名,插入100000条记录,使用了190多秒的时间,刚开始运行时SQL Server内存占用26M,运行时最高峰为305M,结束时占内存96M。
测试代码如下:
DateTime _t1 = DateTime.Now;
List<ExecuteBatchItem> _bath = new List<ExecuteBatchItem>();
//预备批查询的所有项,此时还未连接数据库和执行数据库操作
for (int i = 0; i < 100000; i++)
{
string _par1 = Guid.NewGuid().ToString().Replace("-", "");//"par1";
string _par2 = Guid.NewGuid().ToString().Replace("-", "");//"par2";
string _sql = string.Format("Insert into MyTable (Code,CreateTime) values(@{0},@{1})", _par1, _par2);
List<IDataParameter> _params = new List<IDataParameter>();
IDataParameter _param1 = _da.CreateDataParameter(string.Format("@{0}", _par1),
DbType.String, Guid.NewGuid().ToString());
IDataParameter _param2 = _da.CreateDataParameter(string.Format("@{0}", _par2),
DbType.DateTime, DateTime.Now);
_params.Add(_param1);
_params.Add(_param2);
_bath.Add(new ExecuteBatchItem(_sql, _params.ToArray(), ExecuteTypes.ExecuteNonQuery));
}
//我的数据层的批查询方法,在同一次数据库连接中执行_bath里的所有查询操作
DataAccess.Create("TestDB").ExecuteBatch(_bath, true);
//计算时间
Response.Write(((TimeSpan)(DateTime.Now - _t1)).TotalMilliseconds);
List<ExecuteBatchItem> _bath = new List<ExecuteBatchItem>();
//预备批查询的所有项,此时还未连接数据库和执行数据库操作
for (int i = 0; i < 100000; i++)
{
string _par1 = Guid.NewGuid().ToString().Replace("-", "");//"par1";
string _par2 = Guid.NewGuid().ToString().Replace("-", "");//"par2";
string _sql = string.Format("Insert into MyTable (Code,CreateTime) values(@{0},@{1})", _par1, _par2);
List<IDataParameter> _params = new List<IDataParameter>();
IDataParameter _param1 = _da.CreateDataParameter(string.Format("@{0}", _par1),
DbType.String, Guid.NewGuid().ToString());
IDataParameter _param2 = _da.CreateDataParameter(string.Format("@{0}", _par2),
DbType.DateTime, DateTime.Now);
_params.Add(_param1);
_params.Add(_param2);
_bath.Add(new ExecuteBatchItem(_sql, _params.ToArray(), ExecuteTypes.ExecuteNonQuery));
}
//我的数据层的批查询方法,在同一次数据库连接中执行_bath里的所有查询操作
DataAccess.Create("TestDB").ExecuteBatch(_bath, true);
//计算时间
Response.Write(((TimeSpan)(DateTime.Now - _t1)).TotalMilliseconds);
第二次的测试可以看出字符串操作并不是影响SQL Server执行的关键,也更不可能会影响SQL Server的内存占用,应为字符串是.NET程序域内存的一部分而不是SQL Server的