Page 1 of 1
The fastet way to insert a single row
Posted: Tue 22 Mar 2005 14:51
by 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
Posted: Tue 22 Mar 2005 15:22
by 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
Sample: Supposing to INSERT a new row every 300ms
Posted: Wed 23 Mar 2005 19:36
by 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
Posted: Thu 24 Mar 2005 09:09
by 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
...
Ping Polling refresh time and optimal number of rows for loader
Posted: Thu 24 Mar 2005 14:06
by 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
Posted: Fri 25 Mar 2005 12:31
by 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.