我用以下两个方法,分别一次插入5000条记录,5000条的sql语句是组装好的,一次插入数据库的
结果使用了事务的方法不到1秒,而没有使用事务的方法使用了8秒
奇怪,不是说使用事务会降低性能吗?
测试分别在sqlserver2005 以及mysql5.1都是一样的结果
代码如下:
没有使用事务的:#region ExecuteNonQuery基础方法
/// <summary>
/// 返回数据访问方法是否执行成功,返回bool值,适用于:增删改,不用于查询!
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="cmdType">sql语句类型</param>
/// <param name="connString">连接字符串</param>
/// <param name="parameters">参数列表</param>
/// <returns></returns>
public static bool ExecuteNonQuery(string sql, CommandType cmdType, string connString, params DbParameter[] parameters)
{
DbConnection conn = DbFactory.CreateConnection();
conn.ConnectionString = connString;
DbCommand cmd = DbFactory.CreateCommand();
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.CommandType = cmdType;
AddParams(parameters, cmd);
try
{
Open(conn);
int returnCount = 0;
returnCount = cmd.ExecuteNonQuery();
if (returnCount > 0)
return true;
return false;
}
catch (Exception e)
{
throw e;
}
finally
{
Close(conn);
cmd.Dispose();
cmd.Parameters.Clear();
}
}
使用事务的:/// <summary>
/// 返回数据访问方法是否执行成功,返回bool值,适用于:增删改,不用于查询!
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="cmdType">sql语句类型</param>
/// <param name="transaction">sql事务</param>
/// <param name="connString">数据库连接字符串,这个参数是为多个数据库而设计</param>
/// <param name="parameters">参数列表</param>
/// <returns></returns>
public static bool ExecuteNonQuery(string sql, CommandType cmdType, DbTransaction transaction, string connString, params DbParameter[] parameters)
{
DbConnection conn = DbFactory.CreateConnection();
conn.ConnectionString = connString;
DbCommand cmd = DbFactory.CreateCommand();
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.CommandType = cmdType;
AddParams(parameters, cmd);
try
{
Open(conn);
transaction = conn.BeginTransaction();
cmd.Transaction = transaction;
int returnCount = 0;
returnCount = cmd.ExecuteNonQuery();
transaction.Commit();
if (returnCount > 0)
return true;
return false;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
Close(conn);
cmd.Dispose();
cmd.Parameters.Clear();
}
}
结果使用了事务的方法不到1秒,而没有使用事务的方法使用了8秒
奇怪,不是说使用事务会降低性能吗?
测试分别在sqlserver2005 以及mysql5.1都是一样的结果
代码如下:
没有使用事务的:#region ExecuteNonQuery基础方法
/// <summary>
/// 返回数据访问方法是否执行成功,返回bool值,适用于:增删改,不用于查询!
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="cmdType">sql语句类型</param>
/// <param name="connString">连接字符串</param>
/// <param name="parameters">参数列表</param>
/// <returns></returns>
public static bool ExecuteNonQuery(string sql, CommandType cmdType, string connString, params DbParameter[] parameters)
{
DbConnection conn = DbFactory.CreateConnection();
conn.ConnectionString = connString;
DbCommand cmd = DbFactory.CreateCommand();
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.CommandType = cmdType;
AddParams(parameters, cmd);
try
{
Open(conn);
int returnCount = 0;
returnCount = cmd.ExecuteNonQuery();
if (returnCount > 0)
return true;
return false;
}
catch (Exception e)
{
throw e;
}
finally
{
Close(conn);
cmd.Dispose();
cmd.Parameters.Clear();
}
}
使用事务的:/// <summary>
/// 返回数据访问方法是否执行成功,返回bool值,适用于:增删改,不用于查询!
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="cmdType">sql语句类型</param>
/// <param name="transaction">sql事务</param>
/// <param name="connString">数据库连接字符串,这个参数是为多个数据库而设计</param>
/// <param name="parameters">参数列表</param>
/// <returns></returns>
public static bool ExecuteNonQuery(string sql, CommandType cmdType, DbTransaction transaction, string connString, params DbParameter[] parameters)
{
DbConnection conn = DbFactory.CreateConnection();
conn.ConnectionString = connString;
DbCommand cmd = DbFactory.CreateCommand();
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.CommandType = cmdType;
AddParams(parameters, cmd);
try
{
Open(conn);
transaction = conn.BeginTransaction();
cmd.Transaction = transaction;
int returnCount = 0;
returnCount = cmd.ExecuteNonQuery();
transaction.Commit();
if (returnCount > 0)
return true;
return false;
}
catch (Exception e)
{
transaction.Rollback();
throw e;
}
finally
{
Close(conn);
cmd.Dispose();
cmd.Parameters.Clear();
}
}
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货