title: C#, SQL Server Compact - optimize insert record speed
body: <p>I am building application for windows mobile 6.5, on a resource restricted device Unitech PA690 and i am having speed issue when inserting records into my SQL server compact edition database...
  Does anyone know the best and fastest method for inserting values into compact database?
  Here's my insert-testing code, i'am using direct Insert:</p>
  
  <pre><code>private void button1_Click(object sender, EventArgs e)
      {
          Stopwatch stopwatch = new Stopwatch();
          stopwatch.Start();
          string conn = "Data Source=" + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase)) + "\\AppDatabase1.sdf;Persist Security Info=False";
          SqlCeConnection connection = new SqlCeConnection(conn);
          connection.Open();
          int z = 0;
          string name = "stack";
          string surname = "overflow";
          progressBar1.Maximum = 2000;
          while (z&lt;2000)
              {
                  try
                  {
                      SqlCeCommand cmd = new SqlCeCommand("Insert into test (id,name,surname) values (@id, @name, @surname)", connection);
                      cmd.Parameters.AddWithValue("@id", z);
                      cmd.Parameters.AddWithValue("@name", name);
                      cmd.Parameters.AddWithValue("@surname", surname);
                      cmd.CommandType = System.Data.CommandType.Text;
                      cmd.ExecuteNonQuery();
                      z++;
                      progressBar1.Value = z;
  
                  }
                  catch (SqlCeException)
                  {
                      MessageBox.Show("!!!","exception");
                  }
                  finally
                  {
  
                  }
              }
          stopwatch.Stop();
          MessageBox.Show("Time: {0}" + stopwatch.Elapsed);
          connection.Close();
  
      }
  </code></pre>
  
  <p>The elapsed time is: 96 seconds, which makes insert speed of 21 row/sec. Does anyone know the best method for improving insert speed here ? I know that this mobile device is slower, but i also believe that insert speed should be at least 400 rows/sec according to this link: <a href="http://social.msdn.microsoft.com/Forums/sqlserver/en-US/ea096003-4069-4d0b-9a96-39e1bcb8b7af/insert-rows-with-sqlserverce-v35-is-very-slow-can-anyone-help-insert-performance-poor" rel="nofollow">SQL CE slow insert</a>,
  Or am I wrong?
  I have a file with approximately 20 000 rows to be inserted very often, so please help...
  Thanks,</p>