主页 / dba / 问题 / 177316
Accepted
Lucky
Asked: 2017-06-27 12:45:08 +0800 CST

从 DB2 链接服务器上的 OpenQuery 将数据插入 MS SQL 表的最有效方法？

  • 772

经过大量的研究和实验，我想我会尝试获得一些专家的建议。

我正在维护一个作为作业执行的存储过程。它创建了一个带有很好定义的数据的表。然后它使用 OpenQuery 收集一些数据并将其插入到表中。

例子：

insert into LOCAL_TABLE 
select * 
from OPENQUERY(linked_server, 'select * from linktable')

openquery 的结果是 250 万行。这大约需要 2 个小时才能完成。

数据库服务器是 iSeries，所以我使用 Visual Explain 运行查询，查询在大约 1 秒内完成。我正在从 MS SQL Server 2016 运行查询。两台服务器彼此相邻，通过千兆交换机连接。

我试图确定这里的瓶颈，我相信它是 INSERT。

我已阅读有关 BULK INSERT 的信息，看来我无法将 BULK INSERT 与 OpenQuery 一起使用。

我已阅读有关 OPENROWSET(BULK... ) 的信息，但我认为我不能使用链接服务器。我必须维护使用链接服务器，以便如果服务器更改代码不需要更新。

最后，将其作为 SELECT * INTO 运行可能会提高性能，但数据类型将由结果集定义。我想一旦它是本地的，我就可以从那里使用它，但我想在开始之前确认这是值得的。

关于如何提高性能的任何建议？

sql-server db2

3 个回答

  1. 我使用 Visual Explain 运行查询，查询在大约 1 秒内完成。

    然后它可能没有获取 250 万行，这可能是您的瓶颈。

    我试图确定这里的瓶颈，我相信它是 INSERT。

    然后尝试运行

    select * 
from OPENQUERY(linked_server, 'select * from linktable')

    消除INSERT。

    将其作为 SELECT * INTO 运行可能会提高性能

    也测试一下。并使用临时表测试 INSERT INTO。

    使用 4 部分名称不应提高性能。

    • 3

  2. 而不是OPENQUERY，请尝试仅使用 4 部分名称...

    insert into local_table
  select * from linked_server.MYIBMI.MYLIB.MYTBL

    使用 OPENQUERY 的唯一原因是WHERE选择上有一个子句。OPENQUERY 将查询发送到 IBM i，然后只取回结果。使用 4 部分名称，整个表被拉回并在本地执行。由于您没有 WHERE，因此仅使用 4 部分名称没有害处。

    编辑
    David 的正确之处在于 VE 时间可能不适用于所有行。默认情况下，VE 会针对 *FIRSTIO 进行优化，这意味着尽可能快地给我第一页数据；将其更改为 *ALLIO，然后将结果保存到 .CSV 文件。这将使您更好地了解传输所有行需要多长时间。

    在对 MS SQL Server 进行了一些复习之后......并找到了这篇论文， MS SQL 2008的数据加载性能指南。对于我来说，仅使用 MS SQL 和 DB2 的最快方法似乎是将数据导出为 CSV然后使用一种可用的批量插入方法。

    或者，您可以使用 MS SQL Server 集成服务 (SSIS) 或其他 ETL 工具，甚至可以编写自己的批量加载应用程序。

    • 1
  3. Best Answer

    根据@Charles和@David Browne - Microsoft的建议，我创建了四种创建表并使用DB2 OpenQuery 结果填充表的方法并对其进行了基准测试。

    作为测试的一部分，我没有尝试导出为 CSV。从@Charles阅读链接后，从 OLE DB 目标方法中的 SSIS 似乎是使用集成服务的最快方法。如果有人不同意，请随时纠正我。

    刷新一下，我的目标是从 DB2 数据库 OpenQuery 传输 250 万条记录，并将结果插入到 MS Sql Server 上的表中。我的基准测试是在 7 月 3 日执行的，那天公司里很多人都休息了一天，所以我预计服务器负载不会产生太大影响。最后，我会提到我正在为这个数据库使用批量记录事务日志方法。

    我的基准测试结果：

    1. 从 OPENQUERY 方法插入表：00:52:00
    2. 从 OPENQUERY 方法插入到表中（TABLOCK）：00:49:21
    3. 来自 OLE DB 目标方法的SSIS ：00:39:34
    4. 从 OPENQUERY 方法中选择 * 到表中：00:46:58

    使用 SSIS 似乎是从 OpenQuery 批量传输的最快方法。

    • 1

相关问题