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

[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?

[Motoma]

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?

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.

[Vague]

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.

Thankyou for introducing me to the EXPLAIN command. It is extremely usefull, and I'm very gratified to see the they must have taught me something right at school, because all my tables are really well optimised right off the bat. As good as that might be for my currently flagging ego, it's still not the answer.
The database queries are running really well, as was demonstrated by repeatedly calling queries from MySQL Query Browser, with no lag. When I first posted I debated wether to put this question to the MySQL guys, or the .NET folk. The problem really seems to be rooted in the networking or the connector.
Allthough the ping would suggest the network is fine and the connector is lauded as being very reliable.
I might put this question to the .NET team now. Thankyou very much for your help and I'll be sure to let you know if a solution finaly presents itself.

-Vague