unable to update database and code takes 15 -20 min to execute

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

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 .

C#

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 2:30am

mubarakahmad

Updated 23-Nov-12 2:42am

fjdiewornncalwe

v3

Add a Solution

Comments

Herman<T>.Instance 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

fjdiewornncalwe 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.

Herman<T>.Instance 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

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Tamer Hatoum · Answer 1 · 2012-11-23T10:21:00

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

Mohd. Mukhtar · Answer 2 · 2012-11-23T18:00:00

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.

C#

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.

unable to update database and code takes 15 -20 min to execute

2 solutions

Solution 1

Solution 3

Add your solution here

Preview 0