The number of index values that are stored in the database by a single execution of the bulk import SSIS package can be changed. By default, this is 200,000. Performance is improved when the BulkImportThreshold value is set to a high value and there is still memory to perform the operation without any swapping being necessary, as this harms performance significantly.
The value set for the BulkImportThreshold is not a hard limit; it is a threshold used by DataStoreDSX when calculating how best to divide up a large file.
Reducing the number from 200,000 reduces the amount of memory required by the SSIS package, improving performance; the trade-off is that more SSIS executions are necessary, which then starts to reduce performance again if the threshold is set too low. On a server with a very large amount of memory, increasing the BulkImportThreshold reduces the number of SSIS executions required to store a large file.
The configuration is set in HitecLabs.DataStore.DataStoreService.exe.config, which is located in the same directory as the DataStore service executable.
- In the <configSections> block at the top of the configuration file, in the <sectionGroup name="applicationSettings" ...> block, look for the following entry.
(If it does not exist, add it.)
<section name="HitecLabs.DataStore.Services.Database.DataItemSettings" type="System.Configuration.ClientSettingsSection, System, Version=220.127.116.11, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false"/>
- In the <applicationSettings> section, look for the following section. (If it does not exist, add it.)
- Within the <HitecLabs.DataStore.Services.Database.DataItemSettings> section, add:
<setting name="BulkImportValueThreshold" serializeAs="String">
replacing 200000 with the threshold that you want to set.
- Save the file.
- After modifying this configuration, the service must be restarted for the changes to take effect.