title: Optimizing bulk insertion into a SQL Server CE database
body: <p>I'm trying to initialize a SQL Server CE database with a large number of rows (~100K), but the process takes minutes to complete. The database is created using a LinqToSQL data model, using modified code from <a href="http://sqlcebulkcopy.codeplex.com" rel="nofollow noreferrer" title="sqlbulkcopy">codeplex sql bulk copy</a>.</p>
  
  <p>What more can I do to speed things up?</p>
  
  <p>Profiling shows the majority of the time is spent in the row insertion call:</p>
  
  <p><img src="https://i.stack.imgur.com/o0d5i.png" alt="Profiled Expensive Insertion"></p>
  
  <p>Relevant code:</p>
  
  <pre class="lang-vb prettyprint-override"><code>Public Sub CreateAndPopulateDatabase()
      'read data from comma-separated files
      ...
  
      'create and populate
      Using db = New TestLinqToSQLDataModelDataContext("Data Source=&lt;path&gt;.sdf")
          db.CreateDatabase()
  
          Dim c = DirectCast(db.Connection, SqlCeConnection)
          Using trans = c.BeginTransaction()
              c.LinqToSQLCeBulkInsert(someItems, trans)
              c.LinqToSQLCeBulkInsert(someOtherItemsOfADifferentType, trans)
              c.LinqToSQLCeBulkInsert(lotsMoreItemsOfAnotherType, trans)
              ...
  
              trans.Commit()
          End Using
      End Using
  End Function
  
  '''&lt;summary&gt;Inserts a collection of items into a SQLCe database using Linq-To-Sql metadata.&lt;/summary&gt;
  '''&lt;typeparam name="T"&gt;The type of items to insert. The target database table and column-field mappings are determined based on the type's LingToSQL meta data.&lt;/typeparam&gt;
  &lt;Extension()&gt;
  Private Sub LinqToSQLCeBulkInsert(Of T)(conn As SqlCeConnection, collection As IEnumerable(Of T), trans As SqlCeTransaction)
      Dim tableName = GetType(T).GetCustomAttributes(inherit:=False).
                      OfType(Of TableAttribute).
                      Select(Function(e) e.Name).
                      Where(Function(e) Not String.IsNullOrEmpty(e)).
                      Append(GetType(T).Name).
                      First()
      Dim specializedRecordUpdater = MakeSpecializedRecordUpdaterForTypeToCols(Of T, SqlCeUpdatableRecord)(IterTableColumns(conn, tableName))
  
      Using cmd As New SqlCeCommand(tableName, conn, trans) With {.CommandType = CommandType.TableDirect},
            rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable)
          Dim rec = rs.CreateRecord()
          For Each sourceItem In collection
              specializedRecordUpdater(sourceItem, rec)
              rs.Insert(rec)
          Next
      End Using
  End Sub
  
  '''&lt;summary&gt;Compiles a specialized dynamic method that populates a row record using values of a specified type.&lt;/summary&gt;
  Private Function MakeSpecializedRecordUpdaterForTypeToCols(Of TValue, TRecord)(cols As IEnumerable(Of String)) As Action(Of TValue, TRecord)
      'Find value getters corresponding to column names
      Dim colToValueGetterMap = GetType(TValue).GetProperties().ToMap(
          keySelector:=Function(e) e.GetCustomAttributes(inherit:=False).
                                     OfType(Of ColumnAttribute)().
                                     Select(Function(attr) attr.Name).
                                     Where(Function(x) Not String.IsNullOrEmpty(x)).
                                     Append(e.Name).
                                     First().
                                     ToUpperInvariant(),
          valueSelector:=Function(e) e.GetGetMethod())
  
      'Find record setters corresponding to types
      Dim typeToUpdaterMap = (From potentialSpecializedSetter In GetType(TRecord).GetMethods()
                              Where potentialSpecializedSetter.GetParameters().Length = 2
                              Where potentialSpecializedSetter.ReturnType = GetType(Void)
                              Let parColId = potentialSpecializedSetter.GetParameters().First()
                              Let parValue = potentialSpecializedSetter.GetParameters().Last()
                              Where parColId.ParameterType = GetType(Integer)
                              Where parColId.Name = "ordinal"
                              Where parValue.Name = "value"
                              Where potentialSpecializedSetter.Name = "Set" + parValue.ParameterType.Name OrElse potentialSpecializedSetter.Name = "SetValue"
                              ).ToMap(Function(e) e.parValue.ParameterType,
                                      Function(e) e.potentialSpecializedSetter)
  
      'Dynamic method updates each column by passing result of getter to corresponding updater
      Dim targetParameter = Expression.Parameter(GetType(TRecord), "target")
      Dim valueParameter = Expression.Parameter(GetType(TValue), "value")
      Dim updateCalls = cols.Select(Function(e, i)
                                        Dim colId = Expression.Constant(i)
                                        Dim getter = colToValueGetterMap(e.ToUpperInvariant())
                                        Dim updater = typeToUpdaterMap(getter.ReturnType)
                                        Dim getterValue = Expression.Call(valueParameter, getter)
                                        Return Expression.Call(targetParameter, updater, {colId, getterValue})
                                    End Function).ToArray()
      Dim updateBlock = Expression.Block(updateCalls)
      Dim func = Expression.Lambda(updateBlock,
                                   "_MakeSpecializedRecordUpdaterForTypeToCols" + GetType(TValue).Name,
                                   {valueParameter, targetParameter})
      Return DirectCast(func.Compile(), Action(Of TValue, TRecord))
  End Function
  
  '''&lt;summary&gt;Enumerates the columns in a SQLCe database table.&lt;/summary&gt;
  Private Iterator Function IterTableColumns(conn As SqlCeConnection, tableName As String) As IEnumerable(Of String)
      Dim hasResults = False
      Using ordCmd As New SqlCeCommand("SELECT Column_Name FROM information_schema.columns WHERE TABLE_NAME = N'{0}' ORDER BY Ordinal_Position".Frmt(tableName), conn),
            reader = ordCmd.ExecuteReader()
          While reader.Read()
              Dim name = reader.GetString(0)
              If String.IsNullOrEmpty(name) Then Throw New IO.IOException("Unnamed column")
              Yield name
              hasResults = True
          End While
      End Using
      If Not hasResults Then Throw New IO.IOException("No columns")
  End Function
  </code></pre>