Setting Batch Data Cleansing option may Improve data cleansing performance.
You can choose to split the INSERT statement up in batches during data cleansing, i.e. when copying data from the transformation view for a table to the valid table. This saves log space on the SQL Server, which provides for better performance on large tables with 100,000s or millions of rows.
To enable batch data cleansing, follow the steps below.
- Right-click the table where batch data cleansing is to be performed and the select Table settings.
- Click the Performance tab and select Enable batch data cleansing.
- (Optional) Enter the number of records you would like each batch to contain in Batch size. The default is 100,000.
- Click OK.
Note that the optimal batch size may require some evaluation before it is apparent. Execute the batch data cleansing process and monitor the results to see if any adjustments should be made to the batch size.
The following community post contains useful information regarding best practices for batch data cleansing.
The optimal batch size is determined by the amount of memory available on the server to support the batch data cleansing process. Selecting the largest batch size that the available memory can support will generally be the optimal batch size.
