Strange lag with C# and MySQL connector

[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,
Expand|Select|Wrap|Line Numbers

1. 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-
Expand|Select|Wrap|Line Numbers

1. MyConString = "server=10.1.1.102;" +
2. "uid=root;" +
3. "pwd=pswrd;" +
4. "database=classic;";

and a normal call is handled like this-
Expand|Select|Wrap|Line Numbers

1. MySqlCommand cmd;
2. MyConnection = new MySqlConnection(MyConString);
3. MyConnection.Open();
4. cmd = new MySqlCommand();
5. MyDA = new MySqlDataAdapter();
6. MyDS = new DataSet()
7. cmd.CommandText = SQLquery;
8. MyDA.SelectCommand = cmd;
9. MyDA.Fill(MyDS);
10. 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?

[emr550m]

I' m having exactly the same problem. This show up on debian server. I'm using windows mysql server first. There is no lag on the program. But when i start using Debian linux mysql server, strangely somehow if there is some consecutive selects, there are 1-2 seconds lags between selects.

I make every variable same amout with linux and windows server but it is not about these things i think. Because if you use other connector like mysql query browser there isnt any lag. This cames up if you use connector .NET + debian Linux + c# .

Are there anybody except us sees that problem?? Can anybody help us :)

[jvbytes]

Pls. post some sample code to connect and insert a simple query to connect to MySQL(Sun or Linux) and c# 2.0.

[r035198x]

There could be several reasons for the slow behavior.
1.) Poor table design
2.) Lack of proper indexing on tables
3.) Inefficient code for both the front-end and back end.
4.) Poor/unreliable network connectivity
5.) Some database connections not being closed.

Some would even say that the list is endless.