Inserting a large number of rows into SQL CE 4.0 with Entity Framework 4 (performance issue)

Question

I have a small SQL CE 4.0 database with several tables, mapped using Entity Framework 4.

Here is the code I have

foreach (String designation in newItemDesignations)
{
    ParameterData defaultValue = PDM.GetDefaultParameterData(designation);

    // Fill the ItemParameterDBO object with the data
    ItemParameterDBO dbParam = new ItemParameterDBO();
    dbParam.ItemID = dbItem.ID;
    dbParam.Designation = designation;    
    dbParam.High = defaultValue.High;
    dbParam.Low = defaultValue.Low;

    database.ItemParameterDBOes.AddObject(dbParam);
}

database.SaveChanges();

This code happens 24 times and each time the newItemDesignations list contains exactly 525 elements. That's a total of 12600 rows to add.

The complete process lasts 509 seconds. I guess that's too much for 12600 rows.

I am aware that I am calling SaveChanges 24 times. At the moment, the application design does not allow me to put all inserts into a single transaction (with SaveChanges). However, take a look at what happens with the single transaction. 509 / 24 = 21 seconds, or a 40 ms per row.

Is 40 ms the normal (avg) time for a row to be inserted via EF4?

I've checked my other code (other than adding to the database and saving changes). It takes total of 100 ms for all 12600 rows. That's 0.01% of complete time, so that's obviously not the problem. The 99.99% of the processing time is spent in EF4 AddObject and SaveChanges.

I am also aware that I am setting the ItemID property which is a foreign key. This is an integer so I guess it shouldn't matter much. But I wouldn't know.

Also note: there are no indexes set on any of the tables (except for primary/foreign keys)

What am I doing wrong here, why is this so slow?
Is this the normal time needed for inserting that much rows or is this some kind of performance restrictions related to SQL CE 4?

99.99% of the time is spent in SaveChanges, which establishes a transaction, processes the inserts sequentially, and commits the transaction. My guess if i you were to use straight up ADO.NET to talk to your SQL CE database the results would be very similar. EF does not introduce "noticeable" overhead to the overall process. — e36M3, Aug 11 '11 at 14:40

score 10 · Answer 1 · edited May 23 '17 at 11:43

10

Since the examples are scarce, here is the code I tested and it worked flawlessly. Thanks to ErikEJ's SqlCeBulkCopy library. A must have.

DataTable table = new DataTable();

table.Columns.Add(new DataColumn("A", typeof(int)));
table.Columns.Add(new DataColumn("B", typeof(String)));
table.Columns.Add(new DataColumn("C", typeof(Byte)));

for(int i = 0; i < 12000; i++)
{
    DataRow row = table.NewRow();
    row["A"] = "124324"
    row["B"] = "something";
    row["C"] = 15;

    table.Rows.Add(row);
}

String connString = @"Data Source = C:\Database.sdf";
SqlCeBulkCopy bulkInsert = new SqlCeBulkCopy(connString);
bulkInsert.DestinationTableName = "Items";
bulkInsert.WriteToServer(table);

Inserting my (OP) 12600 rows took less than 2 seconds.

This example is bad, it's not strongly typed, but it describes how to manually create a datatable and insert it into a database using SqlCeBulkCopy library (see the accepted answer for a link).

edited May 23 '17 at 11:43

Community

1
1

answered Aug 12 '11 at 05:39

Kornelije Petak

9,412
15
68
96

