SQL Server 2005 stored procedure fast in SSMS slow from VBA

Question

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'

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?

Thanks,

Steve

Answer 1

Use SQL Server Profiler

• Set up a trace on your database.
• Limit the trace only to the stored procedure object in question
• Limit to the username used by the VBA code

An introduction to SQL Server Profiler

In particular, check the SET options used by the connection and compare these with the defaults used when running the stored procedure in SSMS.

I have come across scenarios before where the SET options were different between calling code and within SSMS and the difference in performance was HUGE.

Answer 2

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

In reply to the comments on the original question

Are you using the exact same parameter values?

Yes exactly the same.

How much data is being returned (roughly) - number of rows and columns (and are any of them particularly big)?

Under 200 rows, perhaps 15 fields mostly ints with a couple of 20 character varchars.

Can you run SQL profiler and confirm if the sql is the issue or the remains of the macro in excel ?

The SQL is pretty ugly, as is the underlying database schema, and unfortunately is under NDA so I can't post it. If the query were the issue then wouldn't it be slow in management studio too though?

Answer 3

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

The answers say it's about parameter sniffing and how that affects which execution plan is used. The answers there specifically mentions the arithabort set option and how that affects the selection of plan.

Now I just need to find out how to change the set options from VBA...

Finally thanks to this forum entry on social.msdn.com i managed to get it right. First, set multiple connections to false:

connectionObject.Properties("Multiple Connections") = False

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

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 4

When using SQL Profiler do you see the SQL appear straight away... then 6 minutes elapses before the data gets returned back...

or...

using SQL Profiler does it take 6 minutes before you see the SQL appear, which then it takes a few seconds to pass the data back to Excel.

Also, does the SQL you find in Profiler look exactly like what you running below. (Right down to format of the date as well as the '' surronding those dates)

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