Data Transfer over WAN is slow with VB6 and MSSQL 2000
Hi everybody,
Hope some one can help me to solve the problem!
About the project:
I am developing a VB6 program with MS-SQL 2000 server as
the database (remote). For insert/update i am using ADO
connectivity with the database, but for most of the select
commands i am using dataenvironment connection record
source, while for some of the queries i am using ADO
connectivity.
I have seperate module for each table in the database,
which have its own ADO connection variables and connection
commands (sample attached).
While inserting/updating the database, it is taking nearly
2-3 minutes to process the operation:
operation details:
insertion : 1 table with 1 row (nearly 30 fields)
" : 1 table with 9 rows (nearly 8 fields)
" : 1 table with 5 rows (nearly 8 fields)
updation : 1 table with 1 row (nearly 4 fields)
I feel its slow, please send me your comments, if it is
slow how i can improve the performance, if we have any
other solution than ADO
Thanks in advance
Pramod
Sample Code: This a VB6 Module
Public adoEror As ADODB.Error
Public adoconn As ADODB.Connection
Public adodcmd As ADODB.Command
Public adorecs As ADODB.Recordset
Const sConnStr = "driver={sql server}; server=; uid=;
pwd=; database="
Public Function adoconnectserver() As Boolean
adoconnectserver = True
Set adoconn = New ADODB.Connection
adoconn.ConnectionString = sConnStr
adoconn.Open
If adoconn.State <> adStateOpen Then adoconnectserver
= False
End Function
Public Function adodisconnect()
Set adodcmd = Nothing
Set adorecs = Nothing
If adoconn.State = adStateOpen Then
adoconn.Close
Set adoconn = Nothing
End If
End Function
Public Function adoopenrs()
Set adorecs = New ADODB.Recordset
adorecs.Open "tablename", adoconn, adOpenDynamic,
adLockPessimistic, adCmdTable
End Function
Public Sub Main()
Dim cntr As Integer
Call adoconnectserver
Call adoopenrs
All processing functions will be called here
Call adodisconnect
End Sub
