如何加快更新大量行的速度

Question

我有一个包含 1.400.000 个条目的表。它是一个简单的文档列表

表 - 文档

• ID int
• DocumentPath nvarchar
• DocumentValid 位

我扫描一个目录并将在该目录中找到的任何文档设置为有效。

    public void SetReportsToValidated(List<int> validatedReports)
    {
        SqlConnection myCon = null;

        try
        {
            myCon = new SqlConnection(_conn);
            myCon.Open();

            foreach (int id in validatedReports)
            {                    
                SqlDataAdapter myAdap = new SqlDataAdapter("update_DocumentValidated", myCon);
                myAdap.SelectCommand.CommandType = CommandType.StoredProcedure;

                SqlParameter pId = new SqlParameter("@Id", SqlDbType.Int);
                pId.Value = id;
                myAdap.SelectCommand.Parameters.Add(pId); 

                myAdap.SelectCommand.ExecuteNonQuery();
            }
        }
        catch (SystemException ex)
        {
            _log.Error(ex);
            throw;
        }
        finally
        {
            if (myCon != null)
            {
                myCon.Close();
            }
        }
    }

更新的性能还可以，但我想要更多。 100万份文档更新生效需要1个多小时。有什么好的办法可以加快更新速度吗？我正在考虑使用某种批处理（例如表值参数）。 在 SQLServer 上进行分析时，每次更新大约需要 5-10 毫秒。

Answer 1

读取报告并将它们一起附加到数据表中（因为它们具有相同的维度），然后使用 SqlBulkCopy 对象用于上传整个内容。可能会更适合你。鉴于列数和行数较少，我认为您不会遇到内存问题。

Answer 2

目前，您正在分别为每条记录调用数据库。您可以使用 SqlDataAdapter 进行批量更新（简而言之）：

1) 定义一个 SqlDataAdapter
2) 将适配器上的 .UpdateCommand 设置为您的更新存储过程
3) 调用适配器上的 .Update 方法，向其传递一个包含要更新的文档 ID 的 DataTable。这会将数据表中更新的行批量存储到数据库中，以批量方式为每条记录调用存储过程。您可以通过 .BatchSize 属性控制批量大小。
4）所以你要做的就是删除手动的逐行循环，这对于批量更新来说效率低下。

查看示例：
http://support.microsoft.com/kb/308055
http://www.c-sharpcorner.com/UploadFile/61b832/4430/

或者，您可以：
1）使用SqlBulkCopy将所有ID批量插入到数据库中的新表中（效率很高）
2) 加载到该临时表后，运行单个 SQL 语句从该临时表更新主表以验证文档。

查看示例：
http://www.adathedev.co .uk/2010/02/sqlbulkcopy-bulk-load-to-sql-server.html
http://www.adathedev.co .uk/2011/01/sqlbulkcopy-to-sql-server-in-parallel.html

Answer 3

不必每次在循环中创建适配器和参数，只需创建一次并为参数分配不同的值：

            SqlDataAdapter myAdap = new SqlDataAdapter("update_DocumentValidated", myCon);
            myAdap.SelectCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter pId = new SqlParameter("@Id", SqlDbType.Int);
            myAdap.SelectCommand.Parameters.Add(pId); 

            foreach (int id in validatedReports)
            {
                myAdap.SelectCommand.Parameters[0].Value = id;  
                myAdap.SelectCommand.ExecuteNonQuery();
            }

这可能不会带来非常显着的改进，但与原始代码相比更好。此外，当您手动执行 SqlCommand 对象时，您根本不需要适配器。直接使用SqlCommand就可以了。