Nice solution! But the performance difference to your EF code is actually depressing :( I'm wondering if SQL CE is much slower than SQL Server. I had recently an example of bulk inserting 560000 rows into a table in SQL Server. It was with EF 4.1 though but the code was similar to your code in the question. The best I could achieve with EF was 164 sec which means: 3,7 sec for 12600 rows: http://stackoverflow.com/questions/5940225/fastest-way-of-inserting-in-entity-framework/5942176#5942176. I'm really wondering what could be the reason for your bad performance experience. – Slauma Aug 12 '11 at 09:53
@Kornelije the `SqlBulkCopy` class for standard SQL Server takes an `IDataReader` implementation into `WriteToServer`. There is an implementation of `IDataReader` that reads over `List<>` objects. This allows you to avoid making a copy of the data you want to import in memory (`DataTable` will duplicate all the values) so you can stream the values in from existing objects. It looks like the `SqlCeBulkCopy` implementation has this option too - this may help reduce your application's memory pressure :-) we did it recently with success. – Adam Houldsworth Aug 12 '11 at 09:53
@Slauma: don't know if it makes any difference, but it was calling SaveChanges 24 times with 525 elements instead of calling it once with 12600. But still... – Kornelije Petak Aug 12 '11 at 10:08
@Adam: Is there an existing library in .NET that enables automatic conversion from List<> to IDataReader? I assume it uses reflection, is the performance hit relevant in this scenario? – Kornelije Petak Aug 12 '11 at 10:08
@Kornelije I've posted an answer with links to an open source implementation. It likely uses reflection. The situation with performance is something you will need to review. Loading thousands of potentially heavy objects and then copying that all into a data table might make your memory usage a little questionable. I'm not saying there is a problem, just thought I'd mention it so you at least know there are other options. – Adam Houldsworth Aug 12 '11 at 10:11
The SqlCeBulkCopy library also now support IEnumerable as input (for example List<>) – ErikEJ Aug 12 '11 at 10:22
@Kornelije: In my example I was calling `SaveChanges` after 100 elements, so 5600 times. I don't think that calling `SaveChanges` multiple times in your example is the reason for the poor performance. – Slauma Aug 12 '11 at 10:29
@Adam: I am aware of duplication. However, the process I am doing is rather rare and it consumes 350 kB max. So I guess duplication is not an issue here. But it's good to know, in case I'd need to insert larger quantities of data. – Kornelije Petak Aug 12 '11 at 10:29
@Slauma: Did your table contain any foreign keys? I don't know if that would be the problem in my scenario, but who knows. – Kornelije Petak Aug 12 '11 at 10:31
@Kornelije: No, only 9 scalar properties, no relationships at all. – Slauma Aug 12 '11 at 10:34

score 6 · Accepted Answer · answered Aug 11 '11 at 14:52

6

You could consider using my SqlCeBulkCopy library, to bypass EF http://sqlcebulkcopy.codeplex.com

answered Aug 11 '11 at 14:52

ErikEJ

40,951
5
75
115

Is there a similar library which also allows fast updates to the database instead of just inserts? – Brady Moritz Oct 03 '15 at 06:00
It is built into the Ado.net provider, yes: http://erikej.blogspot.dk/2015/07/sql-server-compact-adonet-data-access.html @boomhauer – ErikEJ Oct 03 '15 at 06:49
OK, so does this mean the sqlcebulkcopy tool linked above can bulk copy style "upsert" into a database, and not just do inserts? Thanks – Brady Moritz Oct 03 '15 at 16:07

score 1 · Answer 3 · edited May 23 '17 at 11:58

1

To further ErikEJ's answer and your own sample, you can use the implementation of IDataReader over lists to stream the data into WriteToServer instead of duplicating the values via a DataTable. See this question:

Get an IDataReader from a typed List

I implemented this at work once, it doesn't appear to improve performance, but it appeared to reduce memory consumption.

edited May 23 '17 at 11:58

Community

1
1

answered Aug 12 '11 at 09:56

Adam Houldsworth

63,413
11
150
187

SqlCeBulkcopy library supports List<> directly as input. – ErikEJ Aug 12 '11 at 10:23
Since when? Is that in beta? I've D/Led the last stable version and it doesn't have any overloads for that. – Kornelije Petak Aug 12 '11 at 10:27
1

In 2.0 (no longer in beta... as of now) – ErikEJ Aug 12 '11 at 10:49

Inserting a large number of rows into SQL CE 4.0 with Entity Framework 4 (performance issue)

3 Answers3