如何提高 MS Access INSERT 性能
我有一个 C++ 程序，它使用 OLEDBConnection 将大约一百万条记录插入到 MS Access DB 中。为此，我运行了 INSERT INTO 查询一百万次，以便插入需要相当长时间的记录。
数据在程序中以数组的形式生成，是否有其他方法可以将数据一步加载到数据库中以提高性能？
谢谢！
我当前使用循环插入记录
for (int i = 0; i < populationSize; i++){
insertSQL = "INSERT INTO [" + pTableName + "] (" + columnsName + ") VALUES (" + columnsValue[i] + ");";`
outputDBConn->runSQLEdit(insertSQL);
}
运行 SQL 查询的方法
void DBConnector::runSQLEdit(String^ query){
SQLCMD = gcnew OleDbCommand( query, dbConnection );
SQLCMD->CommandTimeout = 30;
dbConnection->Open();
SQLCMD->ExecuteNonQuery();
dbConnection->Close();
}
I have a C++ program that insert about a million of records into MS Access DB using OLEDBConnection. To do that, I ran the INSERT INTO query a millions time in order to get the records inserted which take quite a long time.
The data is generated in the program in form of array, will that be any other way that i can load the data into database in one single step to improve the performance?
Thanks!
Loop i use to insert the records currently
for (int i = 0; i < populationSize; i++){
insertSQL = "INSERT INTO [" + pTableName + "] (" + columnsName + ") VALUES (" + columnsValue[i] + ");";`
outputDBConn->runSQLEdit(insertSQL);
}
Method that run the SQL query
void DBConnector::runSQLEdit(String^ query){
SQLCMD = gcnew OleDbCommand( query, dbConnection );
SQLCMD->CommandTimeout = 30;
dbConnection->Open();
SQLCMD->ExecuteNonQuery();
dbConnection->Close();
}
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
为每个插入语句打开/关闭连接似乎效率很低。
标准方法类似于：
更新：以下内容不适用于 MS Access。 Access 不支持插入多行 < em>来自文字。它仅支持从现有数据源插入多行。 （虽然这里是 " workabout” 可能会起作用。无论如何，最重要的事情可能是限制交易数量。）
还可以做的一件事是构建一个单个插入命令，一次添加多个记录。这可以通过多个语句或多记录插入（如果支持）来完成。它可能会或可能不会比上述速度快得多（取决于网络延迟和数据库引擎等其他因素），并且可能需要进行调整以适应数据库的限制（例如可能只一次可以处理几百条记录）。仅应在如上所述正确使用连接/事务后才考虑这一点。
如果我们已经制作了“批量插入”库/模块，我不会感到惊讶......并且我不使用 MS Access，所以我只能希望上述建议有所帮助:-)
快乐编码。
It seems very inefficient to open/close the connection for each insert statement.
The standard approach goes something like:
Update: The following does not apply to MS Access. Access does not support inserting multiple rows from a literal. It only supports inserting multiple rows from an existing data source. (Although here is a "workabout" that might work. In any case, the most important thing is likely limiting the number of transactions.)
One more thing that can be done is to build a single insert command that adds multiple records at once. This can be done with either multiple statements or a multi-record insert (if supported). It may or may not be significantly faster than just the above (depends upon other factors like network latency and database engine) and will likely need to be adapted to fit within the restrictions of the database (e.g. might only be feasible for a few hundred records at once). This should only be considered after proper connection/transaction usage as described above.
I wouldn't be surprised if there we already-made "bulk insert" libraries/modules floating about... and I don't use MS Access so I can only hope that the above suggestions were helpful :-)
Happy coding.
不要对每个命令执行一次插入。
将您的代码更改为类似以下内容：
我不确定命令的最大缓冲区大小是多少，因此请进行一些检查，然后获取最佳值以在每个 X 插入时进行一些“中断”。
Don't do ONE insertion per command.
Change your code to something like, this:
I'm not sure what's the max buffer size of the command, so do some checks and then get the best value to do some "breaks" at every X inserts.