VBA：更快的记录集连接
我有以下代码来从 SQL Server 2008 访问记录集。
Dim cnn As Object
Dim rs As Object
Dim strSQL As String
Set cnn = CreateObject("ADODB.Connection") 'ADO Connection
cnn.ConnectionString = "DRIVER=SQL Server;SERVER=" & dbServer & ";" & _
"Trusted_Connection=Yes;DATABASE=" & dbDatabase
cnn.Open
strSQL = "Select * from mytable"
Set rs = CreateObject("ADODB.Recordset")
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
在 rs.Open 上，需要一秒到三秒的时间。有没有更快的方法来访问记录集？
变量 dbServer 和 dbDatabase 在模块中定义为常量字符串。
I have the following code to access a recordset from SQL Server 2008.
Dim cnn As Object
Dim rs As Object
Dim strSQL As String
Set cnn = CreateObject("ADODB.Connection") 'ADO Connection
cnn.ConnectionString = "DRIVER=SQL Server;SERVER=" & dbServer & ";" & _
"Trusted_Connection=Yes;DATABASE=" & dbDatabase
cnn.Open
strSQL = "Select * from mytable"
Set rs = CreateObject("ADODB.Recordset")
rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
On rs.Open it takes from one second to three seconds. Is there a faster way to access the recordset?
The variables dbServer and dbDatabase are defined in a module as a constant string.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
假设 mytable 是基表而不是 VIEW，则 SQL 尽可能简单，即没有优化的余地。
如果您要在 VBA 代码中将整个表内容进行进一步的 RBAR（“逐行排列”= 贬义）处理，那么请考虑使用更复杂的 SQL 将过程逻辑重写为“基于集合”范例，例如添加
WHERE子句将是一个好的开始。如果确实需要把整个表的内容都带过来，而且表比较大的话，会需要比较长的时间。同样，您的代码中没有优化的余地。
您当前正在执行同步提取，这意味着 VBA 代码的执行将在
rs.Open行等待，直到提取完成。对用户的影响将是应用程序看起来冻结，甚至可能崩溃。另一种方法是使用异步获取。重新定位您的代码类模块，将记录集声明为
WIthEvents（需要重新定位到模块级别），并在打开记录集之前指定adAsyncFetch选项（您可能需要稍微改变你的方法来适应这一点）。这将导致请求发送后立即继续执行 rs.Open（因此您需要将关闭连接/记录集的任何代码重新定位到单独的子过程）。然后，您可以使用FetchProgress和FetchComplete事件向应用程序中的最终用户提供反馈。有关更多详细信息，请参阅这篇 MSDN 文章。我从未能够从 Access 引擎/提供程序中获得任何有意义的“完成百分比”样式的进度。但您至少可以向用户显示字幕样式的进度条，以确保他们正在获取数据。他们仍然能够与 UI 交互（例如单击控件）并确信应用程序没有崩溃。
Assuming
mytableis a base table rather than a VIEW, the SQL is as trivial as is possible i.e. there is no scope for optimization.If you are bringing the entire table contents over to do further RBAR ("row by agonizing row" = pejorative) processing in VBA code then consider rewriting your procedural logic as a "set-based" paradigm using more complex SQL e.g. adding a
WHEREclause would be a good start.If you do need to bring the entire table contents over and the table is relatively large then it will take a relatively long time. Again, there is no scope in your code for optimization.
You are currently performing a synchronous fetch, meaning execution of your VBA code will wait at the
rs.Openline until the fetch is complete. The effect for your users will be that the application appears frozen, possibly crashed.An alternative approach is to use an asynchronous fetch. Relocate your code class module, declare the recordset as
WIthEvents(needs to be relocated to module level) and specify theadAsyncFetchoption on the recordset before opening it (you may need to alter your approach slightly to accommodate this). This will cause execution to continue past thers.Openas soon as the request has been sent (so you will need to relocate any code that closes the connection/recordset to a separate sub procedure). You can then use theFetchProgressandFetchCompleteevents to give feedback to your end users in the application. For more details, see this MSDN article.I've never been able to get any meaningful 'percent completed' style progress out of the Access engine/provider. But you can at least show a marquee style progress bar to users to reassure them that data is being fetched. They will be able to still interact with the UI (e.g. click on controls) and be reassured that the application has not crashed.
您有 2 个操作：
集 延迟在哪里？
您可以在两个打开语句之前添加 Debug.Print Now() 行，并在记录集打开之后添加另一行。
如果打开连接是瓶颈，请查看使用本机客户端提供程序是否可以加快速度。 www.connectionstrings.com：SQL Server Native Client 10.0 OLE DB 提供程序
如果打开记录集是瓶颈，你有多种选择。如果您实际上不需要动态/可编辑记录集，请使用 adOpenStatic 而不是 adOpenKeyset。您可以尝试 CommandTypeEnum adCmdTable 打开表，而不是使用返回所有内容的 SELECT 语句。或者使用 SELECT 语句，包含显式字段列表而不是“*”...因为“*”需要额外访问服务器才能确定它代表哪些字段。 （尽管这可能不是一个大瓶颈。）您还可以尝试使用 WHERE 子句根据主键值选择单个记录。
You have 2 operations:
Where is the delay?
You could add line for
Debug.Print Now()immediately before the two open statements, and another after the recordset open.If opening the connection is the bottleneck, see whether using the native client provider speeds things up. www.connectionstrings.com: SQL Server Native Client 10.0 OLE DB Provider
If opening the recordset is the bottleneck, you have several options. If you don't actually need a dynamic/editable recordset, use adOpenStatic instead of adOpenKeyset. You could try CommandTypeEnum adCmdTable to open the table instead of using a SELECT statement which returns everything. Or with the SELECT statement, include an explicit field list instead of "*" ... because "*" requires an extra trip to the server to figure out which fields it represents. (Although that's probably not a big bottleneck.) And you could also try a WHERE clause to select a single record based on a primary key value.