CODEWITHSUNDEEP
c#.netlinq-to-sql
werner giam
werner giam

Reputation: 1

Memory leak with LINQ to SQL

I have a program which will read 1 million records from the database and insert the records into another table after some processing. So, the program calls a DAO API to get 1 million records and loop through them in memory. The insertion is also using a DAO API to do the insert. Each DAO API part is implemented as

    public static void Main(string[] args)
    {
        List<TableA> tableAs = GetTableAs();
        TableB tableB = null;
        foreach (var tableA in tableAs) {
           tableB.id = tableA.id;
           // ...
           // here I copy/modify records from table A
           // ...
           InsertTableB(tableB);
        } 
    }

    public static List<TableA> GetTableAs()
    {
        using (var ctx = new TableDataContext())
        {
            var entities = from e in ctx.tableAs
                           select e;
            return new List<tableA>(entities);
        }
    }

    public static void InsertTableB(TableB tableB)
    {
        using (var ctx = new TableDataContext())
        {
            ctx.tableBs.InsertOnSubmit(tableB);
            ctx.SubmitChanges();
        }
    }

My program will experience "Out of memory" exception after 500k inserts and it is very consistent. And i notice that the memory usage keeps increasing while looping. I even force garbage collection also cannot reclaim any of the memory. Is there anything wrong with my interact with LINQ so that causes the memory hold up and not being released. Any help will be much appreciated.

Upvotes: 2

Views: 3288

Answers (3)

donRumatta
donRumatta

Reputation: 842

I've faced the same problem when tried to insert over 3000000 rows using linq2sql. OutOfMemoryException was appearing after 700k of inserts. Finally, I just dispose previous DataContext and create new after 100k and even don't turn off tracking.

Upvotes: 1

mazatsushi
mazatsushi

Reputation: 1121

The problem most probably lies not with a memory leak in the .NET CLR, but perhaps the sheer volume of memory that the above LINQ to SQL code requires to execute.

Please refer to this MSDN blog posting first: http://blogs.msdn.com/b/tom/archive/2008/04/10/chat-question-memory-limits-for-32-bit-and-64-bit-processes.aspx

Since I do not have any hardware specifications, I shall assume a memory limit of 2800MB like in the article above.

1,000,000 records <= 2800MB
1,000,000 records <= 23,488,102,400 bits
1 record <= 23488.1024 bits
1 record <= 2.8672KB

In theory, this means that each record copied into memory from Table A should be no more than 2.8672KB.

However the actual value is usually lower due to other overheads involved with the CLR, and this is reflected in an OutOfMemoryException thrown after 500,000 records deep copied from Table A then inserted into Table B.

Granted there are ways to increase the number of records that can be kept in memory, like other posters have suggested, but the problem will most probably surface again with an increase in the number of records in Table A.

A viable alternative would perhaps be to create a stored procedure in the underlying SQL Server database and execute that instead.

Upvotes: 0

Tenerezza
Tenerezza

Reputation: 394

First and foremost I think you should reconsider the way you write that code, right now it's insane inefficient, example you recreate the context every time, why not just keep ONE context?

Better yet rewrite it into a single SQL statement would save you all hassle.

Recreating the context is pretty heavy CPU wise, and if you just need one connection, as you show with the example above, creating it more then once is waste of resources.

Secondly LINQ to SQL have Change tracking occurring on every object you create, edit, modify, so it knows what objects to deal with. And could possible be the source of your memory problem.

So I would recommend using ObjectTrackingEnabled = false and see what happens.

And lastly you want to peek at Bulk Inserting.

Upvotes: 1

Related Questions