Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow running SQL via ADO connection in excel
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow running SQL via ADO connection in excel
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. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Azza" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Slow running SQL via ADO connection in excel
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? "Martin Fishlock" wrote: 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. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Azza" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 07 running slow | New Users to Excel | |||
Excel performance running slow | Excel Discussion (Misc queries) | |||
excel running slow when saving | Excel Discussion (Misc queries) | |||
excel macros running slow with XP | Excel Programming | |||
Excel running really slow on my machine | Excel Programming |