Slow running SQL via ADO connection in excel

[Azza]

Hi,

I have an interactive report which uses a combination of SQL statements, VB
and other excel functions to return various stats driven by what the user
sets as perameters.
The SQL statements are stored in excel on a worksheet and i have written an
ADO connection module to create a connection to the database and execute the
SQL. The results are then returned into the same excel workbook. The SQL
works and all results are returned however it seems a little slow.

I have tried running the SQL in access itself and the total duration of the
4 queries is approx 1minute but running through excel, the SQL execution time
is 3minutes.

Does anybody have any idea why executing SQL through excel would take almost
triple the time than directly in access?
Please could somebody advise how i can get round this and speed the SQL
execution up?

All help greatly appreciated.

 

[Martin Fishlock]

Hi Azza,

It maybe the way the sql is working and the data that is fetched from the
database via the ADO pipe.

Try using queries in access rather than sql in excel. I know that you want
dynamic queries but it will see if the that is the problem.

If it solves it you then know that you need to refine the data extraction
routine.

One way is to create dynamic queries in access from excel run the queries
and then extract the results to excel.

just osme ideas for you to try.

And I would be interested to see other answers.

 

[Azza]

Hi Martin,

Thanks for your response. Sorry for the time lag, i've had to put this on
the back burner a bit at work but am picking it back up again. I took your
advise and have written out all my stored procedures in access but i'm ahving
a connection problem.

I'm using the code below to execute the stored procedures and pass values
into the SP's parameters:

Public Sub tester()
Dim adoConnection As New ADODB.Connection
Dim rsResults As ADODB.Recordset
Dim SQL_hierarchy_level As String
Dim SQL_selection_name As String
Dim SQL_SDate As String
Dim SQL_EDate As String
Dim SP_Name As String
Dim adoCommand As ADODB.Command
Dim prmSDate As ADODB.Parameter
Dim prmEDate As ADODB.Parameter
Dim prmSelectName As ADODB.Parameter

Set rsResults = New ADODB.Recordset

SQL_hierarchy_level = Sheets("MainSQL").Range("C2")
SQL_hierarchy_level = SQL_hierarchy_level & "_i"
SQL_selection_name = Sheets("MainSQL").Range("C3")
SQL_SDate = Sheets("MainSQL").Range("C5")
SQL_EDate = Sheets("MainSQL").Range("C6")
SP_Name = Sheets("MainSQL").Range("C31")

Call setDNSConnection(adoConnection, 1)
Set adoCommand = New ADODB.Command
adoCommand.CommandText = SP_Name
adoCommand.CommandType = adCmdStoredProc

Set prmSDate = adoCommand.CreateParameter("SDate_i", adVarChar,
adParamInput, 8)
adoCommand.Parameters.Append prmSDate
prmSDate.Value = (SQL_SDate)

Set prmEDate = adoCommand.CreateParameter("EDate_i", adVarChar,
adParamInput, 8)
adoCommand.Parameters.Append prmEDate
prmEDate.Value = (SQL_EDate)

Set prmSelectName = adoCommand.CreateParameter(SQL_hierarchy_level,
adVarChar, adParamInput, 200)
adoCommand.Parameters.Append prmSelectName
prmSelectName.Value = (SQL_selection_name)

Set rsResults = adoCommand.Execute

ThisWorkbook.Worksheets("DATA_Report").Cells.Clear
ThisWorkbook.Worksheets("DATA_Report").Range("A2").CopyFromRecordset rsResults

rsResults.Close
Set rsResults = Nothing

adoConnection.Close
Set adoConnection = Nothing
End Sub


The connection code i'm using is the same as before when i wasn't using
stored procedures. I've attached this below:

Function setDNSConnection(ByRef adoConnection As ADODB.Connection, _
intDNSSource As Integer)

Dim strConnectionString As String
Dim strDatabasePathAndName As String

If intDNSSource > 0 And intDNSSource <= 2 Then
Select Case intDNSSource

Case 1
strDatabasePathAndName = strDatabaseLocationPath1 &
strDatabaseName1
With adoConnection
.Provider = "Microsoft.Jet.OLEDB.4.0"
'.CursorLocation = adUseClient
.Open "Data Source=" & strDatabasePathAndName
End With


Case 2
strDatabasePathAndName = strDatabaseLocationPath1 &
strDatabaseName1
With adoConnection
.Provider = "Microsoft.Jet.OLEDB.4.0"
.CursorLocation = adUseClient
.Open "Data Source=" & strDatabasePathAndName
End With


End Select
End If
End Function


When i execute the tester code, it bugs out at the executing ado command
stage. The message reads "Run-Time error '3709': the connection cannot be
used to perform this operation. It is either closed or invalid in this
context"

Do you or anybody else have any ideas?