Hey Everyone -

I've been having a consistent slow performance using MySql and VB .Net, but I don't know where the slow performance is. The problem has been that when ever I set up a query, things get VERY slow. It can often take 10-20 seconds to do a very simple query in a database that has maybe 10 records total. Can anyone help me out?

Here's the environment:

- Win2k Server
- VB.Net
- MySQL ODBC Driver v 3.51.09
- MySql 4.0.20d (I tried this with v4.017 and had the same results)
- MySql was installed in the default folder (c:\mysql)

Here's the Code:


------------------------------------------
Public Function dbQry(ByVal UserID As String) As Array

Dim MySQLConn As New OdbcConnection("DSN=DSN-testdb")

Dim StartTime1, StartTime2, EndTime1, EndTime2 As DateTime

' ** Standard odbc mysql connection setup. **
MySQLConn.Open()

Dim strSQL As String = "SELECT * FROM tbl_Everyone WHERE UserID=" & UserID
Dim objCommand As OdbcCommand
objCommand = New OdbcCommand(strSQL, MySQLConn)

Dim objDataReader As OdbcDataReader
objDataReader = objCommand.ExecuteReader

Dim UserData(43) As String

If objDataReader.Read() Then
' ** Querry returned something. **
EndTime1 = DateTime.Now()
UserData(0) = objDataReader.Item("UserID")
UserData(1) = objDataReader.Item("UserName")

...
[there are about 40 objDataReader.Item reads here, and then...]
...

UserData(43) = objDataReader.Item("LFNoPrefMW")
'Querry returned nothing
End If

MySQLConn.Close()
Return UserData

End Function

--------------------------------------

If I step through the code, it appears that the .Open() statement takes about 1 to 2 seconds to complete, and the DataReader.Item statements together take an additional 20 seconds to complete.

Some other things to note:

- I've been using the exact came code to hit an MS Access database, and it's pretty fast. I get results in only a few milliseconds, so I know nothing is weird with .Net or my code.

- When I do the same query using phpMyAdmin, the results come back in about .002 seconds.

- I am still new to MySql so maybe there is some setting somewhere that I don't know about?

Thanks!!

James