Click here to Skip to main content
12,627,316 members (37,212 online)
Rate this:
 
Please Sign up or sign in to vote.
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.");
}
Posted 23-Nov-12 3:30am
Updated 23-Nov-12 3:42am
v3
Comments
digimanus 23-Nov-12 8:34am
   
do you get any exceptions?
Can you change the commandtext and re-execute the command?
mubarakahmad 23-Nov-12 8:38am
   
i used to get "The CLR has been unable to transition from COM context 0x.. to COM
context 0x.. for 60 seconds. The thread that owns the destination
context/apartment is most likely either doing a non pumping wait or
processing a very long running operation without pumping Windows messages.
This situation generally has a negative performance impact and may even lead
to the application becoming non responsive or memory usage accumulating
continually over time. To avoid this problem, all single threaded apartment
(STA) threads should use pumping wait primitives (such as
CoWaitForMultipleHandles) and routinely pump messages during long running
operations." but i unchecked the context deadlock exception and it did not report again
Marcus Kramer 23-Nov-12 8:39am
   
I'm not entirely sure it is a good idea to reuse the same command object over and over like you do.
For the sake of trying, create a separate command statement to be used with each of the ExecuteNonQuery calls.
Prathap Gangireddy 23-Nov-12 9:06am
   
Why dont you use Stored procedures instead of Inline queries.Atleast there will be performance improvement.
digimanus 23-Nov-12 10:54am
   
and use async calls.
better have 1 stored procedure that can update based on differences in 2 tables, the current table and the newvalues table
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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...
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

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.
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.161205.3 | Last Updated 24 Nov 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100