加快插入MDB [英] Speed up insert mdb

问题描述

有没有一种方法，以加快插入到一个MDB？

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
}

这大约需要20秒的〜2密耳的记录从CSV
，但是当我在MDB插入我加勉强可以得到10000条记录中10分钟，所以你可以看到它会采取永远

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();

}

有没有更好的办法？连接池？线程？
这里是我的构造提供商= Microsoft.Jet.OLEDB.4.0;数据源= mypath中;喷射OLEDB：发动机
类型= 5

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"

问候

_Eric

推荐答案

时，你有可能使用查询直接从CSV中插入呢？例如：

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

您可以使用非标准分隔符类似的东西，但你需要在同一个目录中的Schema.ini文件如要导入的文件，因此只需要包含以下内容：

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\

这篇关于加快插入MDB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！