SQL Server 2005 stored procedure slows down in SSMS with VBA

Question

I have a stored procedure that when launched from SQL Server Management Studio sequentially takes 5 seconds to start when it is called like this.

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

When calling from an Excel spreadsheet through VBA, it takes 6 minutes plus (not counting the time taken to copy the recordset to the worksheet. VBA is nothing just using an ADO connection to return the recordset. Unfortunately, the Excel approach is a client requirement, from which I still can not get rid.

 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 know why this will happen or how I can start tracking what is happening?

Thanks,

Steve

Answer 1

All you need to know about this topic: Slow in application, fast in SSMS? Understanding the Sacraments of Performance

Answer 2

I think I have the same problem as Steve Homer. In addition to this SO question, I also found this topic at eggheadcafe.com. Very slow SP execution when using .net - very fast in Management Studio - totico

The answers talk about parametric sniffing and how this affects the execution plan. The answers specifically mention the arithabort set parameter and how this affects the choice of plan.

Now I just need to figure out how to change the dialing options from VBA ...

Finally, thanks to this forum page on social.msdn.com, I was able to fix this. First set some connections to false:

 connectionObject.Properties("Multiple Connections") = False 

and then use the following function in your connection to set arithabort to ...

 Private Sub OptionSet(ByRef cnn As adodb.Connection) Dim cmd As adodb.Command Set cmd = New adodb.Command With cmd Set .ActiveConnection = cnn .CommandType = adodb.CommandTypeEnum.adCmdText .CommandText = "set arithabort on" Call .Execute End With Set cmd = Nothing End Sub 

Answer 3

Use SQL Server Profiler

• Set up tracing in your database.
• Limit tracing only to a stored procedure object
• Limit username used by VBA code

Introduction to SQL Server Profiler

In particular, check the SET Parameters used by the connection and compare them with the default values ​​used when starting the stored procedure in SSMS.

I came across scripts before when the SET options were different between the call code and inside the SSMS, and the performance difference was HUGE.

Answer 4

Thanks, I'll take a look at the trace tools.

In response to comments on the original question

Do you use the same parameter values?

Yes, exactly the same.

How much data is returned (approximately) - the number of rows and columns (and any of them are especially large)?

Less than 200 lines, possibly 15 fields, mostly with several 20-character varchars.

Is it possible to run the SQL profiler and confirm if sql is a problem or macro residuals in excel?

SQL is pretty ugly, like the underlying database schema, and unfortunately is under the NDA, so I can't post it. If the issue was a problem, wouldn't it be too slow in a management studio?