Data Transfer over WAN is slow with VB6 and MSSQL 2000 
Author Message
 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



Fri, 04 Mar 2005 13:45:51 GMT  
 Data Transfer over WAN is slow with VB6 and MSSQL 2000


Quote:
> 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

Try using a dsn-less connection instead of odbc:

Provider=SQLOLEDB.1;Persist Security Info=True;User
ID=userid;Password=password;Initial Catalog=yourdb;Data Source=yourmachine

<data source should be changed to the name of the machine running your SQL
server, also change userid/password and yourdb>

 However, I wouldn't have expected ODBC to be a slow as you describe.

--
John Blessing

http://www.LbeHelpdesk.com - Help Desk software at affordable prices
http://www.free-helpdesk.com - Completely free help desk software !
http://www.lbetoolbox.com - Utilities for MS Outlook



Fri, 04 Mar 2005 15:13:04 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. In VB6 I am Printing Loads of data under Win 2000

2. App runs slower after conversion to VB6 and Access 2000

3. Transfer Record within Access 2000 Tables using VB6 Sql

4. Transfer tables within Access 2000 Record using VB6

5. Transfer Record within Access 2000 Tables using VB6 Sql

6. Transfer tables within Access 2000 Record using VB6

7. Slow, slow, slow search on VB6, ADO and SS7

8. data transfer over a SLOW connection

9. MSSQL 2000 and VB.Net merge replication

10. Importing products from delimited textfiles to MSSQL 2000

11. ! Need help with WAN File Transfer !!

12. Slow MSSQL table updates...

 

 
Powered by phpBB® Forum Software