MySQL .NET Connector C# running real slow... Sometimes,

Originally Posted by Vague

Hello There, I've recently been working on a large project, very database driven, in C# on windows systems, calling a MySQL database on a linux box.
The project is going very well. Except that simple calls that are sometimes instant, will also sometimes take 10 - 20 seconds. If I were to click a button that refreshes a table, 7 times out of 10 it will be instant, the other 3 times it will lag.
Some details...
All computers that are running clients are running windows XP Prof SP2.
The computer I'm developing on is a P4 3.21 with 2g of RAM.
The other client computers (of which there are 6) range down to a AMD 1100 machine with 512Meg of RAM. They are never really all accessing the database at the same time. They all experience about the same range of performance with the database.
The server was a Thecus Yes Box N2100 Debian Linux with MySQL 5.0.24a, but thinking that it might have been the memory constraints of that system causing the lag, I installed a P4 2100 box with 1g of RAM with Debian, lean and mean, and installed MySQL 5.0.32 on it copying the database over...
But the lag situation was the same.
I started writing the software using a connector I was familiar with, mysql-connector-odbc 3.51
Thinking that that might be the problem I changed to MySQL connector .net 5.0.6 which is what I'm currently using.
But the lag situation was the same.
While I am continually refreshing a simple call, say,

SELECT t1.EmpNo, t1.JobNo, t1.CustomerName, t1.TaskName, t1.Start, t2.EstTime, t2.TotalTime, t1.TaskNo FROM Work AS t1 INNER JOIN Task AS t2 ON t1.JobNo = t2.JobNo AND t1.TaskNo = t2.TaskNo WHERE t1.Fin IS NULL AND t1.State = 'Started'

I can be running a ping at the same time and it won't fluctuate from being <1ms.
I can run heavy calls in MySQL Administrator over and over in rapid succesion without there being any lag.

My connection string is-

MyConString = "server=10.1.1.102;" +
"uid=root;" +
"pwd=pswrd;" +
"database=classic;";

and a normal call is handled like this-

MySqlCommand cmd;
MyConnection = new MySqlConnection(MyConString);
MyConnection.Open();
cmd = new MySqlCommand();
MyDA = new MySqlDataAdapter();
MyDS = new DataSet()
cmd.CommandText = SQLquery;
MyDA.SelectCommand = cmd;
MyDA.Fill(MyDS);
MyConnection.Close();

but with lots of error handling and stuff.
The IDE I'm using is Visual C# 2005 Express Edition.
The program works fine, and is sweet to use except for this frequent and annoying database lag.

any ideas?

Originally Posted by Motoma

Try using EXPLAIN to glean where this lag is coming from. You may be able to speed up your queries by adding a couple of indexes.