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("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.

No martin. The network is very simple. It has one server two pc's and no other services of any kind except the usual domain controller kind of services. The Domain controller is the SQL Server as well. However, we only have 5 users only.

Hi guys,
The stored procedure behind this is a very basic procedure. It takes 4 parameters and return back the rows that it finds. That code is the same that runs at both cases, when it runs fast and when it runs slow. I doubt very much it the stored procedure. I have run the profiler a few times on it and it is fast in returning results.
The router is very basic setup. The only thing is that we disable the usual 1433 port to the SQL Server and configured another port for it for added security.
I have since yesterday ran speed tests on the router for upload and download. I caught one of the slow times when the application was getting data at 30 secs or more. When I checked the speed of the download/upload everything seemed normal.

I am stumped.

When I use SQL Server Profiler, I see that some RPC calls take longer sometimes, but not always. What could be affecting that since the data is the same?