Well, I did some Improvements on my code, et voila, the bulkcopy routine (without changes!) runs faster than ever (about 6 seconds for 11 MB data in 15000 rows)! Conclusion: Update your code bevore complaining ;-)
Seriously, that did not change my mind. SQL performance is poor, hence the insertion takes 6 seconds, but loading and parsing the csv takes no time at all.
1. Use
OleDb
over
ODBC
like:
OdbcConnection, OdbcCommand, OdbcDataReader
OleDbConnection, OleDbCommand, OleDbDataReader
2. Use
ACE
over
Jet
for 64 bit support - install from redistributable if neccessary, search for
AccessDatabaseEngine_X64
for 64 bit version
3. and coose the right connection string like:
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" CsvDirectory.Trim() @";Extended Properties=""text;HDR=yes;FMT=Delimited""
"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" CsvDirectory.Trim() ";Extensions=asc,csv,tab,txt;Persist Security Info=False";
"Driver={Microsoft Access Text Driver (*.txt, *.csv)};Extensions=asc,csv,tab,txt;Persist Security Info=False;Dbq=" CsvDirectory.Trim()
3. now we are able to take it into x64 architecture
4. I feel like I forgot something...