使用存储过程将许多记录逐个插入数据库，但速度太慢
本文关键字：数据库 速度 插入 存储过程 许多 记录 | 更新日期: 2023-09-27 18:30:41
我有一个包含大约 8000 条记录的 datagridview，对于每条记录，我调用 SQL Server 存储过程将其插入数据库，代码如下：
for (int i = 0; i < dgv_compare.Rows.Count; i++)
{
if (!DBCommands.sp_app_RatePlanDetail_Add(
CarrierID,
is_supplier == 1 ? 0 : 1,
row.Prefix,
row.RegionName,
row.NewRate,
row.FreeBlock,
row.InitialBlock,
row.RecycleBlock,
row.ConnectionCharge,
row.EnrollDate,
row.ExpiryDate,
row.isDisabled,
row.TimeF1,
row.TimeT1,
row.Rate1,
row.Block1Enabled,
row.TimeF2,
row.TimeT2,
row.Rate2,
row.Block2Enabled,
row.TimeF3,
row.TimeT3,
row.Rate3,
row.Block3Enabled,
Operator,
FlagShortOverLong,
ref ErrCode,
ref ErrMsg))
{
//tb_log.Text += DBCommands.LastError + "'r'n";
MessageBox.Show(DBCommands.LastError);
return;
}
}
函数DBCommands.sp_app_RatePlanDetail_Add是这样的形式：
public static bool sp_app_RatePlanDetail_Add
(
int CustID,
int IsInBound,
string Prefix,
string RegionName,
double RatePerMin,
int FreeBlock,
int InitialBlock,
int RecycleBlock,
double ConnectionCharge,
DateTime EnrollDate,
DateTime ExpiryDate,
int isDisabled,
TimeSpan TimeF1,
TimeSpan TimeT1,
double Rate1,
int Block1Enabled,
TimeSpan TimeF2,
TimeSpan TimeT2,
double Rate2,
int Block2Enabled,
TimeSpan TimeF3,
TimeSpan TimeT3,
double Rate3,
int Block3Enabled,
string Operator,
int FlagShortOverLong,
ref int ErrCode,
ref string ErrMsg
)
{
SqlConnection conn = null;
SqlCommand sqlCmd = null;
try
{
try
{
conn = new SqlConnection(ConnectString);
conn.Open();
if (conn.State != ConnectionState.Open)
{
LastErrorCode = -1;
LastError = "Connect database error.";
return false;
}
}
catch (Exception ex)
{
LastErrorCode = -1;
LastError = "Connect database error. " + ex.ToString();
return false;
}
try
{
sqlCmd = new SqlCommand("sp_app_RatePlanDetail_Add_0909", conn);
sqlCmd.CommandTimeout = ExecuteTimeout;
sqlCmd.CommandType = CommandType.StoredProcedure;
AddParam(sqlCmd, "@CustID", SqlDbType.Int, 4, 10, 0, ParameterDirection.Input, CustID);
AddParam(sqlCmd, "@IsInBound", SqlDbType.Int, 4, 10, 0, ParameterDirection.Input, IsInBound);
AddParam(sqlCmd, "@Prefix", SqlDbType.VarChar, 60, 30, 0, ParameterDirection.Input, Prefix);
AddParam(sqlCmd, "@RegionName", SqlDbType.VarChar, 400, 200, 0, ParameterDirection.Input, RegionName);
AddParam(sqlCmd, "@RatePerMin", SqlDbType.Decimal, 13, 20, 10, ParameterDirection.Input, RatePerMin);
AddParam(sqlCmd, "@FreeBlock", SqlDbType.Int, 4, 10, 0, ParameterDirection.Input, FreeBlock);
AddParam(sqlCmd, "@InitialBlock", SqlDbType.Int, 4, 10, 0, ParameterDirection.Input, InitialBlock);
AddParam(sqlCmd, "@RecycleBlock", SqlDbType.Int, 4, 10, 0, ParameterDirection.Input, RecycleBlock);
AddParam(sqlCmd, "@ConnectionCharge", SqlDbType.Decimal, 9, 18, 8, ParameterDirection.Input, ConnectionCharge);
AddParam(sqlCmd, "@EnrollDate", SqlDbType.DateTime, 8, 23, 3, ParameterDirection.Input, EnrollDate);
AddParam(sqlCmd, "@ExpiryDate", SqlDbType.DateTime, 8, 23, 3, ParameterDirection.Input, ExpiryDate);
AddParam(sqlCmd, "@isDisabled", SqlDbType.Int, 4, 10, 0, ParameterDirection.Input, isDisabled);
AddParam(sqlCmd, "@TimeF1", SqlDbType.Time, 5, 16, 7, ParameterDirection.Input, TimeF1);
AddParam(sqlCmd, "@TimeT1", SqlDbType.Time, 5, 16, 7, ParameterDirection.Input, TimeT1);
AddParam(sqlCmd, "@Rate1", SqlDbType.Decimal, 13, 20, 10, ParameterDirection.Input, Rate1);
AddParam(sqlCmd, "@Block1Enabled", SqlDbType.Int, 4, 10, 0, ParameterDirection.Input, Block1Enabled);
AddParam(sqlCmd, "@TimeF2", SqlDbType.Time, 5, 16, 7, ParameterDirection.Input, TimeF2);
AddParam(sqlCmd, "@TimeT2", SqlDbType.Time, 5, 16, 7, ParameterDirection.Input, TimeT2);
AddParam(sqlCmd, "@Rate2", SqlDbType.Decimal, 13, 20, 10, ParameterDirection.Input, Rate2);
AddParam(sqlCmd, "@Block2Enabled", SqlDbType.Int, 4, 10, 0, ParameterDirection.Input, Block2Enabled);
AddParam(sqlCmd, "@TimeF3", SqlDbType.Time, 5, 16, 7, ParameterDirection.Input, TimeF3);
AddParam(sqlCmd, "@TimeT3", SqlDbType.Time, 5, 16, 7, ParameterDirection.Input, TimeT3);
AddParam(sqlCmd, "@Rate3", SqlDbType.Decimal, 13, 20, 10, ParameterDirection.Input, Rate3);
AddParam(sqlCmd, "@Block3Enabled", SqlDbType.Int, 4, 10, 0, ParameterDirection.Input, Block3Enabled);
AddParam(sqlCmd, "@Operator", SqlDbType.VarChar, 60, 30, 0, ParameterDirection.Input, Operator);
AddParam(sqlCmd, "@FlagShortOverLong", SqlDbType.Int, 4, 10, 0, ParameterDirection.Input, FlagShortOverLong);
AddParam(sqlCmd, "@ErrCode", SqlDbType.Int, 4, 10, 0, ParameterDirection.Output, null);
AddParam(sqlCmd, "@ErrMsg", SqlDbType.VarChar, 100, 50, 0, ParameterDirection.Output, null);
sqlCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int, 4);
sqlCmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
sqlCmd.ExecuteNonQuery();
ErrCode = get_int_value(sqlCmd.Parameters["@ErrCode"].Value);
ErrMsg = get_string_value(sqlCmd.Parameters["@ErrMsg"].Value);
int _temp = Convert.ToInt32(sqlCmd.Parameters["@RETURN_VALUE"].Value.ToString());
return true;
}
catch (Exception ex)
{
LastErrorCode = -1;
LastError = "Execute sp_app_RatePlanDetail_Add error. " + ex.ToString();
return false;
}
}
finally
{
if (sqlCmd != null)
sqlCmd.Dispose();
if (conn != null)
{
conn.Close();
conn.Dispose();
}
}
}
因为我需要做一些检查和比较，所以我无法将数据网格视图保存到csv并一次插入整个数据集。
在这个sp_app_RatePlanDetail_Add_0909的过程中，我做了一些更新和删除，插入运算符，虽然可以工作，但是花费的时间太多了
我想这可能是因为未优化的更新或删除，所以我尝试更改程序sp_app_RatePlanDetail_Add_0909，让它只包含一个语句，如下所示：
select @a=1
但很奇怪，整个过程的速度似乎一点都没变，还是花了很多时间，谁能告诉我是什么原因？ 谢谢。
我想这可能是因为未优化更新或删除，所以我尝试更改程序
sp_app_RatePlanDetail_Add_0909，让它只包含一个语句，如下所示：select @a=1但很奇怪，整个过程的速度似乎一点都没变，还是花了很多时间，谁能告诉我是什么原因？谢谢。
强调我的。
这将表明您的问题与延迟有关（或者，存储过程的性能不是性能问题所在;请考虑查找其他位置）。也就是说，发送请求以执行存储过程并接收结果的时间远远大于执行存储过程所需的时间。
由于每条记录调用一次存储过程，因此由于延迟而"浪费"的时间会增加 8，000 倍。
通过使用表值参数一次将许多记录流式传输到存储过程，可以显著减少往返次数。
请注意，使用表值参数不是灵丹妙药，存储过程可能会由于参数探查而性能不佳。
您确定是存储过程执行时间过长吗？或者您指的是通过 DataGridView 执行整个循环？因为据我所知，您实际上正在打开和关闭与数据库的 8000 个连接，这可能会消耗一些时间。
似乎
您为存储过程的每次调用打开/关闭连接。 如果要提高性能，请首先尝试使用单个连接而不是多个连接（即将连接作为参数传递给方法并在外部处理连接）。 像这样：
using (SqlConnection con = new SqlConnection("your connection string")){
con.Open();
for (int i = 0; i < dgv_compare.Rows.Count; i++)
{
if (!DBCommands.sp_app_RatePlanDetail_Add(
con,
CarrierID,
...
};
con.Close();
}
相信这将缓解性能问题