Excel ListObjects.Add() via OLEDB Very Slow

Jazattack

New Member
Joined
Aug 16, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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:

VBA 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)
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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..


VBA Code:
SET ARITHABORT ON

 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,539
Messages
6,179,415
Members
452,912
Latest member
alicemil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top