SQL Server 目标与 OLE DB 目标 [英] SQL Server Destination vs OLE DB Destination
问题描述
我使用 OLE Db 目标批量导入多个平面文件.经过一些调整后，我最终将 SQL Server Destination 速度提高了 25 - 50%.
I was using OLE Db destination for Bulk import of multiple Flat Files. After some tuning I ended up with SQL Server Destination to be 25 - 50 % faster.
虽然我对这个目的地感到困惑，因为网络上有相互矛盾的信息，但有些人反对，有些人建议使用它.我想知道，在将其部署到生产环境之前是否存在任何严重的陷阱?谢谢
Though I am confused about this destination as there are contradictory information on the web, some are against it, some are suggesting using it. I would like to know, are there any serious pitfalls before I deploy it to production? Thanks
推荐答案
在这个回答中，我将尝试从 SSIS 的官方文档中提供信息，并提及我在 SQL Server 目标方面的个人经验.
根据官方SQL服务器目标文档:
SQL Server 目标连接到本地 SQL Server 数据库并将数据批量加载到 SQL Server 表和视图中.您不能在访问远程服务器上的 SQL Server 数据库的包中使用 SQL Server 目标.相反，包应该使用 OLE DB 目标.
The SQL Server destination connects to a local SQL Server database and bulk loads data into SQL Server tables and views. You cannot use the SQL Server destination in packages that access a SQL Server database on a remote server. Instead, the packages should use the OLE DB destination.
SQL Server 目标提供与大容量插入任务提供的相同的高速数据插入 SQL Server；但是，通过使用 SQL Server 目标，包可以在将数据加载到 SQL Server 之前将转换应用于列数据.
The SQL Server destination offers the same high-speed insertion of data into SQL Server that the Bulk Insert task provides; however, by using the SQL Server destination, a package can apply transformations to column data before the data is loaded into SQL Server.
为了将数据加载到 SQL Server，您应该考虑使用 SQL Server 目标而不是 OLE DB 目标
For loading data into SQL Server, you should consider using the SQL Server destination instead of the OLE DB destination
2.OLEDB 目的地
根据 官方 OLEDB 目标文档:
OLEDB 目标 - 快速加载选项:将数据加载到 OLE DB 目标中的表或视图中，并使用针对批量插入进行优化的快速加载选项
OLEDB Destination - fast load option: Load data into a table or view in the OLE DB destination and use the fast load option, which are optimized for bulk inserts
3.OLEDB 目标与 SQL Server 目标
根据 SQL Server 目标与 OLE DB 目标 - MSDN 主题:
Donald Farmer，前集成服务集团项目经理说，使用 SQL Server Destination，您可以将性能提高 5% 到 10%.
Donald Farmer, the former Group Program Manager for Integration Services said that you can get a 5 to 10% increase in performance using the SQL Server Destination.
另外参考Matt Masson 是 Microsoft 的数据集成专家，他回答了以下问题:
In addition, refering to the following post of Matt Masson a data integration specialist at Microsoft where he answered the following question:
我应该使用 SQL Server 目标吗?
Should I use the SQL Server Destination?
答案是
否
...
我的建议是，如果您需要每一点性能(在 10 小时负载下提高 10% 的性能可能很重要)，请试用 SQL Server Destination 以了解它如何为您工作.但是，请记住 SQL Server 目标的以下限制:
My recommendation is that if you need every bit of performance (a 10% perf increase on a 10 hour load can be significant), try out the SQL Server Destination to see how it works for you. However – keep in mind the following limitations of the SQL Server Destination:
- 您必须在目标数据库所在的同一台机器上运行 SSIS
- 您必须以管理员身份运行程序包
- 出现问题时很难调试
鉴于这些限制，我建议使用 OLE DB 目标，即使您看到 SQL Server 目标的性能有所提高.
Given these limitations, I recommend using the OLE DB Destination even if you are seeing a performance increase with the SQL Server Destination.
3.1.数据加载性能指南
(更新@ 2019-03-25)
在搜索 SSIS 最佳实践时，我发现了一个非常有用的 Microsoft 文章，可以用作参考:
While searching on SSIS best practices i found a very helpful Microsoft artcile that can be used as a reference:
在这篇文章中，他们对包括 SQL Server 目标和 OLEDB 目标在内的所有数据加载方法进行了比较，他们提到:
In this article they made a comparison between all data loads methods including SQL Server destination and OLEDB destination, they mentioned that:
SQL Server 目标 SQL Server 目标是将数据从 Integration Services 数据流批量加载到 SQL Server 的最快方式.此目标支持 SQL Server 的所有批量加载选项 - 除了 ROWS_PER_BATCH.
SQL Server Destination The SQL Server destination is the fastest way to bulk load data from an Integration Services data flow to SQL Server. This destination supports all the bulk load options of SQL Server – except ROWS_PER_BATCH.
请注意，此目标需要到 SQL Server 的共享内存连接.这意味着它只能在 Integration Services 与 SQL Server 运行在同一台物理计算机上时使用.
Be aware that this destination requires shared memory connections to SQL Server. This means that it can only be used when Integration Services is running on the same physical computer as SQL Server.
OLE DB 目标: OLE DB 目标支持 SQL Server 的所有批量加载选项.但是，为了支持有序批量加载，需要一些额外的配置.有关详细信息，请参阅排序的输入数据".要使用批量 API，您必须将此目的地配置为快速加载".
OLE DB Destination: The OLE DB destination supports all of the bulk load options for SQL Server. However, to support ordered bulk load, some additional configuration is required. For more information, see "Sorted Input Data". To use the bulk API, you have to configure this destination for "fast load".
OLE DB 目标可以使用 TCP/IP 和命名管道连接到 SQL Server.这意味着 OLE DB 目标与 SQL Server 目标不同，可以在大容量加载目标以外的计算机上运行.由于使用 OLE DB 目标的 Integration Services 包不需要在 SQL Server 计算机本身上运行，因此您可以使用主力服务器扩展 ETL 流.
The OLE DB destination can use both TCP/IP and named pipes connections to SQL Server. This means that the OLE DB destination, unlike the SQL Server destination, can be run on a computer other than the bulk load target. Because Integration Services packages that use the OLE DB destination do not need to run on the SQL Server computer itself, you can scale out the ETL flow with workhorse servers.
3.2.亲身经历
(更新@ 2019-03-25)
由于这个问题被很多人用作参考，并且在这个领域有更多经验之后，我添加了这一部分来提及我使用 SQL Server 目标的个人经验.
Since this question is used as a reference by many, and after being more experienced in this domain, i added this section to mention my personal experience using SQL Server destination.
虽然官方文档提到 SQL Server 目标会提高性能，但由于多种原因，我根本不建议使用此组件:
While official documentation mentioned that SQL Server destination will increase performance, i don't recommend at all using this components due to many reasons:
- 它要求目标服务器和 ETL 服务器相同(仅适用于本地 SQL 服务器)
- 它总是抛出没有任何意义的异常
- 在对大量数据进行测试后，与 OLEDB 目标的性能差异可以忽略不计(在分块加载的约 500 GB 数据上进行测试，时间差小于一分钟)
您还可以参考以下帖子(来自@billinkc)以获取有关此主题的更多信息:
You can also refer to the following post (from @billinkc) to get more information about this topic:
基于微软的文章，你可以说SQL Server Destination提高了插入数据的性能(它使用BULK insert)，但它是为特定情况而设计的这是本地 SQL 服务器.OLEDB Destination 更通用，建议在其他情况下使用 Fast Load 数据访问模式 (也使用 BULK 插入)OLE DB 目标 它将提高数据加载的性能.
Based on Microsoft articles, you can say that SQL Server Destination increase the performance of inserting data (it uses BULK insert), but it is designed for a specific case which is the Local SQL server. OLEDB Destination is more general and recommended in the other cases and by using the Fast Load data access mode (which uses also BULK insert) on the OLE DB destination it will increase the performance of data load.
另一方面，根据我的经验和 SSIS 专家撰写的许多文章，完全不建议使用 SQL Server Destination，因为它不稳定，并且经常抛出异常和性能可以忽略不计.
On the other hand, based on my experience and from many articles written by SSIS experts, it is not recommended at all to use SQL Server Destination since it is not stable and it often throws exception and the performance can be considered as negligible.
最近，我发表了一篇关于这个话题的详细文章.您可以在以下位置查看:
Recently, I published a detailed article about this topic. You can check it at:
这篇关于SQL Server 目标与 OLE DB 目标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
