SSIS - OLE DB Destination (Data Access - Fast load configuration)
来源:互联网 发布:三维雕刻机软件 编辑:程序博客网 时间:2024/05/12 05:11
Here is better example:
http://stackoverflow.com/questions/10688583/what-is-the-difference-between-two-keep-identity-in-oledb-destination
http://www.mssqltips.com/sqlservertip/1840/sql-server-integration-services-ssis-best-practices/
Effect of OLEDB Destination Settings
There are couple of settings with OLEDB destination which can impact the performance of data transfer as listed below.
Data Access Mode – This setting provides the 'fast load' option which internally uses a BULK INSERT statement for uploading data into the destination table instead of a simple INSERT statement (for each single row)as in the case for other options. So unless you have a reason for changing it, don't change this default value of fast load. If you select the 'fast load' option, there are also a couple of other settings which you can use as discussed below.
Keep Identity – By default this setting is unchecked which means the destination table (if it has an identity column) will create identity values on its own. If you check this setting, the dataflow engine will ensure that the source identity values are preserved and same value is inserted into the destination table.
Keep Nulls – Again by default this setting is unchecked which means default value will be inserted (if the default constraint is defined on the target column) during insert into the destination table if NULL value is coming from the source for that particular column. If you check this option then default constraint on the destination table's column will be ignored and preserved NULL of the source column will be inserted into the destination.
Table Lock – By default this setting is checked and the recommendation is to let it be checked unless the same table is being used by some other process at same time. It specifies a table lock will be acquired on the destination table instead of acquiring multiple row level locks, which could turn into lock escalation problems.
Check Constraints – Again by default this setting is checked and recommendation is to un-check it if you are sure that the incoming data is not going to violate constraints of the destination table. This setting specifies that the dataflow pipeline engine will validate the incoming data against the constraints of target table. If you un-check this option it will improve the performance of the data load.
Effect of Rows Per Batch and Maximum Insert Commit Size Settings
Rows per batch – The default value for this setting is -1 which specifies all incoming rows will be treated as a single batch. You can change this default behavior and break all incoming rows into multiple batches. The allowed value is only positive integer which specifies the maximum number of rows in a batch.
Maximum insert commit size – The default value for this setting is '2147483647' (largest value for 4 byte integer type) which specifies all incoming rows will be committed once on successful completion. You can specify a positive value for this setting to indicate that commit will be done for those number of records. You might be wondering, changing the default value for this setting will put overhead on the dataflow engine to commit several times. Yes that is true, but at the same time it will release the pressure on the transaction log and tempdb to grow tremendously specifically during high volume data transfers.
The above two settings are very important to understand to improve the performance of tempdb and the transaction log. For example if you leave 'Max insert commit size' to its default, the transaction log and tempdb will keep on growing during the extraction process and if you are transferring a high volume of data the tempdb will soon run out of memory as a result of this your extraction will fail. So it is recommended to set these values to an optimum value based on your environment.
Note: The above recommendations have been done on the basis of experience gained working with DTS and SSIS for the last couple of years. But as noted before there are other factors which impact the performance, one of the them is infrastructure and network. So you should do thorough testing before putting these changes into your production environment.
- SSIS - OLE DB Destination (Data Access - Fast load configuration)
- SSIS Data Flows - ADO.NET vs. OLE DB vs. ODBC
- SSIS OLE DB 命令 使用方法
- SQL2005-SSIS 使用 OLE DB Command Transformation 组件
- [SQL2005]SSIS 使用 OLE DB Command Transformation 组件
- SSIS高级转换任务—OLE DB命令
- VC使用OLE DB访问ACCESS、EXCEL
- Fast Data Access MMU Miss
- OLE DB for Data Mining中的基本概念
- ODBC && OLE DB.NET DATA PROVIDER
- Oracle load data into db
- SQL 2005: SSIS – PUSHING DATA TO MYSQL USING SCRIPT COMPONENT DESTINATION
- OLE DB
- OLE DB
- OLE DB
- ole db
- OLE DB
- 使用ole db连接带有工作组信息的Access文件.
- MySQL保存emoji
- 用什么语言开发的Matlab
- 网上找的一些有用的eclipse快捷键
- IplImage, CvMat, Mat 的关系和相互转换(转)
- linux 未跑程序 内存 cache使用过大
- SSIS - OLE DB Destination (Data Access - Fast load configuration)
- shell special characters within shell command lines
- Linux中的mktemp
- unity character controller
- Mrtg系统状态监控[CPU 内存 网卡流量 系统进程 硬盘空间 TCP连接数]
- 【discuz x3】pre_common_members表与pre_ucenter_members表中密码比较
- Oracle内连接、外连接、右外连接、全外连接小总结
- 【live555】FramedFilter粗浅分析
- 加密狗