很长时间插入SQL Server 2008 R2 [英] Long time to insert into SQL Server 2008 R2

问题描述

我在c＃Winforms gridview中有超过200,000条记录，大约需要一个小时才能插入到我的数据库中。我正在努力提高这种插入的性能。我想在5到10分钟内插入所有记录。



我使用For循环来填充每一行以插入到DB中SQL事务，我不认为SqlBulkCopy会运行，因为在插入之前需要使用DB验证所有200,000条记录。



真实场景：

将在2015年6月26日至2015年6月30日之间的特定日期范围之间为某些项目创建促销活动，因此再次同一项目不应该属于新促销，所以我我给每个Db点击每次检查所需的项目已经在促销，如果没有然后我插入其他这些项目将被跳过...像智者完全我有200,000条记录检查

如何我可以优化以下代码或新的任何新方法来完成任务



保存功能：

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.

Real Scenario:
Promotion will be created for some items between specific date range let say between 26-june-2015 to 30-june-2015 so once again same item shouldn't fall for new promotion so i am giving Db hit each and every time to check desired item has already been on promotion if not then i am inserting else those items will be skipped ...like wise totally i have 200,000 records to check
How can i optimize the below code or new any new way to accomplish the task

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

推荐答案

您可以单独调用DB，在存储过程中检查数据是否存在，如果没有则插入或者不执行任何操作。





您可以立即将所有元素发送到DB，让存储过程执行这些操作。您可以在SQL服务器中使用Cursor并循环遍历记录。



要将整个列表发送到SQL存储过程，请按照以下链接。



http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure [ ^ ]

You can do single call to DB, where in your stored procedure checks if the Data is there, if not then insert or else don't do anything.


You can send all the elements to DB at once and let the Stored procedure do these things. You can use the Cursor in SQL server and loop through the records.

To send entire list to SQL stored procedure, please follow the below link.

http://stackoverflow.com/questions/11102358/how-to-pass-an-array-into-a-sql-server-stored-procedure[^]

这篇关于很长时间插入SQL Server 2008 R2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！