~Matt~ Posted February 11, 2009 Share Posted February 11, 2009 Does anyone know why my program lags terribly whenever I make a mysql connection/query? Link to comment Share on other sites More sharing options...
0 Antaris Veteran Posted February 11, 2009 Veteran Share Posted February 11, 2009 There could be numerous reasons, ranging from how you have coded it, network traffic (if your not running it locally), etc. Can you explain your setup? Link to comment Share on other sites More sharing options...
0 ~Matt~ Posted February 12, 2009 Author Share Posted February 12, 2009 There could be numerous reasons, ranging from how you have coded it, network traffic (if your not running it locally), etc.Can you explain your setup? This is the code where the mysql is located: Imports MySql.Data Private Sub string_to_process_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles string_to_process.Click Dim cmd As New MySqlClient.MySqlCommand Dim connect As New MySqlClient.MySqlConnection("Database=*database name*;Data Source=tehplanet.net;User Id=*my username*;Password=*my passwod*") Dim the_time As String = System.DateTime.Now Dim WC As New System.Net.WebClient Dim the_ip As String the_ip = System.Text.Encoding.ASCII.GetString((WC.DownloadData("http://whatismyip.com/automation/n09230945.asp"))) WC.Dispose() If string_to_process.Text <> Nothing Then cmd.CommandText = "INSERT INTO `table name`(string, ip, time) VALUES (’" & string_to_process.Text & "‘, ’" & the_ip & "‘, ’" & the_time & "‘)" cmd.Connection = connect connect.Open() cmd.ExecuteNonQuery() connect.Close() End If string_to_process.Text = Nothing End Sub It inserts the values fine, but the program freezes for up to five seconds when its making this query. My hosting is run on a shared server, but other programs(python-based) can make queries almost instantly without lag. Link to comment Share on other sites More sharing options...
0 jamend Posted February 12, 2009 Share Posted February 12, 2009 You're accessing 2 hosts that I'm guessing are external to yours (tehplanet.net and whatismyip.com) every time you run the query. The latter might even be throttling your requests... Link to comment Share on other sites More sharing options...
0 ~Matt~ Posted February 12, 2009 Author Share Posted February 12, 2009 You're accessing 2 hosts that I'm guessing are external to yours (tehplanet.net and whatismyip.com) every time you run the query. The latter might even be throttling your requests... Even when I remove the whatismyiprequest.com request it still has the same lag. Link to comment Share on other sites More sharing options...
0 Antaris Veteran Posted February 12, 2009 Veteran Share Posted February 12, 2009 Have you stepped through the code line-by-line to see where it is actually taking its time? You can do this using F11 in Visual Studio. Link to comment Share on other sites More sharing options...
0 ~Matt~ Posted February 13, 2009 Author Share Posted February 13, 2009 It starts on the first line and I cant drag the arrow anywhere as shown in the image: Link to comment Share on other sites More sharing options...
0 Antaris Veteran Posted February 13, 2009 Veteran Share Posted February 13, 2009 Keep pressing F11 to step through the code, don't move the arrow itself unless you need to go back. Basically keep stepping through until you find what is taking the longest. Link to comment Share on other sites More sharing options...
0 XerXis Posted February 13, 2009 Share Posted February 13, 2009 (edited) horrible code by the way, take my advice, use connection within a "using" construction so it is always disposed and closed. Inside that using block you should also use a try, catch, finally block to make sure you catch the exceptions and dispose of objects in your finally block. Apart from that, if you don't want opening connections and executing queries make your program unresponsive you can use a background thread. However, judging from your style of coding I don't know if you are ready for the added complexities of multi-threading. But keep up the work, you'll get there! edit: and for the love of god, use commands with parameters instead of string concatenation! That code is wide open to sql injection attacks Edited February 13, 2009 by XerXis Link to comment Share on other sites More sharing options...
0 XerXis Posted February 13, 2009 Share Posted February 13, 2009 (edited) rewritten structure: using connection as MysqlConnection = new Mysqlconnection(connectionstring) Dim cmd As New MySqlClient.MySqlCommand try connection.open() cmd = new mysqlcommand("Insert into blabla (blabla) values(?param), connection) cmd.parameters.add(new mysqlparameter("param","yourvalue")) cmd.executenonquery() catch ex as exception //do something with the exception finally //cleanup your mess! if cmd isnot nothing then cmd.parameters.clear() cmd.dispose() end if end try end using it might have some syntax errors, my vb is rusty and I didn't write this in the IDE Edited February 13, 2009 by XerXis Link to comment Share on other sites More sharing options...
Question
~Matt~
Does anyone know why my program lags terribly whenever I make a mysql connection/query?
Link to comment
Share on other sites
9 answers to this question
Recommended Posts