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("theCategory").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.
|
Similar Threads
