无法更新数据库，代码需要15 -20分钟才能执行 [英] unable to update database and code takes 15 -20 min to execute

问题描述

我正在尝试从excel文件导入数据，并在存在新值的情况下更新一个字段(specific_page的sale_price).
以下代码大约需要15-20分钟才能执行，这是问题之一，excel文件中大约有5000行.
第一个插入查询仅将优良记录从excel文件插入到Sales_update表中.第二个问题是更新查询在sql server中可以正常工作，但在这里不起作用.任何帮助将不胜感激.

I am trying to import data from excel file and update one field(sale_price of specific_page) if new value exists.
the following code takes roughly around 15-20 min to execute which is one of the problem ,there are around 5000 rows in excel file.
the first insert query just fine records are inserted from excel file into Sales_update table . the second problem is that update query works fine in sql server but here it does not work . any help would be much appreciated .

if (str == "sale")
{
   for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
   {
      cmd.Parameters.Clear();
      if (ds.Tables[0].Rows[i]["Sr_No"] != null)
      {
         cmd.Parameters.AddWithValue("@Item", ds.Tables[0].Rows[i]["Sr_No"]);
         cmd.Parameters.AddWithValue("@SalePart", ds.Tables[0].Rows[i]["Sale_Part_No"]);
         cmd.Parameters.AddWithValue("@Sale_Price", ds.Tables[0].Rows[i]["Sale_Price"]);
         cmd.Parameters.AddWithValue("@Partname", ds.Tables[0].Rows[i]["Sale_Part_Desc"]);
         cmd.Parameters.AddWithValue("@LocalPartNo", ds.Tables[0].Rows[i]["Local_Part_No"]);
         cmd.Parameters.AddWithValue("@Parent_Part_No ", ds.Tables[0].Rows[i]["Parent_Part_No"]);
         cmd.Parameters.AddWithValue("@Part_Type_TR_ENG", ds.Tables[0].Rows[i]["Part_Type_TR_ENG"]);
         cmd.Parameters.AddWithValue("@Id1", ds.Tables[0].Rows[i]["Sale_Part_No"]);
         cmd.Parameters.AddWithValue("@Id2", ds.Tables[0].Rows[i]["Local_Part_No"]);

         string query = @"If Not Exists(select * from SalesUpdate where [Item#]=@Item)Begin insert into  SalesUpdate ([Item#],[SalePart],[Sale_Price] ,[Part name] ,[LocalPartNo],[Parent_Part_No],[Part_Type_TR_ENG])VALUES(@Item,@SalePart,@Sale_Price,@Partname,@LocalPartNo,@Parent_Part_No,@Part_Type_TR_ENG);End";
         
         cmd.CommandText = query;
         cmd.ExecuteNonQuery();

         cmd.CommandText = "select * from SalesUpdate CROSS JOIN specific_page where SalesUpdate.[SalePart]=specific_page.[sale_part#] and SalesUpdate.LocalPartNo=specific_page.[local_part#] and SalesUpdate.[Sale_Price]!=specific_page.[sale_price];";
         if (cmd.ExecuteScalar() != null)
         {
            cmd.CommandText = "UPDATE specific_page SET [sale_price] =@Sale_Price FROM SalesUpdate WHERE sale_part#=@Id1 and local_part#=@Id2 and specific_page.[sale_price]!= SalesUpdate.[Sale_Price];";
            cmd.ExecuteNonQuery();
         }
      }
   }
   MessageBox.Show("Successfully added.");
}

推荐答案

您在同一命令上执行的查询数超过一个，这将导致繁重的工作，因此，在与您执行相同命令的同一打开的连接上复杂的查询会导致繁重的工作，首先尝试将您的代码与函数分开，例如尝试检查是否在不同的命令中存在单独的函数，然后在其他函数中进行更新...

you are executing more then one query on the same command and that makes heavy loading , so on the same opened connection with same command you executing complicated queries that makes heavy loading, first try to separate your code to functions , such trying to check if exist in separate function in deferent command , then in other function do the update...

穆巴拉克，你好，

您可以将代码移到存储过程中，然后可以执行存储过程，这样会更快.

2dn您可以尝试以下逻辑.

Hi Mubarak,

You can move code into stored procedure and then you can execute stored procedure and this will be much faster.

2dn you can try with below logic.

if (str == "sale")
{
   for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
   {      
      if (ds.Tables[0].Rows[i]["Sr_No"] != null)
      {
         cmd.Parameters.AddWithValue("@Item", ds.Tables[0].Rows[i]["Sr_No"]);
         cmd.Parameters.AddWithValue("@SalePart", ds.Tables[0].Rows[i]["Sale_Part_No"]);
         cmd.Parameters.AddWithValue("@Sale_Price", ds.Tables[0].Rows[i]["Sale_Price"]);
         cmd.Parameters.AddWithValue("@Partname", ds.Tables[0].Rows[i]["Sale_Part_Desc"]);
         cmd.Parameters.AddWithValue("@LocalPartNo", ds.Tables[0].Rows[i]["Local_Part_No"]);
         cmd.Parameters.AddWithValue("@Parent_Part_No ", ds.Tables[0].Rows[i]["Parent_Part_No"]);
         cmd.Parameters.AddWithValue("@Part_Type_TR_ENG", ds.Tables[0].Rows[i]["Part_Type_TR_ENG"]);
         cmd.Parameters.AddWithValue("@Id1", ds.Tables[0].Rows[i]["Sale_Part_No"]);
         cmd.Parameters.AddWithValue("@Id2", ds.Tables[0].Rows[i]["Local_Part_No"]);

         string query = @"If Not Exists(select * from SalesUpdate where [Item#]=@Item)Begin insert into  SalesUpdate ([Item#],[SalePart],[Sale_Price] ,[Part name] ,[LocalPartNo],[Parent_Part_No],[Part_Type_TR_ENG])VALUES(@Item,@SalePart,@Sale_Price,@Partname,@LocalPartNo,@Parent_Part_No,@Part_Type_TR_ENG);End";
         cmd = new SqlCommand(query,con); //con is your opened sql connection.
         cmd.ExecuteNonQuery();
         cmd = new SqlCommand("select * from SalesUpdate CROSS JOIN specific_page where SalesUpdate.[SalePart]=specific_page.[sale_part#] and SalesUpdate.LocalPartNo=specific_page.[local_part#] and SalesUpdate.[Sale_Price]!=specific_page.[sale_price];", con);
         if (cmd.ExecuteScalar() != null)
         {
            cmd = new SqlCommand("UPDATE specific_page SET [sale_price] =@Sale_Price FROM SalesUpdate WHERE sale_part#=@Id1 and local_part#=@Id2 and specific_page.[sale_price]!= SalesUpdate.[Sale_Price];", con);
            cmd.ExecuteNonQuery();
         }
      }
   }
   MessageBox.Show("Successfully added.");
}

希望对您有帮助.

Hope this help you.

这篇关于无法更新数据库，代码需要15 -20分钟才能执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！