代码之家  ›  专栏  ›  技术社区  ›  Aaronaught

bcp/批量插入与表值参数的性能

table-valued-parameters bulkinsert sql-server-2008 performance
  •  73
  • Aaronaught  · 技术社区  · 16 年前

    我将不得不使用SQLServer的 BULK INSERT 命令,因为模式已更改,我突然想到,也许我应该考虑切换到具有TVP的存储过程,但我想知道它可能对性能有什么影响。

    一些可能有助于解释我为什么要问这个问题的背景信息:

    • 数据实际上是通过web服务输入的。web服务将文本文件写入数据库服务器上的共享文件夹,然后数据库服务器执行 批量插入 . 这个过程最初是在SQLServer2000上实现的,当时除了扔掉几百个之外,实在别无选择 INSERT 服务器上的语句,这实际上是原始进程,是一个性能灾难。

    • 要插入的数据量是“大的”,但不是“大的”——通常是几百行,在极少数情况下可能是5-10k行。所以我的直觉是 批量插入 作为一个未记录的操作不会使 那个

    • 是 批评的

    批量插入 有TVP的有:

    • 在NetBIOS上编写文本文件可能已经花费了一些时间,从体系结构的角度来看,这相当可怕。

    • 我认为可以(而且应该)取消临时会议桌。它存在的主要原因是插入的数据需要在插入的同时用于其他几个更新,并且尝试从大规模生产表进行更新的成本远远高于使用几乎为空的临时表。对于TVP,参数 是

    • 我几乎可以消除重复检查、清理代码以及与批量插入相关的所有开销。

    • 如果服务器一次获得其中一些事务,则无需担心暂存表或tempdb上的锁争用(我们试图避免它,但它确实发生了)。

    很明显,在投入生产之前,我会先对其进行分析,但我认为在我花费所有时间之前,先问问周围的人,看看是否有人对使用TVPs进行此用途有任何严厉的警告。

    那么,对于任何一个对SQLServer2008非常熟悉的人来说,如果他已经尝试过或者至少已经调查过这个问题,那么他的结论是什么呢?比如说,插入几百到几千行,发生的频率相当高,TVP切芥末了吗?与批量插入相比,性能是否有显著差异?

    (又名:测试结果)

    在经历了36个阶段的部署过程之后,最终结果现在已经投入生产。这两种解决方案都经过了广泛测试:

    • SqlBulkCopy 直接分类;
    • 使用TVPs切换到存储过程。

    只是为了让读者了解 什么 为了消除对该数据可靠性的任何怀疑,这里对该导入过程进行了更详细的解释 事实上 :

    1. 从一个时间数据序列开始,该序列通常约为20-50个数据点(尽管有时可能高达几百个);

    3. 将所有3个序列和原始序列合并成一批。

    4. 将所有8-10个现已完成的加工任务中的批次组合成一个大超级批次。

    5. 使用 策略(参见下一步),或TVP策略(跳至步骤8)。

    6. SqlBulkCopy

    7. 运行一个存储过程,该存储过程(a)对其中两个表执行一系列聚合步骤,包括 JOIN 条件,然后(b)执行 MERGE 在6个生产表上使用聚合和非聚合数据(已完成)

      或

    8. 生成4 DataTable 包含要合并的数据的对象;其中3个包含CLR类型,不幸的是ADO.NET TVPs不正确支持这些类型,因此必须将它们作为字符串表示形式插入,这会稍微影响性能。

    9. 将TVP馈送到存储过程,该存储过程执行与(7)基本相同的处理,但直接处理接收到的表(已完成)

    结果相当接近,但TVP方法最终平均表现更好,即使数据少量超过1000行。

    最初,平均合并几乎需要8秒才能完成(在正常负载下)。删除NetBIOS乱码并切换到 将时间缩短到几乎正好7秒。切换到TVPs进一步缩短了恢复的时间 5.2秒 每批。那是个好主意 改善35% SqlBulkCopy .

    事实上,我相当有信心,真正的改善远远不止于此。在测试过程中,很明显,最终合并不再是关键路径;相反,执行所有数据处理的Web服务开始受到传入请求数量的限制。CPU和数据库I/O都没有真正达到最大值,也没有明显的锁定活动。在某些情况下,我们看到连续合并之间有几秒钟的空闲时间。有一个轻微的差距,但小得多(半秒左右)使用时 SqlBulkCopy . 但我想这将成为另一天的故事。

    批量插入 在中型数据集上运行的复杂导入+转换过程的操作。

    我想补充另外一点,只是为了缓解支持临时会议桌的人们的担忧。在某种程度上,整个服务是一个巨大的过渡过程。流程的每一步都经过严格审核,因此我们不会 需要 一个暂存表,用于确定某些特定合并失败的原因(尽管在实践中几乎从未发生)。我们所要做的就是在服务中设置一个调试标志,它将中断到调试器或将其数据转储到文件而不是数据库。

    插入 和 UPDATE

    还请注意,我们有 不 替换了每一个 使用TVPs进行操作。一些处理大量数据和/或不需要对数据做任何特殊处理(只需将数据扔到数据库中)的操作仍在使用 SqlBulkCopy . SqlBulkCopy

    好了。关键是TToni找到了最相关的链接,但我也很欣赏其他回复。再次感谢!

    4 回复  |  直到 16 年前
        1
  •  10
  •   TToni    16 年前

    here .

    他们说批量插入具有更高的启动成本,但此后速度更快。在远程客户端场景中,它们在大约1000行处划出界限(对于“简单”服务器逻辑)。从他们的描述来看,我认为你应该可以使用TVP。性能上的损失(如果有的话)可能可以忽略不计,而且架构上的好处似乎非常好。

        2
  •  7
  •   Community Mohan Dere    8 年前

    2008 和 2008 R2

    另一方面,SQL Server客户咨询团队提供了以下白皮书: Maximizing Throughput with TVP

    我倾向于避免使用临时表,因为数据验证应该在应用层完成。通过使用TVPs,这很容易适应,并且存储过程中的TVP Table变量本质上是一个本地化的暂存表(因此与同时运行的其他进程没有冲突,就像使用实际表进行暂存时一样)。

    关于问题中所做的测试,我认为可以证明它比最初发现的更快:

    1. IEnumerable<SqlDataRecord> 接口速度更快,占用的内存更少,因为您不会在内存中复制集合,而只是将其发送到数据库。我在以下地方对此进行了记录:
    2. TVP是表变量,因此不维护统计信息。也就是说,它们只向查询优化器报告一行。因此,在您的过程中,或者:
      • 使用TVP对任何查询使用语句级重新编译,而不是简单的SELECT: OPTION (RECOMPILE)
      • # )并将TVP的内容复制到temp表中
        3
  •  5
  •   AdaTheDev    16 年前

    我想我还是坚持批量插入的方法。您可能会发现,使用具有合理行数的TVP仍然会命中tempdb。这是我的直觉,我不能说我已经测试过使用TVP的性能(不过我也对听别人的输入感兴趣)

    您没有提到是否使用.NET,但我为优化以前的解决方案而采取的方法是使用 SqlBulkCopy SqlBulkCopy 类(例如)DataTable-这是将数据插入数据库的最快方法。5-10K行并不多,我已经使用了750K行。我怀疑,一般来说,使用TVP的几百行不会有很大的不同。但扩大规模对IMHO来说是有限的。

    也许是新的 MERGE SQL 2008中的功能对您有好处吗?

    另外,如果您现有的暂存表是用于此流程的每个实例的单个表,并且您担心争用等问题,您是否考虑过每次创建一个新的“临时”但物理的暂存表,然后在完成后将其删除?

    注意:您可以通过在不使用任何索引的情况下填充此临时表来优化加载。然后,一旦填充完毕,在该点上添加任何所需的索引(FILLFACTOR=100以获得最佳读取性能,因为此时它不会被更新)。

        4
  •  -2
  •   HLGEM    16 年前

    中转台很好!真的,我不想用别的方法。为什么?因为数据导入可能会发生意外的变化(通常是以您无法预见的方式,例如,当列仍被称为first name和last name,但在last name列中有first name数据时,选择一个不是随机的例子。)很容易研究暂存表的问题,这样您就可以确切地看到导入处理的列中有哪些数据。我想当你使用内存中的表时,很难找到。我知道很多人和我一样以进口为生,他们都建议使用中转表。我怀疑这是有原因的。

    那么,您将如何取消所有的数据清理任务呢?你可能做得不一样,但仍然需要去做。同样,按照您描述的方式更改流程是非常危险的。

    就我个人而言,听起来你只是因为使用了旧技术而不是因为有机会玩新玩具而生气。除了批量插入是如此2000之外,您似乎没有想要更改的实际依据。

    推荐文章
    关于   移动版
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号