CODEWITHSUNDEEP
mysqlvb.netperformance
Monolord's Knight
Monolord's Knight

Reputation: 183

Slow remote mysql with VB.net- need to speed up

I'm using vb.net and mysql( from a shared hosting) with the following code

    Public conn As OdbcConnection
        Public da As OdbcDataAdapter
        Public ds As DataSet
        Public dr As OdbcDataReader
        Public cmd As OdbcCommand
        Public MysqlConn As MySqlConnection
        Public xc As MySqlCommand
        Public xd As MySqlDataAdapter
        Public serv As String, user As String, pass As String, db As String
Sub connect()
            serv = "xyz.com"
            user = "abc"
            pass = "mypasword" 
            db = "mydb"
            MysqlConn = New MySqlConnection()
            MysqlConn.ConnectionString = "server=" & serv & ";" & "UserID=" & user & ";" & "password=" & pass & ";" & "database=" & db & ";Character Set=utf8;"
            Try
                MysqlConn.Open()
                MysqlConn.Close()
            Catch myerror As Exception
                MsgBox("Connection Error")
            End Try
End Sub

Now any select, insert or update action takes more that 5 seconds and freezes the whole application. But my client wants it faster. How can I speed it up? is that the hosting or mysql or connection method - which is to upgrade or alter with other to make it faster?

Thanks

Upvotes: 0

Views: 1867

Answers (1)

Rick James
Rick James

Reputation: 142540

India:Canada -- 140ms seems reasonable. This means that every query sent to the server will take at least that long.

As for the "5 seconds"... Turn on the "General log" for a short while and run the app. Then look through the log. You may find that vb.net is injecting some extra commands, possibly in front of each query you run. This would further slow down your application. If that is the case, abandon vb.net, and be suspicious of any other 3rd party software.

I hope you are doing only one "connection" for an entire session?

Aside from that, the best thing to consider doing is to write Stored Routines (Procedures, Functions, Triggers, etc) whenever you need to perform more than one query in a row. That way, you can issue one query instead of multiple SQL statements, thereby fewer roundtrips.

Upvotes: 2

Related Questions