Re: Fewer large queries vs lots of small. performance?

[Darren White]

Thank you for taking the time to reply to me Phillip. However I see I must be much more concise if I am to expect helpful replies. I'll try again:

I am using MySql Server 5.6, innodb, C# Connector/Net 6.9.7.

I am mapping status data from an embedded control system into a database to make it more accessible. So say I have 100 tables, each has 100 columns and 100 rows. All tables are fixed in size and data has fixed position in terms of fields/rows (or 'cells' as I say in my sloppy terminology). Every value is updated with a new value once a second.

So I need to know how should I do this with maximum efficiency.

This (I think is a question just on the database server side):

Which is faster:
-------
UPDATE SET column_1=1 WHERE id=1;
UPDATE SET column_2=1 WHERE id=1;
UPDATE SET column_n=1 WHERE id=1;
UPDATE SET column_1=1 WHERE id=2;
UPDATE SET column_2=1 WHERE id=2;
UPDATE SET column_n=1 WHERE id=2;
UPDATE SET column_1=1 WHERE id=n;
UPDATE SET column_2=1 WHERE id=n;
UPDATE SET column_n=1 WHERE id=n;
-------
or
-------
UPDATE SET column_1=1 , column_2=1, column_n=1 WHERE id=1;
UPDATE SET column_1=1 , column_2=1, column_n=1 WHERE id=2;
UPDATE SET column_1=1 , column_2=1, column_n=1 WHERE id=n;
-------

Then this is maybe a question on the MySql C# Connector/Net 6.9.7 side:

In addition to the above database specific performance issues I have two more:

1) When and how often do I open and close a connection
2) How many (SQL) commands do I put in a string and send as a C# command.

ie (Assuming updating a row at a time is faster than per 'cell' (field of a row). What is faster:

-------
open new connection

string sql= “UPDATE SET column_1="1" , column_2="1", column_n="1" WHERE id=1; ”
MySqlCommand cmd = new MySqlCommand(sql,connection);
cmd.ExecuteNonQuery();

string sql= “UPDATE SET column_1="1" , column_2="1", column_n="2" WHERE id=2; ”
MySqlCommand cmd = new MySqlCommand(sql,connection);
cmd.ExecuteNonQuery();

string sql= “UPDATE SET column_1="1" , column_2="1", column_n="n" WHERE id=n; ”
MySqlCommand cmd = new MySqlCommand(sql,connection);
cmd.ExecuteNonQuery();

close connection
-------
or
-------
open new connection
string sql=
“
UPDATE SET column_1="1" , column_2="1", column_n="1" WHERE id=1;
UPDATE SET column_1="1" , column_2="1", column_n="1" WHERE id=2;
UPDATE SET column_1="1" , column_2="1", column_n="1" WHERE id=n;
”
MySqlCommand cmd = new MySqlCommand(sql,connection);
cmd.ExecuteNonQuery();
close connection
-------