使用 ADO.NET / OleDb 将记录插入 Jet 数据库非常慢
我正在尝试通过 ADO.NET / OleDb 将大量记录插入到 Jet (Access) 数据库中。它运行缓慢得令人痛苦。速度缓慢主要是由于 DbDataAdapter.Update 方法。用微软的话说，
...这些语句不是作为批处理执行的；每行都单独更新。
这是一个真正的 WTF，因为我的数据库应用程序的性能比使用旧 ADO 或 DAO 的 VB6 中的等效代码（带有 AddNew / Update 循环的 Recordset）慢了大约 30 倍。
SqlClient 提供程序具有 SqlBulkCopy 类；有与 OleDb 等效的东西吗？
甚至能够改变其写入缓存行为也可能有所帮助。 （即，插入每一行后不要刷新缓存！）
我能做些什么吗？或者 ADO.NET 对于 Jet 来说是否已损坏？
*编辑添加：这是我的代码的精简版本，使用精简的测试数据库。
首先，VBA/ADO 版本 (Access 2003)：
Dim con As ADODB.Connection
Set con = CurrentProject.Connection
con.Execute "DELETE * FROM tblTest", , adCmdText Or adExecuteNoRecords
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "tblTest", con, , adLockPessimistic
Dim i&, t!
t = Timer
For i = 1 To 10000
rs.AddNew
rs!mainKey = i
rs!testColumn = Rnd * 100
rs.Update
Next
rs.Close
Debug.Print "time to add 10000 (by ADO) " & Timer - t
输出： time to add 10000 (by ADO) 0.296875
现在 ADO.NET 版本 (VB.NET 2010)：
Dim sProvider As String = "PROVIDER = Microsoft.Jet.OLEDB.4.0;"
Dim sDataSource As String = "Data Source = 'C:\test.mdb'"
Dim connection As New OleDbConnection(sProvider & sDataSource)
connection.Open()
Dim q As New OleDbCommand("DELETE * FROM tblTest", connection)
q.ExecuteNonQuery()
Dim ds As New DataSet
Dim selectCmd As OleDbCommand = connection.CreateCommand()
selectCmd.CommandText = "SELECT * FROM tblTest"
Dim da As New OleDbDataAdapter(selectCmd)
da.Fill(ds, "tblTest")
Dim theTable As DataTable = ds.Tables("tblTest")
For i As Integer = 1 To 10000
Dim row = theTable.NewRow()
row.Item("mainKey") = i
row.Item("testColumn") = Rnd() * 100
theTable.Rows.Add(row)
Next i
Dim t! : t = Microsoft.VisualBasic.Timer
Dim cb As New OleDbCommandBuilder(da)
da.Update(ds, "tblTest")
Debug.Print("time to add 10000 (by ADO.NET): " & Microsoft.VisualBasic.Timer - t)
connection.Close()
输出： time to添加 10000（通过 ADO.NET）：5.859375
I'm trying to insert a lot of records into a Jet (Access) database via ADO.NET / OleDb. It is running painfully slowly. The slowness is mainly due to the DbDataAdapter.Update method. In Microsoft's words,
...these statements are not performed as a batch process; each row is updated individually.
This is a real WTF as my database application gives about 30 times slower performance than the equivalent code in VB6 using old ADO or DAO (a Recordset with an AddNew / Update loop).
The SqlClient provider has the SqlBulkCopy class; is there anything equivalent for OleDb?
Even being able to change its write-caching behaviour might help. (ie. don't flush the cache after each row is inserted!)
Is there anything I can do, or is ADO.NET just broken for Jet?
*Edited to add: Here's a cut down version of my code, using a cut down test database.
First, the VBA/ADO version (Access 2003):
Dim con As ADODB.Connection
Set con = CurrentProject.Connection
con.Execute "DELETE * FROM tblTest", , adCmdText Or adExecuteNoRecords
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "tblTest", con, , adLockPessimistic
Dim i&, t!
t = Timer
For i = 1 To 10000
rs.AddNew
rs!mainKey = i
rs!testColumn = Rnd * 100
rs.Update
Next
rs.Close
Debug.Print "time to add 10000 (by ADO) " & Timer - t
Output: time to add 10000 (by ADO) 0.296875
Now the ADO.NET version (VB.NET 2010):
Dim sProvider As String = "PROVIDER = Microsoft.Jet.OLEDB.4.0;"
Dim sDataSource As String = "Data Source = 'C:\test.mdb'"
Dim connection As New OleDbConnection(sProvider & sDataSource)
connection.Open()
Dim q As New OleDbCommand("DELETE * FROM tblTest", connection)
q.ExecuteNonQuery()
Dim ds As New DataSet
Dim selectCmd As OleDbCommand = connection.CreateCommand()
selectCmd.CommandText = "SELECT * FROM tblTest"
Dim da As New OleDbDataAdapter(selectCmd)
da.Fill(ds, "tblTest")
Dim theTable As DataTable = ds.Tables("tblTest")
For i As Integer = 1 To 10000
Dim row = theTable.NewRow()
row.Item("mainKey") = i
row.Item("testColumn") = Rnd() * 100
theTable.Rows.Add(row)
Next i
Dim t! : t = Microsoft.VisualBasic.Timer
Dim cb As New OleDbCommandBuilder(da)
da.Update(ds, "tblTest")
Debug.Print("time to add 10000 (by ADO.NET): " & Microsoft.VisualBasic.Timer - t)
connection.Close()
Output: time to add 10000 (by ADO.NET): 5.859375
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
确保调用该方法时连接已打开。如果在调用更新方法之前关闭连接（我实际上在某些示例代码中看到了这一点），则更新方法可能会尝试以非最佳方式打开连接。
如果连接未池化，则在 Jet 中打开连接可能会非常慢。您可能需要添加 OLE DB SERVICES = -1 以确保连接已池化。
Make sure that the connection is open when the method is called. If the connection is closed before the update method is called (I actually saw that in some example code) the update method may attempt to open the connection in a non-optimal way.
Opening connections in Jet can be very slow if the connection is not pooled. You may need to add OLE DB SERVICES = -1 to make sure the connection is pooled.
MSAccess 磁盘写入速度慢
Slow MSAccess disk writing
您真的想用随机值填充表吗？如果是这样，有更快的方法来执行此操作（使用基于现有表或要附加到的表的 INSERT，以便您可以多次运行它并快速达到所需的记录数）。
一般来说，SQL INSERT 比一次添加一条记录快一个数量级。如果您必须按照目前的方式进行操作，那么您可能会考虑是否可以通过 ADO/ADO.NET 使用 Jet/ACE 事务。我不知道这是否可用。如果不是，并且假设 COM 是一个选项，您应该考虑只使用 DAO，这样您就可以使用 Jet/ACE 事务，这会将写入延迟到最后（就像发布一批）。
我不是 ADO 专家，但我记得经典 ADO 中也有一些批处理函数，因此您也可以考虑对此进行研究。
Are you really trying to populate a table with random values? If so, there are faster ways to do it (using an INSERT based on an existing table, or on the table you're appending to, so you can run it multiple times and quickly reach the number of desired records).
In general, a SQL INSERT is going to be an order of magnitude faster than adding one record at a time. If you have to do it the way you're doing it, then you might look into whether or not you can use a Jet/ACE transaction via ADO/ADO.NET. I haven't a clue whether that's available or not. If it's not, and assuming COM is an option, you should consider just using DAO so you could use Jet/ACE transactions, which will delay the write to the very end (like posting a batch).
I'm no ADO maven, but I recally there being some batch functions in Classic ADO, too, so you might consider investigating that, as well.