Speed ​​up mdb insert

Question

Is there a way to speed up inserts in mdb?

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

It takes about 20 seconds for ~ 2 mm records from csv but when I add mdb to the insert, I can barely get 10,000 records in 10 minutes, so you can see that it takes 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 pool? multithreading? Here is my constr Provider = Microsoft.Jet.OLEDB.4.0; Data Source = mypath; Jet OLEDB: Engine Type = 5 "

Hi

_Eric

Answer 1

Is it possible to use a query that inserts directly from csv? For instance:

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

- , Schema.ini , , . :

[tempImportfile.csv]
TextDelimiter='

, , , :

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

Answer 2

, , . 1 / N N /.

Answer 3

Microsoft Jet Sql- (INSERT/UPDATE) . , , - Jet. , ( , , , , , , ) . ONCE ( ), , Sql (OleDbCommand), .

Answer 4

, , - . . . , . , .

Answer 5

: ( ) .NET/# OleDb DAO.