Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO performance
Folks,
I have a very interesting problem and need some tips. I am running an application with an Excel frontend and a remote SQL Server backend. My issue is peformance. This application runs sometime incredibly fast. I get my data which is about 10,000 records under 4 secs and at other times at 40 secs. Needless to say the latter is not acceptable by my clients. Details: 1. The SQL Server is 2005. While both runs are taking place the server has nothing else running on it. I run the Excel application locally while the server is hosted in another location. 2. The entire enviroment where the server is running is fixed. Nothing else takes place on the server while I am testing the application. 3. When I test the connection speed the upload speed from the server is 1MB/sec. That is also the maximum speed of the router. In other words, that is the exact speed of upload when the application is running fast. Here is the code that I am running: Private Const CN_SBS_Server_msC As String = "X.X.X.X" Private Const CN_SBS_Database_msC As String = "Development" Private Const CN_SBS_UserName_msC As String = "Plato" Private Const CN_SBS_Password_msC As String = "plato" Private Const CN_SBIS_Server_msC As String = "X.X.X.X" Private Const CN_SBIS_UserName_msC As String = "Plato" Private Const CN_SBIS_Password_msC As String = "plato" Function Get_Data_Markets(ImportToWB As Workbook, Optional WrkShtName As String = "Lists") As Boolean Dim rs As ADODB.Recordset, cmd As ADODB.Command, db_was_not_open_b As Boolean, rng_name$ Dim category_s$ Dim in_errhandler_b As Boolean Const Source_sC As String = "Get_Data_Markets()" On Error GoTo ErrHandler category_s = CStr(ImportToWB.Sheets("Params").Range("theCategor y").Value2) If MainDB_gCN Is Nothing Then OpenMainDB category_s: db_was_not_open_b = True Set cmd = New ADODB.Command cmd.ActiveConnection = MainDB_gCN cmd.CommandText = "qry_XLA_Markets_All" cmd.CommandType = adCmdStoredProc: cmd.NamedParameters = True cmd.Parameters.Append cmd.CreateParameter("@theCategory", adVarChar, adParamInput, 255, category_s) Set rs = New ADODB.Recordset rs.Open cmd, , adOpenStatic FillRangeFromRecordset "MarketsLst", ImportToWB, rs Get_Data_Markets = True CleanUp: On Error Resume Next If Not rs Is Nothing Then rs.Close: If db_was_not_open_b Then CloseMainDB If in_errhandler_b Then CentralErrorHandlerP2 Exit Function ErrHandler: If CentralErrorHandlerP1(Module_msC, Source_sC, Erl, , EntryPoint_b:=False) Then Stop: Resume in_errhandler_b = True: Get_Data_Markets = False: GoTo CleanUp End Function So what the heck is going on? What could be affecting the performance to that degree? Thank you for your suggestion. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO performance
Have you profiled the performance ? Eg: what is the variation in time to
returning the first record vs. time from first record to last ? How much data are you bringing back to Excel ? How are you dumping the records to the sheet ? You'll need to find out exactly where the performance variation is coming from before you've any chance of addressing it. Tim "Rebooot" wrote in message ... Folks, I have a very interesting problem and need some tips. I am running an application with an Excel frontend and a remote SQL Server backend. My issue is peformance. This application runs sometime incredibly fast. I get my data which is about 10,000 records under 4 secs and at other times at 40 secs. Needless to say the latter is not acceptable by my clients. Details: 1. The SQL Server is 2005. While both runs are taking place the server has nothing else running on it. I run the Excel application locally while the server is hosted in another location. 2. The entire enviroment where the server is running is fixed. Nothing else takes place on the server while I am testing the application. 3. When I test the connection speed the upload speed from the server is 1MB/sec. That is also the maximum speed of the router. In other words, that is the exact speed of upload when the application is running fast. Here is the code that I am running: Private Const CN_SBS_Server_msC As String = "X.X.X.X" Private Const CN_SBS_Database_msC As String = "Development" Private Const CN_SBS_UserName_msC As String = "Plato" Private Const CN_SBS_Password_msC As String = "plato" Private Const CN_SBIS_Server_msC As String = "X.X.X.X" Private Const CN_SBIS_UserName_msC As String = "Plato" Private Const CN_SBIS_Password_msC As String = "plato" Function Get_Data_Markets(ImportToWB As Workbook, Optional WrkShtName As String = "Lists") As Boolean Dim rs As ADODB.Recordset, cmd As ADODB.Command, db_was_not_open_b As Boolean, rng_name$ Dim category_s$ Dim in_errhandler_b As Boolean Const Source_sC As String = "Get_Data_Markets()" On Error GoTo ErrHandler category_s = CStr(ImportToWB.Sheets("Params").Range("theCategor y").Value2) If MainDB_gCN Is Nothing Then OpenMainDB category_s: db_was_not_open_b = True Set cmd = New ADODB.Command cmd.ActiveConnection = MainDB_gCN cmd.CommandText = "qry_XLA_Markets_All" cmd.CommandType = adCmdStoredProc: cmd.NamedParameters = True cmd.Parameters.Append cmd.CreateParameter("@theCategory", adVarChar, adParamInput, 255, category_s) Set rs = New ADODB.Recordset rs.Open cmd, , adOpenStatic FillRangeFromRecordset "MarketsLst", ImportToWB, rs Get_Data_Markets = True CleanUp: On Error Resume Next If Not rs Is Nothing Then rs.Close: If db_was_not_open_b Then CloseMainDB If in_errhandler_b Then CentralErrorHandlerP2 Exit Function ErrHandler: If CentralErrorHandlerP1(Module_msC, Source_sC, Erl, , EntryPoint_b:=False) Then Stop: Resume in_errhandler_b = True: Get_Data_Markets = False: GoTo CleanUp End Function So what the heck is going on? What could be affecting the performance to that degree? Thank you for your suggestion. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Performance | Excel Discussion (Misc queries) | |||
performance | Excel Programming | |||
Performance IF(IF or IF(AND | Excel Worksheet Functions | |||
XLL performance in NT and XP | Excel Programming | |||
Bad performance | Excel Programming |