Linq and adding a large amount of data

Question

I need insert to large amount of data to sqlite db.
I uses Linq to Entities.

I have problem to adding large amount of data 1M+.
Not enough memory or a very long time.

This code - fast, but requires a lot of memory:

// query - IQueryable of DbfRecord
// db - ObjectContext
int i = 0;
foreach (var item in query) {
    db.AddToKladrs(new Kladr() {
        Id = item.GetField(0),
        ParentId = item.GetField(1),
        RegionId = item.GetField(3),
        Name = item.GetField(2),
        Index = item.GetField(4)
    });
    if(++i % 4000 == 0)
        db.SaveChanges(SaveOptions.AcceptAllChangesAfterSave);
}

This code - not resource-intensive, but very slow:

// query - IQueryable of DbfRecord
// db - ObjectContext
foreach (var item in query) {
    db.ExecuteStoreCommand("insert into [Kladr] values({0}, {1}, {2}, {3}, {4})",
        item.GetField(0),
        item.GetField(1),
        item.GetField(3),
        item.GetField(2),
        item.GetField(4)
    );
}

I missed the try-catch construction and ghost types.

Help me find the best solution!

Saving every row or every 4000 rows is a pretty extreme split. Have you tried saving every, say, 100 or 200? — Ben M, Jul 17 '11 at 15:58
Of course, then decreases productivity. But 4000 is chosen at random =) but it works fast! — Artem Zubkov, Jul 17 '11 at 16:03

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

1

You can use the SqlBulkCopy for copying large amounts of data. Havn't tried it with SQL lite but it should work.

Link 1
Link 2

Update :

Here is a good answer by Marc Gravell. how-to-do-a-bulk-insert-linq-to-entities

edited May 23 '17 at 10:34

Community

1
1

answered Jul 17 '11 at 15:55

Jethro

5,896
3
23
24

this don't work with `DbConnection`. I cann't conver `SQLiteConnection` (`DbConnection`) to `SqlConnection` =( i uses `System.Data.SQLite` – Artem Zubkov Jul 24 '11 at 10:20
I just found that Bulk insert is not supported by sqlite, however you can use a transaction which will do the same thing. – Jethro Jul 24 '11 at 15:22

Linq and adding a large amount of data

1 Answers1