Takes long time to insert rows in SQL Server 2008 R2

Question

I have over 200,000 records in c# Winforms gridview, it takes around an hour to get inserted into my database. I'm trying to improve the performance of this insert. I'm looking to insert all of the records within 5 to 10 minutes.

I am using For loop to populate each and every row to get insert into DB with a SQL transactions and I don't think that SqlBulkCopy will work out because all 200,000 records needs to be validated with the DB before insertion into DB.

     Save-Function:
     if (chkretailprice.Checked)
        {
                    DataTable dt_grid = (DataTable)gcPromotion.DataSource;
                    dt_grid.AcceptChanges();

                    for (int tt = 0; tt < gvPromotion.RowCount; tt++)
                    {
                        gvPromotion.FocusedRowHandle = tt;
                        double dRGridMinus = Convert.ToDouble(gvPromotion.GetRowCellValue(tt, gvPromotion.Columns["PromotionalRetailPrice"]));
                        string sItem = Convert.ToString(gvPromotion.GetRowCellValue(tt, gvPromotion.Columns["ItemName"]).ToString());
                        string sPack = Convert.ToString(gvPromotion.GetRowCellValue(tt, gvPromotion.Columns["Package"]).ToString());

                        if (dRGridMinus < 0)
                        {
                            gvPromotion.FocusedRowHandle = tt;
                            MessageBoxInfo("Promotional RetailPrice contains Negative Values for this ItemName-'" + sItem + "' & Package-'" + sPack + "'");
                            gvPromotion.Focus();
                            return;
                        }
                    }
                    int iReCount = dt_grid.Select("PromotionalRetailPrice='0.00'").Length;

                    if (iReCount != 0)
                    {
                        MessageBoxInfo("Promotional RetailPrice Must not be 0");
                        gvPromotion.Focus();
                        return;
                    }
       }
      if (rgPromotion.Checked)
         {
                        for (int p = 0; p < gvPromotion.RowCount; p++)
                        {
                            string[] sbranchArr = sBranchIDs.Split(',');
                            for (int pp = 0; pp < sbranchArr.Length; pp++)
                            {
                                objProEntity.PromotionMasterId = objProEntity.PromotionMasterId;
                                objProEntity.BranchId = Convert.ToInt32(sbranchArr[pp]);//gvPromotion.GetRowCellValue(p, gvPromotion.Columns["BranchID"]));
                                objProEntity.ItemId = Convert.ToInt64(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["ItemID"]));
                                objProEntity.PackId = Convert.ToInt32(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["PackTypeID"]));
                                objProEntity.PromotionValueType = Convert.ToString(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["PromotionValueType"]));
                                objProEntity.PromotionValue = Convert.ToString(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["PromotionValue"]));

                                if (chkretailprice.Checked && chkwholesaleprice.Checked)// when both retailprice & wholesaleprice checkbox is checked
                                {
                                    objProEntity.ActualRetailPrice = Convert.ToDecimal(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["ActualRetailPrice"]));
                                    objProEntity.PromoRetailPrice = Convert.ToDecimal(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["PromotionalRetailPrice"]));
                                    objProEntity.ActualWholeSalePrice = Convert.ToDecimal(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["ActualWholeSalePrice"]));
                                    objProEntity.PromoWholesalePrice = Convert.ToDecimal(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["PromotionalWholeSalePrice"]));
                                }
                                else if (chkretailprice.Checked)// when retailprice checkbox is checked
                                {

                                    objProEntity.ActualRetailPrice = Convert.ToDecimal(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["ActualRetailPrice"]));
                                    objProEntity.PromoRetailPrice = Convert.ToDecimal(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["PromotionalRetailPrice"]));
                                    objProEntity.ActualWholeSalePrice = Convert.ToDecimal(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["ActualWholeSalePrice"]));
                                    objProEntity.PromoWholesalePrice = Convert.ToDecimal(0);
                                }
                                else if (chkwholesaleprice.Checked)// when wholesaleprice checkbox is checked
                                {
                                    objProEntity.ActualRetailPrice = Convert.ToDecimal(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["ActualRetailPrice"]));
                                    objProEntity.PromoRetailPrice = Convert.ToDecimal(0);
                                    objProEntity.ActualWholeSalePrice = Convert.ToDecimal(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["ActualWholeSalePrice"]));
                                    objProEntity.PromoWholesalePrice = Convert.ToDecimal(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["PromotionalWholeSalePrice"]));
                                }
                                objProEntity.DiscountAllowed = Convert.ToBoolean(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["DiscountAllowed"]));

                                DataTable dt_Check = new DataTable();
                                dt_Check = SalesPromotionData.IsCheckItemExists(objProEntity, SQLTrans);                                    
                                if (dt_Check.Rows.Count == 0)
                                {
                                    if (!IsEdit)
                                    {
                                        DataTable dt_child = SalesPromotionData.InsertChildData(objProEntity, SQLTrans); // Insert Child Details when isEdit=false
                                    }
                                    else
                                    {
                                        if (gvPromotion.Columns.Contains(gvPromotion.Columns["PromotionChildId"]))
                                            if ((DBNull.Value.Equals(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["PromotionChildId"]))) || (gvPromotion.GetRowCellValue(p, gvPromotion.Columns["PromotionChildId"]) == "") || Convert.ToString(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["PromotionChildId"]).ToString()) == "0")
                                            {
                                                objProEntity.PromotionMasterId = masterid;
                                                SalesPromotionData.InsertChildData(objProEntity, SQLTrans);// insert child details when isEdit=true
                                            }
                                            else
                                            {
                                                objProEntity.PromotionChildId = Convert.ToInt64(gvPromotion.GetRowCellValue(p, gvPromotion.Columns["PromotionChildId"]).ToString());
                                                SalesPromotionData.UpdateChildDetails(objProEntity, SQLTrans); // update child details when isEdit=true
                                            }
                                        else
                                        {
                                            objProEntity.PromotionMasterId = masterid;
                                            SalesPromotionData.InsertChildData(objProEntity, SQLTrans);// insert child details when isEdit=true
                                        }
                                    }
                                }
                            }
                        }
    }

