批量更新和导入数据，给点意见，我现在程序太慢了！

Question

下面是SQL语句！

 1  private static string GetInsertInvMasterSql(bool isBestBuy)
 2         {
 3             StringBuilder sb = new StringBuilder(1000);
 4             sb.AppendLine("IF EXISTS(SELECT * FROM InvMasterData WHERE Sku=@Sku)");
 5             sb.AppendLine("UPDATE InvMasterData SET ");
 6             sb.Append("Channel=@Channel,");
 7             sb.Append("Title=@Title,");
 8             sb.Append("Price=@Price,");
 9             sb.Append("ListingType=@ListingType,");
10             sb.Append("ItemType=@ItemType,");
11             sb.Append("Template=@Template,");
12             sb.Append("Commission_Rate=@Commission_Rate,");
13             sb.Append("Upc=@Upc,");
14             sb.Append("BDC_SKU=@BDC_SKU,");
15             sb.Append("Note=@Note,");
16             if (isBestBuy)
17             {
18                 sb.Append("BBY_SKU=@BBY_SKU,");
19             }
20             sb.Append("LastUpdate=GETDATE() ");
21             sb.Append("WHERE Sku=@Sku ");
22             sb.AppendLine("ELSE");
23             sb.AppendLine("INSERT INTO InvMasterData(");
24             sb.Append("Channel,Sku,Component,Title,Price,");
25             sb.Append("ListingType,ItemType,");
26             sb.Append("Template,Commission_Rate,Upc,BDC_SKU,");
27             if (isBestBuy)
28             {
29                 sb.Append("BBY_SKU,");
30             }
31             sb.Append("Note,");
32             sb.Append("EnterDate,LastUpdate)");
33             sb.Append("VALUES(");
34             sb.Append("@Channel,@Sku,@Component,@Title,@Price,");
35             sb.Append("@ListingType,@ItemType,");
36             sb.Append("@Template,@Commission_Rate,@Upc,@BDC_SKU,");
37             if (isBestBuy)
38             {
39                 sb.Append("@BBY_SKU,");
40             }
41             sb.Append("@Note,");
42             sb.Append("GETDATE(),GETDATE())");
43             return sb.ToString();
44         }

 

 1  public static void InsertInvMasterData(BackgroundWorker bgWorker, bool isBestBuy, DataTable importDt, ConnectionStringSettings newChannelInvDb, ConnectionStringSettings omsDataConn,out DataTable notInInvSkuDt)
 2         {
 3             string functionMsg = "Function:InsertChannelDt(BackgroundWorker bgWorker, bool isBestBuy, DataTable importDt, ConnectionStringSettings importDataConn)" + _NewLine + _ClassMsg;
 4             int rowCount = importDt.Rows.Count;
 5             DataTable skuDt = GetDataTablePartColumns(importDt, "Sku");
 6             Dictionary<string, string> skuAndComponentDict = GetSkuAndComponetDict(skuDt, omsDataConn);
 7             List<string> notInInvSkuList = GetNotInInvSkuList(skuDt, omsDataConn);
 8             notInInvSkuDt = new DataTable();
 9             notInInvSkuDt.Columns.AddRange(new DataColumn[] { new DataColumn("Sku", typeof(string)), new DataColumn("Message", typeof(string)) });
10             SqlTransaction sqlTran = null;
11             try
12             {
13                 SqlConnection sqlConn = new SqlConnection(newChannelInvDb.ConnectionString);
14                 if (sqlConn.State == ConnectionState.Closed || sqlConn.State == ConnectionState.Broken)
15                 {
16                     sqlConn.Open();
17                 }
18                 string strSql = GetInsertInvMasterSql(isBestBuy);
19                 sqlTran = sqlConn.BeginTransaction();
20                 SqlCommand sqlCmd = new SqlCommand(strSql, sqlConn, sqlTran);
21                 sqlCmd.CommandTimeout = 600;
22                 for (int i = 0; i < rowCount; i++)
23                 {
24                     string sku = (importDt.Rows[i]["Sku"] ?? string.Empty).ToString();
25                     int progressPer = (int)((((decimal)i + 1.0m) / (decimal)rowCount) * 95);
26                     string userState = string.Format("Import...{0}/{1}", i + 1, rowCount);
27                     bgWorker.ReportProgress(progressPer, userState);
28                     sqlCmd.Parameters.Clear();
29                     if (!notInInvSkuList.Contains(sku))
30                     {
31                         foreach (DataColumn dc in importDt.Columns)
32                         {
33                             string columnName = dc.ColumnName;
34                             if (columnName.ToUpper() == "BDC SKU")
35                             {
36                                 sqlCmd.Parameters.Add(new SqlParameter("@BDC_SKU", importDt.Rows[i][columnName]));
37                             }
38                             else if (columnName.ToUpper() == "BBY SKU")
39                             {
40                                 sqlCmd.Parameters.Add(new SqlParameter("@BBY_SKU", importDt.Rows[i][columnName]));
41                             }
42                             else if (columnName.ToLower() == "commission rate")
43                             {
44                                 sqlCmd.Parameters.Add(new SqlParameter("@Commission_Rate", importDt.Rows[i][columnName]));
45                             }
46                             else
47                             {
48                                 sqlCmd.Parameters.Add(new SqlParameter("@" + columnName, importDt.Rows[i][columnName]));
49                             }
50                         }
51                         sqlCmd.Parameters.Add(new SqlParameter("@Component", skuAndComponentDict[sku]));
52                         sqlCmd.ExecuteNonQuery();
53                     }
54                     else
55                     {
56                         notInInvSkuDt.Rows.Add(sku, "Sku is not existed in OMS");
57                     }
58                 }
59                 sqlTran.Commit();
60                 sqlConn.Close();
61                 sqlConn.Dispose();
62                 bgWorker.ReportProgress(100, "Complete");
63             }
64             catch (Exception ex)
65             {
66                 if (sqlTran != null)
67                 {
68                     sqlTran.Rollback();
69                 }
70                 string exMsg = "Exception: " + ex.ToString() + _NewLine + functionMsg;
71                 throw new Exception(exMsg, ex);
72             }
73         }

导入的时候，先检查是否存在，存在就去更新，否则就导入！感觉速度好慢，如何提高，给点意见吧！

Answer 1

批量更新 应该一条SQL就可以 批量新增的话 如果是一个表的话 也尽量用一条SQL写。  如   INSERT INTO table(column) VALUES ('') ,（‘’） ，（‘’）

Answer 2

一楼正解。。