Weird Speed Problems... Please Help!

[daniel v]

Hi,

I am working on a web application in VB .NET and I'm having some weird speed problems that I can't track down. I have written a stub function that makes repeated calls to my data access layer functions to test performance between the different connection methods. ODBC takes 6 seconds to run the loop, .Net DataSet and .Net DataReader versions both take 18 seconds... what's going on here? .Net is supposed to be faster! Even MS Access is faster at 16 seconds... The snipped sections of code are just response.writes accessing the data in the most direct way possible for each connection method. Is there anything wrong with my code? If not, what else could be wrong? I'm really starting to worry about how this app is going to scale... Help please!





/////////////////////// ODBC version, executes in 6 seconds (300 iterations)

Public Function MySqlOdbc(ByVal strSQL As String, ByRef dsResult As DataSet) As Boolean

Dim MyConString as string = _
"DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=192.168.0.1;" & _
"DATABASE=testdb;" & _
"UID=sa;" & _
"PASSWORD=sa;" & _
"OPTION=3"

Dim MyConnection as new OdbcConnection(MyConString)
Dim myCommand As New OdbcCommand(strSQL, myConnection)
MyConnection.Open()

Dim myReader As OdbcDataReader
myReader = myCommand.ExecuteReader()

While myReader.Read()

//// snip: display the data ////

End While

MyConnection.Close()
MySqlOdbc = True

End Function




/////////////////////// .NET Connector DataReader version, executes in 18 seconds (300 iterations)

Public Function MySqlDataReader(ByVal strSQL As String, ByRef dsResult As DataSet) As Boolean

Dim myConnString As String = _
"Database=testdb;" & _
"DataSource=192.168.0.1;" & _
"User ID=sa;" & _
"Password=sa"

dim myConn as MySqlconnection
dim myCommand as MySqlCommand
dim myReader as MySqlDataReader

myConn = new MySqlconnection(myConnString)
myCommand = new MySqlCommand(strSQL,myConn)
myConn.open()

myReader = myCommand.executeReader()

While myReader.Read()

//// snip: display the data ////

End While

myReader.close()
myConn.close()

MySqlDataReader = True

End Function




/////////////////////// .NET Connector DataSet version, executes in 18 seconds (300 iterations)

Public Function MySqlNet(ByVal strSQL As String, ByRef dsResult As DataSet) As Boolean

Dim myData As New DataSet ( )

Dim myConnString As String = _
"Database=testdb;" & _
"DataSource=192.168.0.1;" & _
"User ID=sa;" & _
"Password=sa"

Dim conn As New MySqlConnection(myConn)
Dim adapter As New MySqlDataAdapter()
adapter.SelectCommand = new MySqlCommand(strSQL, conn)
adapter.Fill(myData, "data")

for each Entry as DataRow in myData.Tables ( "data" ).Rows

//// snip: display the data ////

next

MySqlNet = True

End Function