none
Execute SqlCommand in Parallel.Loop Issue RRS feed

  • Question

  • Hi all,
    I'm testing this concept code:

    static void Main(string[] args)
            {
                DateTime begin = DateTime.Now;

                SqlConnection cnn = new SqlConnection(@"Data Source=APOLINEO_NOTE\LOCALSQL;Initial Catalog=ConceptApp;Integrated Security=True");
                cnn.Open();

                SqlTransaction trans = cnn.BeginTransaction();
                SqlCommand cmd = new SqlCommand("INSERT INTO WAREHOUSE(NAME, ADDRESS, ADDRESSCOMPLEMENT) VALUES (@NAME, @ADDRESS, @ADDRESSCOMPLEMENT); SELECT SCOPE_IDENTITY();", cnn, trans);

                cmd.Parameters.Add(new SqlParameter("@NAME", DBNull.Value));
                cmd.Parameters.Add(new SqlParameter("@ADDRESS", DBNull.Value));
                cmd.Parameters.Add(new SqlParameter("@ADDRESSCOMPLEMENT", DBNull.Value));

                try
                {
                Parallel.For(0, 35000, index =>
                    {
                        cmd.Parameters[0].Value = "Name " + index.ToString();
                        cmd.Parameters[1].Value = "R " + index.ToString();
                        cmd.Parameters[2].Value = DBNull.Value;

                        cmd.ExecuteNonQuery();
                    });
                    trans.Commit();

                }
                catch
                {
                    trans.Rollback();
                }
                finally
                {
                    cnn.Close();
                }

                DateTime end = DateTime.Now;
                Console.WriteLine((end - begin).TotalSeconds.ToString());
                Console.ReadLine();
            }

    When i run it i am getting an NullReferenceException on "FinishExecuteReader" inside of SqlCommand. Bellow is the stack trace:

    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()\r\n   em ParallelInsert.Program.<>c__DisplayClass2.<Main>b__0(Int32 index) na D:\\Projects\\Test\\ParallelInsert\\ParallelInsert\\Program.cs:linha 34
    at System.Threading.Tasks.Parallel.<>c__DisplayClassf`1.<ForWorker>b__c()



    Any one knows if the current ado .net (3.5) has a limitation? Or is there another way to do that?



    Tks,
    Thiago Fernandes
    Thiago H M Fernandes
    Tuesday, February 23, 2010 2:30 AM

Answers

  • Hi Thiago H M Fernandes,

    I have tested your code and reproduced the problem. After some research and analysis, I think I may find the reason. There is only one Sqlconnection, and multiple threads use this one connection to insert rows into one table. If one thread is now executing cmd.ExecuteNonQuery() and occupying the connection, the problem may occur when another thread is also trying to executing cmd.ExecuteNonQuery(). So I tried to add a lock before cmd.ExecuteNonQuery() and the code execute successfully.

    Below is my codes to do the test.

    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
    
        private string connlock = "Lock";
    
        private void button1_Click(object sender, EventArgs e)
        {
            DateTime begin = DateTime.Now;
            SqlConnection cnn = new SqlConnection(@"Data Source=.\SQLExpress; Initial Catalog = Northwind; Integrated Security=True;");
            //SqlConnection cnn = new SqlConnection(@"Data Source=APOLINEO_NOTE\LOCALSQL;Initial Catalog=ConceptApp;Integrated Security=True"); 
            cnn.Open();
    
            SqlTransaction trans = cnn.BeginTransaction();
    
            SqlCommand cmd = new SqlCommand("INSERT INTO Orders(ShipName, ShipAddress, ShipCity) VALUES (@ShipName, @ShipAddress, @ShipCity); SELECT SCOPE_IDENTITY();", cnn, trans);
            //SqlCommand cmd = new SqlCommand("INSERT INTO WAREHOUSE(NAME, ADDRESS, ADDRESSCOMPLEMENT) VALUES (@NAME, @ADDRESS, @ADDRESSCOMPLEMENT); SELECT SCOPE_IDENTITY();", cnn, trans); 
            
            cmd.Parameters.Add(new SqlParameter("@ShipName", DBNull.Value));
            cmd.Parameters.Add(new SqlParameter("@ShipAddress", DBNull.Value));
            cmd.Parameters.Add(new SqlParameter("@ShipCity", DBNull.Value));
    
            try
            {
                System.Threading.Tasks.Parallel.For(0, 35000, index =>
                {
                    cmd.Parameters[0].Value = "Name " + index.ToString();
                    cmd.Parameters[1].Value = "R " + index.ToString();
                    cmd.Parameters[2].Value = DBNull.Value;
    
                    try
                    {
                        lock (connlock)
                        {
                            cmd.ExecuteNonQuery();
                        }
                    }
                    catch (Exception EX)
                    {
                        MessageBox.Show(EX.StackTrace);
                        MessageBox.Show(EX.Message);
                        MessageBox.Show(EX.Source);
                    }
                });
                trans.Commit();
            }
    
            catch (Exception EX)
            {
                MessageBox.Show(EX.StackTrace);
                MessageBox.Show(EX.Message);
                MessageBox.Show(EX.Source);
                trans.Rollback();
            }
    
            finally
            {
                cnn.Close();
            }
    
            DateTime end = DateTime.Now;
            Console.WriteLine((end - begin).TotalSeconds.ToString());
            Console.ReadLine();
        }
    }
    



    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, February 24, 2010 2:43 PM
    Moderator

All replies

  • Hi Thiago H M Fernandes,

    I have tested your code and reproduced the problem. After some research and analysis, I think I may find the reason. There is only one Sqlconnection, and multiple threads use this one connection to insert rows into one table. If one thread is now executing cmd.ExecuteNonQuery() and occupying the connection, the problem may occur when another thread is also trying to executing cmd.ExecuteNonQuery(). So I tried to add a lock before cmd.ExecuteNonQuery() and the code execute successfully.

    Below is my codes to do the test.

    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
    
        private string connlock = "Lock";
    
        private void button1_Click(object sender, EventArgs e)
        {
            DateTime begin = DateTime.Now;
            SqlConnection cnn = new SqlConnection(@"Data Source=.\SQLExpress; Initial Catalog = Northwind; Integrated Security=True;");
            //SqlConnection cnn = new SqlConnection(@"Data Source=APOLINEO_NOTE\LOCALSQL;Initial Catalog=ConceptApp;Integrated Security=True"); 
            cnn.Open();
    
            SqlTransaction trans = cnn.BeginTransaction();
    
            SqlCommand cmd = new SqlCommand("INSERT INTO Orders(ShipName, ShipAddress, ShipCity) VALUES (@ShipName, @ShipAddress, @ShipCity); SELECT SCOPE_IDENTITY();", cnn, trans);
            //SqlCommand cmd = new SqlCommand("INSERT INTO WAREHOUSE(NAME, ADDRESS, ADDRESSCOMPLEMENT) VALUES (@NAME, @ADDRESS, @ADDRESSCOMPLEMENT); SELECT SCOPE_IDENTITY();", cnn, trans); 
            
            cmd.Parameters.Add(new SqlParameter("@ShipName", DBNull.Value));
            cmd.Parameters.Add(new SqlParameter("@ShipAddress", DBNull.Value));
            cmd.Parameters.Add(new SqlParameter("@ShipCity", DBNull.Value));
    
            try
            {
                System.Threading.Tasks.Parallel.For(0, 35000, index =>
                {
                    cmd.Parameters[0].Value = "Name " + index.ToString();
                    cmd.Parameters[1].Value = "R " + index.ToString();
                    cmd.Parameters[2].Value = DBNull.Value;
    
                    try
                    {
                        lock (connlock)
                        {
                            cmd.ExecuteNonQuery();
                        }
                    }
                    catch (Exception EX)
                    {
                        MessageBox.Show(EX.StackTrace);
                        MessageBox.Show(EX.Message);
                        MessageBox.Show(EX.Source);
                    }
                });
                trans.Commit();
            }
    
            catch (Exception EX)
            {
                MessageBox.Show(EX.StackTrace);
                MessageBox.Show(EX.Message);
                MessageBox.Show(EX.Source);
                trans.Rollback();
            }
    
            finally
            {
                cnn.Close();
            }
    
            DateTime end = DateTime.Now;
            Console.WriteLine((end - begin).TotalSeconds.ToString());
            Console.ReadLine();
        }
    }
    



    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, February 24, 2010 2:43 PM
    Moderator
  • Hi Alex,
    Thanks for your answer.

    I did another test with 30.000 rows (and less) and without lock and it worked well. Did you tried that?

    My purpose was improve the total expend time to insert these all 35.000 rows on the database. Here in my laptop (a core 2 duo) i got about 5.37 seconds (the best was 2.35 seconds) locking the connection. The original code (without lock) but with 30.000 give me 0.13 seconds!

    Maybe is there a limitation in ado .net?



    Regards,
    Thiago H M Fernandes

    Thiago H M Fernandes
    Wednesday, February 24, 2010 5:01 PM
  • Hi Thiago H M Fernandes,

    I have tried with 30,000 rows(without lock), but the problem still occurs. However, when I reduce 30,000 rows to 1000 rows, the code sometimes execute successfully but sometimes not, also the time when the problem occurs is not regular. My computer's processor is single core processor, so I think this problem is relative to the scheduling of multiple threads with only one SqlConnection. You have a core 2 duo processor, and you can process 30,000 rows successfully but have problem on 35,000 rows. I have a single core processor, I can process 1000 rows successfully but have problem on 30,000 rows. If I add a lock, all works well.  

    Best regards,
    Alex Liang
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, February 25, 2010 3:35 AM
    Moderator