0

I've done BulkCopy of List using FastMember and Threading for faster Performance.

Actually,I need to bulkcopy like 999999999 records and I've made an interval for every 1000000 records and Clearing the List to avoid OutofMemory Exception.

The whole process took like 16 Hours to complete and Now,My problem is : No records have been inserted.While the process is going on,I've read the data using (nolock) and my query is showing data and sometimes it is showing No Records.I think the data has been inserting and deleting.

The reason,I'm saying this is : If I start inserting any record now,the Identity Column is showing very big Number in Millions.
So,as per my knowledge,I feel that records have been inserted and deleted.

My Code :

private void Btn_Start_Encrypting_Click(object sender, EventArgs e)
        {
            try
            {
                Btn_Start_Encrypting.Enabled = false;

                Lbl_Msg1.Text = "Time Started : " + DateTime.Now.ToString();

                DateTime StartTime = DateTime.Now;                

                #region List<CustomClass> with Random Numbers

                Random rand = new Random();

                Thread previousThread = null;

                for (int i = 1; i <= 999999999; i++)
                {
                    LstEncData.Add(new EncryptedData { MESSAGE_ID = rand.Next(1, 100), ENCRYPTED_DATA = EncryptDecrypt.Encrypt(i.ToString("D9")) });

                    if (i % 1000000 == 0)
                    {
                        LstEncData = LstEncData.OrderBy(p => p.MESSAGE_ID).ToList();

                        if (previousThread != null)
                        {
                            previousThread.Join();
                        }

                        List<EncryptedData> copyList = LstEncData.ToList();

                        previousThread = new Thread(() => BulkCopyList(copyList));
                        previousThread.Start();

                        LstEncData.Clear();
                    }
                }

                if (LstEncData.Count > 0)
                {
                    if (previousThread != null)
                    {
                        previousThread.Join();
                    }

                    List<EncryptedData> copyList = LstEncData.ToList();

                    previousThread = new Thread(() => BulkCopyList(copyList));
                    previousThread.Start();

                    LstEncData.Clear();
                }

                previousThread.Join();

                Lbl_Msg2.Text = "Time Ended : " + DateTime.Now.ToString();

                DateTime EndTime = DateTime.Now;

                TimeSpan duration = EndTime - StartTime;

                Lbl_Msg3.Text = "Total Time Taken in Seconds : " + duration.TotalSeconds.ToString();

                #endregion
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Btn_Start_Encrypting.Enabled = true;
            }
        }

public void BulkCopyList(List<EncryptedData> Lst)
        {
            try
            {
                using (var bcp = new SqlBulkCopy(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"]))
                {
                    using (var reader = ObjectReader.Create(Lst, "ENCRYPTED_DATA"))
                    {
                        SqlBulkCopyColumnMapping mapEncryData = new SqlBulkCopyColumnMapping("ENCRYPTED_DATA", "ENCRYPTED_DATA");
                        bcp.ColumnMappings.Add(mapEncryData);

                        bcp.DestinationTableName = "EncryptedData1";
                        bcp.WriteToServer(reader);
                    }
                }
            }
            catch (Exception ex)
            {
            }
        }

Did I do anything wrong near Threading (or) BulkCopy ?

RealSteel
  • 1,871
  • 3
  • 37
  • 74
  • One million objects is a lot to bulk insert at once. Possibly an `OutOfMemoryException` is occurring in the `try catch` block inside the `BulkCopyList` is hiding it. A higher bulk insert doesn't translate to faster overall performance either. Try using 100,000. – Loathing Aug 07 '14 at 03:53
  • Another possibility is that the multi-threaded bulk inserts are overlapping, causing an exception because two threads are accessing the same connection at once. – Loathing Aug 07 '14 at 04:01
  • @Loathing : Is there any possibility to check whether the SQL got any errors ? May be something like `log` ? – RealSteel Aug 07 '14 at 04:22
  • Can you confirm no exception is occurring in the BulkCopyList method? – Loathing Aug 07 '14 at 04:40
  • Actually,I started trying with `100,000` records. I'm not sure whether the BulkCopyList method got any exception. i will try with less records and update the result. – RealSteel Aug 07 '14 at 04:48
  • You should set a breakpoint in the catch of the BulkCopyList. – Loathing Aug 07 '14 at 05:02

0 Answers0