Slow performance using VB & .Net
Hey Everyone -
I've been having a consistent problem using MySql and VB .Net. 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 entries. 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
Subject
Written By
Posted
Slow performance using VB & .Net
August 16, 2004 11:30PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.