Running a VB 6 app using ADO/SQL and a WAN

[-Radi8-]

I have several little applications that I developed for my company and need to deploy them across a WAN to several remote locations. I am a little disappointed on the performance across the WAN. Even though the recordset fetches are relatively small (maximum 20 fields of no more than 30 records), the remote site metrics are very poor.

I use a combination of stored procedures and embedded SQL commands to get the data I need, and have seen very little difference in the performance either way. It almost seems as though the raw data is taking a long time to traverse the WAN.

FYI: we have 4 remote locations and each one is connected via no less than a 384K (up/down) dedicated pipe. We have monitored the bandwidth usage and it is nominal during the tests.

Is there a way to improve performance across a WAN? Compress/decompress the data? Hardware requirements on the remote machines?

If you need more info on anything, please contact me and I will provide whatever you need in order to help with a solution.

[Twodogs]

You'll need to tune your apps a little bit for the WAN; Firstly, make sure that you are opening an ADO connection just the once, and are reusing it. This slows an app down considerably if you are constantly opening and closing a connection. If you are not noticing any appreciable bandwidth issues, this is the most likely cause (going on the info you've provided).

Secondly, if you are populating a lot of combo boxes, consider getting the info into global arrays when opening the application, and populating the combo boxes from global arrays. The app may take a bit longer to start, but the response for the user will be appreciably better.

Where possible use ADO command objects and parameterized queries - much quicker to call a stored proc with parameters, than it is to call in-line sql.

If you can figure out exactly where your app is slowing, post the code and we'll all have a go at improving it.

[-Radi8-]

Well, I think you pointed out where the problem may lie. I am opening a connection and closing it prior to getting the next dataset.

Using ADO, how do I flush the current recordset and create another without using the rs.open sql, connection type statement?

(PS, I ride an FXSTi)

[Twodogs]

Just reset the recordset to the new sql statement & execute it.
ie

' In a module, create a global connection and global command
Dim gcnn as ADODB.Connection

dim gcmd as ADODB.Command

Set gcnn - NEW ADODB.Connection
Set gcmd = NEW ADODB.Command

gCnn.ConnectionString = "WhateveryourConnectionstringis"
gCnn.Open

Set gcmd.ActiveConnection = gcnn

' In a function/sub
Dim rs ad ADODB.Recordset
gcmd.CommandText = "SELECT * FROM Customers"
Set rs = gcmd.Execute

' do stuff with your recordset
rs.close

' In another function/sub
' if you are using a global recordset, you won't need to dim a new recordset each time, but if you do, you can still use the same connection and command object
Dim rs as ADODB.Recordset
strSql = "SELECT * FROM Suppliers"
set rs = gCmd.Execute
' etc

etc

[Illuvatan]

I don't think you will do that now.. but if you are planning a re-write you should consider getting familiar with DAO and try it that way.

greetings UNI

[Twodogs]

Why would you even consider using DAO into a SQL Server database - especially when M$ decided to tell everyone that they were considering dropping it in 1992...