Project DescriptionAlternative approach to bulk insert delimited files when extreme performance is required.
Input: some large delimited file (GBs)
Operation: BULK INSERT
Destination: SQL Server table(s)
Method: Barbaric approach
Technology: SQLNCLI, Mem mapped files, Windows 2008, C++2008
Devs: welcome if you want to play, there are three parts:
APP_SqlSlicedInserter
DLL
FileSourceReaderx (where we fast aquire data for insert)
DLL
SqlInserterx (where we fast insert data)
Usage:
APP
SqlSlicedInserter.exe DLLFileSourceReader
x.dll "InputParam0~InputParam1~...", DLLSqlInserter_x.dll ConnectionString Destination
Sample:
- APPSqlSlicedInserter.exe DLLFileSourceReaderSliced.dll "file=c:\test.csv~rowdelim=\n~coldelim=;~slice=0~slicecount=8", DLLSqlInserter_BulkInsert.dll "Data Source=localhost;Initial Catalog=msdb;Integrated Security=True" "table=dbo.test"
- APPSqlSlicedInserter.exe DLLFileSourceReaderSliced.dll "file=c:\test.csv~rowdelim=\n~coldelim=;~slice=1~slicecount=8", DLLSqlInserter_BulkInsert.dll "Data Source=localhost;Initial Catalog=msdb;Integrated Security=True" "table=dbo.test"
- APPSqlSlicedInserter.exe DLLFileSourceReaderSliced.dll "file=c:\test.csv~rowdelim=\n~coldelim=;~slice=2~slicecount=8", DLLSqlInserter_BulkInsert.dll "Data Source=localhost;Initial Catalog=msdb;Integrated Security=True" "table=dbo.test"
- APPSqlSlicedInserter.exe DLLFileSourceReaderSliced.dll "file=c:\test.csv~rowdelim=\n~coldelim=;~slice=3~slicecount=8", DLLSqlInserter_BulkInsert.dll "Data Source=localhost;Initial Catalog=msdb;Integrated Security=True" "table=dbo.test"
- APPSqlSlicedInserter.exe DLLFileSourceReaderSliced.dll "file=c:\test.csv~rowdelim=\n~coldelim=;~slice=4~slicecount=8", DLLSqlInserter_BulkInsert.dll "Data Source=localhost;Initial Catalog=msdb;Integrated Security=True" "table=dbo.test"
- APPSqlSlicedInserter.exe DLLFileSourceReaderSliced.dll "file=c:\test.csv~rowdelim=\n~coldelim=;~slice=5~slicecount=8", DLLSqlInserter_BulkInsert.dll "Data Source=localhost;Initial Catalog=msdb;Integrated Security=True" "table=dbo.test"
- APPSqlSlicedInserter.exe DLLFileSourceReaderSliced.dll "file=c:\test.csv~rowdelim=\n~coldelim=;~slice=6~slicecount=8", DLLSqlInserter_BulkInsert.dll "Data Source=localhost;Initial Catalog=msdb;Integrated Security=True" "table=dbo.test"
- APPSqlSlicedInserter.exe DLLFileSourceReaderSliced.dll "file=c:\test.csv~rowdelim=\n~coldelim=;~slice=7~slicecount=8", DLLSqlInserter_BulkInsert.dll "Data Source=localhost;Initial Catalog=msdb;Integrated Security=True" "table=dbo.test"