Why so much Time

I am working on a program in VB6 that connects to a remote mysql at my hosting company.

I have the connection working and am reading in files but it takes forever. I am reading in 1 record set with 9 fields. As a test I only have 1's and 0's in each field. It takes around 20 sec. to fill in the 9 text boxes. Each either haveing a 1 or 0 in it. Any sudjestions on how to speed this up. I am running on a cable modem so hate to see what this would be like on a 56k. This is my connection code below.


If Not Connect(c1) Then
MsgBox "Connection Failed"
Else
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM check1", Conn, adOpenStatic, adLockOptimistic
End If

c7 = rs.Fields("c7").Value
c8 = rs.Fields("c8").Value
c9 = rs.Fields("c9").Value
c10 = rs.Fields("c10").Value
c11 = rs.Fields("c11").Value
c12 = rs.Fields("c12").Value

rs.Close: Set rs = Nothing



and this is the module it calls for the connection




Option Explicit

Public Conn As ADODB.connection

Public Function Connect(cox As String) As Boolean


On Error GoTo ErrorTrap

Connect = False
Set Conn = New ADODB.connection


Conn.CursorLocation = adUseClient
Conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=xxx.xx.xxx.xx;" _
& "DATABASE=com_xxxxxxx;" _
& "UID=com_xxxxxx;" _
& "PWD=xxxxxx;" _
& "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384

Debug.Print Conn.ConnectionString
Conn.Open
Connect = True

ExitPoint:
Exit Function

ErrorTrap:
Connect = False
MsgBox "You must be Connected to the internet before running Club Click"
Resume ExitPoint

End Function

> It takes around 20 sec. to fill in the 9 text boxes.

I believe you count the time to open the connection + fetch the recordset right?
I'd dare to guess that opening of the connection itself takes quite a long time. If so
you'd need to open it (call the function) say in the form_load event (or somewhere else).
Now that the connection is open displaying the data in the texboxes should take much less time (e.g. after the user clicks on a command button or so).

Shurik.

Thank you, that was exactly it. I was calling the connection each time. Now in my splash screen I open the connection one time and put a fake status bar on it so the don't get bored while whaiting. Then each and every page loads quickly after that. Again Thank you so much.

As much as possible, have only one Database Connection in your application. Creating many Database Connections on the same database is bad... :D

To NEOLLE: why do you think he's "creating many Database Connections"?

Actuly NEOLLE was right. I was making a database connection each and every time I pulled a file, even when those files were in the same record. I had worked realy hard on making a connection module for my application but then when I coded the rest of the program I was closeing and reconnecting every time.

Agian thanks for all the help.

OK, NEOLLE seems to have a feeling for this kind of things...

Hmm..there is nothing wrong with opening and closing a connection. If you have ONE open INSTANCE of the database object, closing the connection and re-opening it, will not have any direct speed implications.

Sure you shouldn't be consistenly opening and closing your connection, but you don't need one global, open connection within your application.

Well for some reason it did. Mysql is set up with one table and many fields. If I opened one it would take from 10 - 15 sec. to make the connection, retreave the information and place it in a rtf. then I would close the connection, the next field I would retreave would take again 10 - 15 sec to make the connection and retreave. Useing the Global on a splash screen and takeing the 15 sec. there, then each file I open only takes less then a sec. to place it in the rtf.