The fastet way to insert a single row

[nuntio2000]

Hi,
i'm in a scenario where i have to INSERT a new single row in the same TABLE every 300ms about. Please could you suggest me the best practise with MySQL.NET in order to get the maximum speed performance?


Thx,
Nunzio

[Serious]

Dear sir, in this case the best choise is to use MySqlLoader class. MySqlLoader work is based on generation of INSERT statements that insert data by several rows at the same time. It is designed exactly for the purposes you need.
Please refer to documentation for more detailed information on how to work with MySqlLoader class

[nuntio2000]

Until now i'm using Ping the connection before each inserting and create a new intance of MySQLLoader.
I'm using this code:

Code: Select all

public static void CopyRow(DataRow row, MySqlConnection  connection) 
		{ 
			if (connection.Ping())
			{
				MySqlLoader loader = new MySqlLoader(); 
				loader.Connection = connection; 
				loader.Delayed = false; 
				
				loader.TableName = "cdr"; 
				loader.CreateColumns(); 
				loader.Open();
				
				for (int i = 0; i < loader.Columns.Count; ++i) 
					loader.SetValue( i, row[i]); 
								 
				loader.Close(); // flush table to database 
			}
		}

Can I save time removing the Ping() connection?
Can I save time using the same instance of MySQLLoader?
Please let me know your opinion and also some code samples.

Thanks,
Nunzio

[Serious]

Please consider that MySqlLoader was designed to insert data by several rows at the same time. So it is not the best way to open and close it for each row.
Please use the following schema:

1. Create loader
...
2. Ping connection
3. Open loader
4. Insert several rows
5. Close loader
6. Go to 2
...

[nuntio2000]

In order to follow your schema, please tell the ideal polling interval to Ping periodically the connection and please tell me also an optimal number of rows to bufferize before using the MySQLloader to optimize the speed of the inserting transaction.
Please remember that my app generates about 1-2 new rows every 200ms.

Thanks,
Nunzio

[Serious]

Optimal number of rows to bufferize you can find only after performing a test on your database with typicall data set.
If perfomance is important for you it is unnecessary to Ping connection. The following strategy is optimal for you:

1. Open loader
2. Insert rows
3. Close loader
4. If an error occurs close the old connection, create new connection and insert the rows again.