You seem to using the classic RBAR approach. No wonder it takes ages. Why can you not have validation in the database? Then you can perhaps do a bulk insert — Raj, Jun 26 '15 at 13:53
200,000 rows should be insertable in a handful of seconds or less. Can you show the code you're using? — Cᴏʀʏ, Jun 26 '15 at 13:56
What kind of validation is the DB doing? Why can't it validate all 200k rows at the same time? You can use column mapping with Bulk Copy to efficiently insert them to SQL Server: http://stackoverflow.com/a/20108861/2538939 — Code Different, Jun 26 '15 at 14:10
no i cant have validation in db because every grid row is validated to achieve certain conditions — pradeep S, Jun 26 '15 at 14:11
@pradeepS And what are these conditions? Uniqueness, ordering, etc.. — Code Different, Jun 26 '15 at 14:15
promotion will be created for items between specific date range let say between 26-june-2015 to 30-june-2015 so once again same item shouldn't be inserted for above date so i am giving Db hit each and every time to check desired item has already be on promotion if not then i am inserting else those items will be skipped ...200,000 records — pradeep S, Jun 26 '15 at 14:30

score 1 · Answer 1 · answered Jun 26 '15 at 18:15

1

Normally, you'd stage your data into the database by bulk inserting it into [a] work table(s), with no referential integrity or anything -- just the raw data plus any [non-unique] indices you might need. Once you've got it staged, you can then:

Validate the data in the work table(s) against your database and
apply it to the "real" tables in question.

answered Jun 26 '15 at 18:15

Nicholas Carey

71,308
16
93
135

Yes, that's also my opinion. It's even good to have no indexes at all in the "raw table", because every index is slowing down the insert. Also, what are you then doing with the records which get declined. Better quickly load everything into the raw table, then do further processing in the database engine. – SQL Police Jun 30 '15 at 17:48

Takes long time to insert rows in SQL Server 2008 R2

1 Answers1