我在SQL Server数据库中遇到了困难，我想在数据库中保留引用，并添加来自多个供应商的价格。
我正在读取.xlsx文件引用和价格，并将价格存储在SQL Server数据库中。我必须检查引用是否已经存在-如果是，我会更新，如果不是，我需要插入。
一个ref大约需要100-120毫秒才能插入/更新到数据库中——大部分时间由ExecuteReader(也尝试使用ExecuteNonQuery(消耗，大约70毫秒。
我的问题是：有没有可能让它更快？有了1.000.000个引用，可能需要大约33个小时才能更新。。。
这是代码：
string ref;
string price;
cs.Open();
for (int i = 1; i < lastRow; i++)
{
ref = (xlWorksheet.Cells[i, 1] as Excel.Range).Value2.ToString();
price = (xlWorksheet.Cells[i, 2] as Excel.Range).Value2.ToString();
price = price.Replace(',', '.');
command = new SqlCommand("UPDATE ORG SET "+supplierName+" = '" + price + "' WHERE REFERENCJE = '" + ref + "'"
+ " IF @@ROWCOUNT = 0"
+ " INSERT INTO ORG(" + supplierName + ", REFERENCJE)"
+ " VALUES('" + price + "', '" + ref + "') ", cs);
command.ExecuteReader().Close(); //consume circa 75ms
}
cs.Close();
对不起我的英语。
问题解决了(当然要感谢你们(我是你们建议的-我决定创建临时表，并将所有导入的数据放在那里。下一步将其与主表合并。此外，我将导入从.xlsx从1项更改为2列100行的范围，并将其放入DataTable中，然后使用batch方法插入。也许我的解决方案会对某人有所帮助。我在不到15分钟的时间里更新了1.000000个条目。
代码：
//checking if temp table exists, if not creates it
command = new SqlCommand("IF NOT (EXISTS (SELECT *"
+ " FROM INFORMATION_SCHEMA.TABLES"
+ " WHERE TABLE_NAME = '" + tempTable + "')) CREATE TABLE " + tempTable + "(REFERENCJE VARCHAR(255), PRODUCENT VARCHAR (255), price VARCHAR(255));", cs);
cs.Open();
command.ExecuteNonQuery();
cs.Close();
for (int i = 1; ; i++)
{
//get 2 columns from Excel file with 100 rows and store it in array
Excel.Range range = null;
if (i * 100 > lastRow)
range = xlWorksheet.Range["A" + ((i - 1) * 100 + 1), "B" + lastRow];
else
range = xlWorksheet.Range["A" + ((i - 1) * 100 + 1), "B" + (i * 100)];
Array myValues = (System.Array)range.Cells.Value2;
//rewrites Array to DataTable
for (int j = 1; j < myValues.Length / 2; j++)
{
rowInsert = tabelaInsert.NewRow();
rowInsert["ref"] = myValues.GetValue(j, 1).ToString();
rowInsert["price"] = myValues.GetValue(j, 2).ToString();
rowInsert["prod"] = orgProd;
tabelaInsert.Rows.Add(rowInsert);
}
batchowanieInsert(tabelaInsert, supplierName, tempTable);
tabelaInsert.Clear();
if (i * 100 > lastRow)
break;
}
//SQL MERGE 2 tables
command = new SqlCommand("MERGE ORG AS TARGET"
+ " USING " + tempTable + " AS SOURCE"
+ " ON(TARGET.REFERENCJE = SOURCE.REFERENCJE)"
+ " WHEN MATCHED AND TARGET." + supplierName + " <> SOURCE.CENA THEN"
+ " UPDATE SET TARGET." + supplierName + " = SOURCE.CENA,"
+ " TARGET.PRODUCENT = SOURCE.PRODUCENT"
+ " WHEN NOT MATCHED BY TARGET THEN"
+ " INSERT(REFERENCJE, PRODUCENT, " + supplierName + ")"
+ " VALUES(SOURCE.REFERENCJE, SOURCE.PRODUCENT, SOURCE.CENA);", cs);
cs.Open();
command.ExecuteNonQuery();
cs.Close();
//DELETING temp table
command = new SqlCommand("DROP TABLE " + tempTable, cs);
cs.Open();
command.ExecuteNonQuery();
cs.Close();
//batchowanieInsert function
public void batchowanieInsert(DataTable tabela, string supplierName, string tempTable)
{
da.UpdateBatchSize = tabela.Rows.Count;
da.InsertCommand = new SqlCommand("INSERT INTO " + tempTable + " (REFERENCJE, PRODUCENT, CENA) VALUES (@REFERENCJE, @PRODUCENT, @CENA)", cs);
da.InsertCommand.Parameters.Add("@REFERENCJE", SqlDbType.VarChar, 20, "ref");
da.InsertCommand.Parameters.Add("@PRODUCENT", SqlDbType.VarChar, 20, "prod");
da.InsertCommand.Parameters.Add("@CENA", SqlDbType.VarChar, 20, "price");
da.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
da.Update(tabela);
}
-
将所有数据导入临时表(您可以使用多个INSERT语句，SqlServer在批量处理约100条记录时表现更好(
-
更新存在的
-
插入不存在的
在单个Excel单元格中循环总是会导致效率低下。我建议：
-
将数据导入SQL表，然后使用SELECT语句执行检查，而不是@@ROWCOUNT(请记住，@@ROWCOUNT是从服务器返回并传输到客户端的消息，用于插入的每一行(。请使用@NOCOUN将服务器回复静音。
-
或者考虑使用CSV而不是Excel工作簿。