ADO performance

[Rebooot]

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.

 

[Tim Williams]

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