Page 1 of 2
.net mysql connector performance
Posted: Sat Dec 31, 2011 10:41 am
by reefcrazed
I am getting a little irritated here. Has anyone had problems with the MySql connection for .net ? It just seems painfully slow, especially on the first connection. Doing something like adding items to a combobox seems very slow compared to my day to day dealings with MS SQL. It has to be something I am doing wrong because there is at least one visual studio project on SVN that runs lightning fast on loading his combobox, unfortunately I do not see his source code on SVN.
Also I have tried from the newest mysql dll, back to version 5. I have tried the .net 2.x and the 4.x flavor of it.
Re: .net mysql connector performance
Posted: Sat Dec 31, 2011 11:42 am
by John Adams
Did you try some of the apps under the JohnAdams folder on eq2tools? I use database-loaded combo boxes myself, and none of them seemed terribly slow to me. Although I guess my expectations are not high that they would be instantaneous hah. I am new to C#, so I just assumed the speed was normal.
I believe Scatman lost most of his sources in a computer crash years ago. The source that is on SVN is all he found.
~shaking finger at Scatman for not committing~
Re: .net mysql connector performance
Posted: Sat Dec 31, 2011 11:51 am
by reefcrazed
Yeah all around, the first connection from C# is slow as *$%&^(*. Connections after the first are respectable, I have been reading all morning about pooling, connection issues, etc. I do not care what the excuse is. If something like Heidi SQL responds back the same results in .0001 seconds, I should be able to do the same. I am going to just keep coding for now and the resolution will eventually come to me.
Re: .net mysql connector performance
Posted: Sat Dec 31, 2011 2:44 pm
by Scatman
Post some code! Let's see what you're doing.
Re: .net mysql connector performance
Posted: Sat Dec 31, 2011 2:55 pm
by reefcrazed
Lets just take this for example. This is nothing more than loading a list of the Zones.Id and Zones.Name in and pusing them into a SortedList. I can throw those into a textbox, ComboBox, whatever because the speed is always slow.
Code: Select all
private void LoadZones()
{
MySqlConnection MyConnection = new MySqlConnection("SERVER=" + Settings.Default.db_host +
";DATABASE=" + Settings.Default.db_database +
";UID=" + Settings.Default.db_login +
";PASSWORD=" + Settings.Default.db_password);
MySqlCommand command = MyConnection.CreateCommand();
command.CommandText = "select name, id from zones";
MyConnection.Open();
MySqlDataReader dr;
dr = command.ExecuteReader();
while (dr.Read())
{
ZoneList.Add(dr[0], dr[1]);
}
MyConnection.Close();
}
Re: .net mysql connector performance
Posted: Sat Dec 31, 2011 2:57 pm
by Scatman
Are you opening and closing a connection before and after every query?
Re: .net mysql connector performance
Posted: Sat Dec 31, 2011 3:00 pm
by reefcrazed
I will try both ways. You know I hear to close it, I hear to leave it open. I never know what is right.
Re: .net mysql connector performance
Posted: Sat Dec 31, 2011 3:01 pm
by Scatman
Leave it open. It won't harm anything to leave it open unless you're nearing the maximum number of file descriptors your OS allows to be opened at a time, which is highly unlikely unless you're doing something crazy.
Re: .net mysql connector performance
Posted: Sat Dec 31, 2011 5:45 pm
by reefcrazed
Well the performance is still not blowing my skirt up. Not going to ditch it yet, but may just save what I have and look at other options also.
Re: .net mysql connector performance
Posted: Sat Dec 31, 2011 6:19 pm
by Scatman
Do you know if it's the query itself causing the performance problems or adding the items to the combo box? I'd test both.
Re: .net mysql connector performance
Posted: Sat Dec 31, 2011 6:22 pm
by reefcrazed
I had tried a textbox and just using Textbox1.AppendText, same result.
Re: .net mysql connector performance
Posted: Sat Dec 31, 2011 10:04 pm
by John Adams
Are you using .Net Connectors lame ass data adapters and all that? I refuse to conform, even if they are easier. I hate them. I write all my own queries in and out, as if I were writing code in PHP. Makes more sense to me, too... thought there are some tricks of C# I do use to filter data once I get the data from the DB.
Check out my EQ2WorldBuilder app, the SQL files. Not sure if this helps any. Again, I do not notice slowness, but I might not really be expect it to be fast. Maybe the slowness is not worth the amount of effort you are spending investigating it
Re: .net mysql connector performance
Posted: Sat Dec 31, 2011 10:20 pm
by reefcrazed
I am with you John. I have to say using LINQ is fairly cool, but there is a lot going on behind the scenes that I cannot see and that bothers me. At work I have gotten to where I will not use Microsoft generated queries even for LINQ. I write my own stored procedures and attach LINQ to them. Meh, ultimately you probably generated the best database editor with your PHP editor that is in place. There really is no reason to recreated the wheel. I could spend months working on something and never come close to emulating what you have already done.
That is a complement by the way.
Re: .net mysql connector performance
Posted: Sat Dec 31, 2011 10:29 pm
by John Adams
Thank you
Funny thing about my editor though, it was a crazed hack attempt to get something to edit the DB, since my C# editor attempts failed miserably. We just hired our two newest content guys (Scatman and Zcoretri), and before they could get bored and wander off, I threw together an entire PHP editor in a few weeks. We've been adding to it since then.
I always liked the idea of a stand-alone exe, but you're right... why re-do what has already been done? Have you seen my DB Editor 2.0 yet? It's just a shell, but I think Steve modeled his editor after it to help move towards a more solid editor for public usage.
Re: .net mysql connector performance
Posted: Sat Dec 31, 2011 10:32 pm
by reefcrazed
Yes, I have seen your eq2db2, I think it is called. It is in SVN.