Excel ListObjects.Add() via OLEDB Very Slow

[Jazattack]

When executing a stored procedure in SQL Server it takes < 30 seconds.

When executing this stored procedure twice separately via 2 separate VBA buttons, once for MTD and once for YTD, separately, it takes ~45 seconds. This is acceptable. This is done via below code:

Sub GetMonthlyData()

Call GetSQLData(Monthly)

End Sub



Sub GetYearlyData()

Call GetSQLData(Yearly)

End Sub



Sub GetSQLData()

    With Sheets(OutPutSheet).ListObjects.Add(SourceType:=0, _

    Source:=Array("OLEDB;Provider=SQLOLEDB.1;" _

    , "Password= " & Password & " ;" _

    , "Persist Security Info=True;" _

    , "User ID= " & UserId & ";" _

    , "Data Source= " & Server & " ;" _

    , "Use Procedure for Prepare=0;" _

    , "Auto Translate=True;" _

    , "Packet Size=4096;" _

    , "Use Encryption for Data=False;" _

    , "Tag with column collation when possible=False;" _

    , "Initial Catalog= " & Database & " "), _

     Destination:=Worksheets(OutPutSheet).Range("$A$1")).QueryTable

    .CommandType = xlCmdSql

    .CommandText = SQLCommandString

    .ListObject.DisplayName = OutPutSheet & "DataDump"

    .Refresh BackgroundQuery:=False

    End With

End Sub

However, if I execute the actions of these 2 buttons back to back, the time taken blows out to 5-10 minutes. Ideally the end user only needs to press one button however there is significant delay when adding 2 list objects in the same execution.

This takes significantly longer than running both separately.

Call GetSQLData(Monthly)
Call GetSQLData(Yearly)

 

[Colo]

There is information about "Executing the stored procedures from SQL Server Management Studio(SSMS) runs fast, but from a web application(ASP), it runs slow.

SSMS sets ARITHABORT option ON as default but from the program, it's OFF. So need to set it ON something like..

SET ARITHABORT ON

Slow in the Application, Fast in SSMS?

 

[Jazattack]

I can't seem to edit but the stored procedure occasionally runs quickly and other times very slow. I don't think it's related to running both at the same time. Will do some investigation into ARITHABORT.