The fastet way to insert a single row

The fastet way to insert a single row

Postby nuntio2000 » Tue 22 Mar 2005 14:51

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
nuntio2000
 
Posts: 19
Joined: Fri 19 Nov 2004 18:51
Location: South Italy

Postby Serious » Tue 22 Mar 2005 15:22

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
Serious
 

Sample: Supposing to INSERT a new row every 300ms

Postby nuntio2000 » Wed 23 Mar 2005 19:36

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
nuntio2000
 
Posts: 19
Joined: Fri 19 Nov 2004 18:51
Location: South Italy

Postby Serious » Thu 24 Mar 2005 09:09

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
...
Serious
 

Ping Polling refresh time and optimal number of rows for loader

Postby nuntio2000 » Thu 24 Mar 2005 14:06

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
nuntio2000
 
Posts: 19
Joined: Fri 19 Nov 2004 18:51
Location: South Italy

Postby Serious » Fri 25 Mar 2005 12:31

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.
Serious
 


Return to dotConnect for MySQL