c# - Speed up insert mdb

Question

Is there a way to speed up inserts to a mdb?

 using (StreamReader sr = new StreamReader(_localDir + "" + _filename))
  while ((line = sr.ReadLine()) != null)
{
   //sanitize the data
}

This takes about 20sec for ~2mil records from a csv but when I add in the mdb insert I can barely get 10,000 records in 10min, so you can see it'll take forever

 using (StreamReader sr = new StreamReader(_localDir + "" + _filename))
 while ((line = sr.ReadLine()) != null)
{
//sanitize the data
using (OleDbConnection con = new OleDbConnection(_conStr))
 using (OleDbCommand cmd = new OleDbCommand())
 cmd.Parameters.AddWithValue...//I have 22 params
cmd.ExecuteNonQuery();

}

Is there a better way? Connection pooling? threading? Here is my constr Provider=Microsoft.Jet.OLEDB.4.0;Data Source=mypath;Jet OLEDB:Engine Type=5"

Regards

_Eric

Answer 1

Is it possible for you to use a query that inserts directly from csv? For example:

SELECT ID,Field1 INTO NewTable 
FROM [Text;HDR=YES;FMT=Delimited;IMEX=2;DATABASE=C:Docs].Some.CSV

You can use something similar with non-standard delimiters, but you will need a Schema.ini file in the same directory as the file to be imported. It need only contain:

[tempImportfile.csv]
TextDelimiter='

You will have to alter the connect string slightly, this seems to work:

Text;HDR=YES;FMT=Delimited;DATABASE=C:Docs