更新数据库中许多行的最快方法是什么？

The basic idea is every row has a unique item id which I'm using to decide which row to update, and am updating it with a quantity. I'm importing inventory from a company's api into my own database, and there are about 80k items. They have separate api endpoints for item details vs item quantities, so I need to get all the inventory from the first endpoint first, then call the second and update all the items' quantities.

基本思路是每一行都有一个唯一的项目ID,我用它来决定要更新哪一行,并用数量更新它。我将库存从公司的api导入到我自己的数据库中,大约有8万件物品。它们具有单独的api端点,用于项目详细信息与项目数量,因此我需要先从第一个端点获取所有库存,然后调用第二个端点并更新所有项目的数量。

My initial solution was to just loop though the results I get back and update one row per query. This is taking about an hour to an hour and a half to update all 80k items. Is this just how long it takes to update so many items or is there any sql magic I could do?

我最初的解决方案是循环通过我得到的结果并为每个查询更新一行。这需要大约一个半小时到一个半小时来更新所有80k项目。这只是更新这么多项目需要多长时间,还是我可以做的任何sql魔法?

Thanks

2 个解决方案

#1

The first thing I would do is to store the update data in a separate table. Then run a query such as:

我要做的第一件事是将更新数据存储在一个单独的表中。然后运行查询,例如:

update t
    set . . .
    from t join
         results r
         on t.?? = r.??;

Assuming the column used for the join is indexed, then this should be faster than an hour and a half.

假设用于连接的列被索引,那么这应该比一个半小时快。

#2

You can use Parallel.ForEach to build update script first then execute the script by means of a SqlCommand. The update script consists of multiple update T-SQL statements.

您可以先使用Parallel.ForEach构建更新脚本,然后通过SqlCommand执行脚本。更新脚本包含多个更新T-SQL语句。

Or using the best library of SQL client assembly. It is the SqlBulkCopy feature. You can refer to this article: http://www.developerfusion.com/article/122498/using-sqlbulkcopy-for-high-performance-inserts/

或者使用最好的SQL客户端程序集库。它是SqlBulkCopy功能。您可以参考这篇文章:http://www.developerfusion.com/article/122498/using-sqlbulkcopy-for-high-performance-inserts/

Using SqlBulkCopy to insert large data to a temp table first then write a stored procedure to update data from temp table to destination table.

首先使用SqlBulkCopy将大数据插入临时表,然后编写存储过程以将数据从临时表更新到目标表。

#1