1

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:

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<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();

    }

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: SQL CE slow insert, Or am I wrong? I have a file with approximately 20 000 rows to be inserted very often, so please help... Thanks,

Ivan Tadic
  • 143
  • 2
  • 9
  • 1
    Does this answer your question? http://stackoverflow.com/questions/1606487/sqlbulkcopy-using-sql-ce – BlueMonkMN Oct 15 '13 at 15:12
  • What about transactions? It should be faster if It will be done in one transaction. var transaction = connection.BeginTransaction(); //while code transaction.Commit(); – Vladimir Gondarev Oct 15 '13 at 15:18
  • Thanks BlueMonkMN for your link, i got a little speed improvement from your method. Now i'am having 29 Rows/sec with processor knocked up at 100 %... Vladimir, could you be more specific about transactions? Thanks... – Ivan Tadic Oct 16 '13 at 11:37

1 Answers1

0

I'm not sure about specific performance times, but have you tried using your while loop to build a single query string which you would then pass to the SQL server? This might cut down on your time because you are just making one call to the database instead of 2000 individual execute commands.

Something like:

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);
    int z = 1;
    string name = "stack";
    string surname = "overflow";
    progressBar1.Maximum = 2001;

String query = "Insert into test (id,name,surname) values (0, name, surname)"
while (z<2000)
    {
    query = query + ", ("+z+", "+name+", "+surname+")"
    z++;
            progressBar1.Value = z;
    }
     try
        {
            connection.Open();
            SqlCeCommand cmd = new SqlCeCommand(query, connection);
            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();

}

It's not great to have this un-parameterized, but this might show you whether or not your bottleneck is in making multiple database calls.

I suppose that you could also use the loop just add more and more parameters to the query instead of just building a large string.

TrustedInSci
  • 148
  • 5