excel - Excel VBA ADODB 访问数据库查找 - 减少查询时间
问题描述
我编写了一些 VBA 代码来查询访问数据库并将该代码拉入单元格中。它可以工作,但是非常慢,主要取决于我编写它的方式,但是我正在努力加快速度。
第一个和第二个查询是相同的,除了第二个查询有 1 个月的偏移量。
我怎样才能加快速度?
第一个问题是我似乎必须在每个循环中连接和关闭数据库。如果我尝试在同一个数据库连接中运行这两个查询,我会收到错误消息。
第二个问题是加入似乎很慢。
' FIRST MONTH QUERY
db.Connect
db.RunGetResults ("SELECT data.*, monthdata.VAL, monthdata.MONTHVAL, monthdata.GREEN, monthdata.RED, monthdata.RAG, monthdata.CREATOR FROM data LEFT JOIN monthdata ON data.UID = monthdata.DATAUID WHERE [UID] = '" & (IDcell) & "' AND [MONTHVAL] = #" & Format(Range("multidate"), "mm/dd/yyyy") & "#")
'Debug.Print db.Recordset.RecordCount
irow = IDcell.Row
Do Until db.Recordset.EOF
icol = 2
For Each ifield In db.Recordset.Fields
Sheet3.Cells(irow, icol) = ifield.Value
icol = icol + 1
Next
i = i + 1
progress i
db.Recordset.MoveNext
If db.Recordset.EOF = True Then
Else
End If
Loop
End If
Next
db.Disconnect
GoTo 69
' SECOND MONTH QUERY
For Each IDcell In Rng
If IDcell <> "" Then
db.Connect
'Application.Goto Reference:="month2"
db.RunGetResults ("SELECT monthdata.VAL, monthdata.MONTHVAL, monthdata.GREEN, monthdata.RED, monthdata.RAG, monthdata.CREATOR FROM data LEFT JOIN monthdata ON data.UID = monthdata.DATAUID WHERE [UID] = '" & (IDcell) & "' AND [MONTHVAL] = #" & Format((month2), "mm/dd/yyyy") & "#")
'Debug.Print db.Recordset.RecordCount
irow = IDcell.Row
Do Until db.Recordset.EOF
icol = 18
For Each ifield In db.Recordset.Fields
Sheet3.Cells(irow, icol) = ifield.Value
icol = icol + 1
Next
i = i + 1
progress i
db.Recordset.MoveNext
If db.Recordset.EOF = True Then
Else
End If
Loop
End If
Next
db.Disconnect
编辑
根据要求,这是 db 对象
Public WithEvents Connection As ADODB.Connection
Public WithEvents Recordset As ADODB.Recordset
Public Command As New ADODB.Command
Public FilePath
Public Password
Public Function Connect()
If Connection.State = 1 Then Disconnect
AccessConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & FilePath & "';Jet OLEDB:Database Password='" & Password & "';"
Connection.ConnectionString = AccessConnect
Connection.CursorLocation = adUseClient
Connection.mode = adModeReadWrite
Connection.Open
End Function
Public Function Disconnect()
On Error Resume Next
Connection.Close
On Error GoTo 0
End Function
Public Function RunGetResults(qryString)
sqlQuery = qryString
Recordset.Open sqlQuery, Connection, adOpenKeyset, adLockOptimistic
End Function
Public Function Execute(qryString)
Connection.Execute (qryString)
End Function
Public Function Esc(eString)
eString = Replace(eString, "'", "''")
Esc = eString
End Function
Private Sub Class_Initialize()
Set Connection = New ADODB.Connection
Set Recordset = New ADODB.Recordset
Password = "xxxxx"
End Sub
解决方案
我建议仅adOpenKeyset, adLockOptimistic
在需要时使用。
锁定和闩锁会产生开销。如果您只是要阅读,请使用adOpenStatic, adLockReadOnly
. 这不会锁定记录,并且在记录被删除时不需要持续连接来接收更新。
你的整个数据库对象对我来说似乎很奇怪。您正在声明记录集和与事件的连接,但不监听任何事件。这一切都会产生一些开销。
您也可以断开记录集,但如果您使用带有只读锁的静态类型记录集,这可能不会有太大区别。
推荐阅读
- javascript - 如何从字符串中创建具有数组属性的对象?
- pygame - 哪些 pygame 模块具有 init 属性?我想手动初始化模块而不是使用 pygame.init()
- mysql - MySql 日期时间格式更改
- python - Command errored out with exit status 1: Building wheel for pysph (setup.py) ... error
- ffmpeg - 使用 ffmpeg 将 heif 文件转换为 png/jpg
- elasticsearch - Kibana painless script to count the json list size
- javascript - Palindrome Index Hackerrank with JavaScript
- jobs - 测试数据生成的作业可以在 HCL OneTest Data 中保留多长时间?用户可以为作业配置此时间段吗?
- google-apps-script - 无法在谷歌脚本中连接
- php - 如果数据存在,PHP隐藏一个按钮