SQL Server 2005 存储过程在 SSMS 中快速从 VBA 变慢 [英] SQL Server 2005 stored procedure fast in SSMS slow from VBA

问题描述

我有一个存储过程，从 SQL Server Management Studio 运行时，像这样调用时，它始终需要 5 秒才能运行.

I have a stored procedure which when run from SQL Server Management Studio consistently takes 5 seconds to run when called like this.

exec dbo.MyStoredProc  '2009-04-30 00:00:00', '2009-04-30 20:00:00' 

当通过 VBA 从 excel 电子表格调用时，需要 6 分钟以上(不包括将记录集复制到工作表所花费的时间.VBA 只是使用 ADO 连接返回记录集没什么特别的.不幸的是，Excel 方法是一个我还无法摆脱的客户需求.

When called from an excel spreadsheet via VBA it takes 6 minutes plus (not including the time taken to copy the recordset to a sheet. The VBA is nothing fancy simply using an ADO connection to return a recordset. Unfortunately the Excel approach is a client requirement that I can't get rid of yet.

Public Function GenerateSQL(strQueryName As String) As Recordset
Dim rs As Recordset, cm As Command, dbsConn As Connection

Set dbsConn = New ADODB.Connection
dbsConn.Open Configuration.Range("ConnectionString")
Set cm = New ADODB.Command


With cm
    .CommandText = strQueryName
    .CommandType = adCmdStoredProc
    .CommandTimeout = 300
    .ActiveConnection = dbsConn

    Set rs = .Execute()

End With

Set GenerateSQL = rs
End Function

有谁知道为什么会发生这种情况，或者我如何开始追踪正在发生的事情?

Does anyone have any idea why this would happen or how I could begin to trace what is happening?

谢谢，

史蒂夫

推荐答案

关于这个主题你需要知道的一切:应用程序慢，SSMS 快?了解性能之谜

Everything you need to know about this topic: Slow in the Application, Fast in SSMS? Understanding Performance Mysteries

这篇关于SQL Server 2005 存储过程在 SSMS 中快速从 VBA 变慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！