SubSonic 3.0.0.4 Update out of memory

Question

When I try to update many posts (~50000) using SubSonic 3.0.0.4 after about 25000 updates i get an exception, out of memory. (Windows7, VS10, SQLServer2008R2)

var myTable = new SubSonicRepository<MyObject>(new MyDB());
getDataFromALargeList

foreach(post in LargeList)
{
    var myObject=GetMyObject(int myID)
    myObject.property1=..
    myObject.property2=..
    myTable.Update(myObject);
}
private MyObject GetMyObject(int myID)
{
    var myObject = new MyObject();
    var tbl = new SubSonicRepository<MyObject>(new MyDB());
    return tbl.Load(myObject, "ID", id) ? myObject : new MyObject();
}

Any ideas?

Answer 1

You will need to do your updates in batches if you really want to do this within your application code, because your post objects are exceeding the memory allocation limits of the .NET runtime.

Your code should count the total number of records that you need to process, and then select records for processing in batches of say 500, until done. This way, memory can be reclaimed by the garbage collector as your post objects pass out of scope (for each batch) and you progress through the processing of all the records.

Alternatively, this is probably one of those occasions where it is worth dropping down to raw SQL. If you do these updates often it could be well worth your time to write a procedure to do this inside the database.

Answer 2

I encountered the same problem with subsonic 3 ActiveRecord. A quick investigation of the generad code showed that every subsonic ActiveRecord object created a new db instance, a new Repository and a new table. The code is basically:

_db=new Northwind.Data.NorthwindDB();
_repo = new SubSonicRepository<Products>(_db);
tbl=_repo.GetTable();

That happens for every ActiveRecord instance you create.

If you are working with large data sets the LinqTemplates are less memory consuming. For instance I got a problem by loading 100000 records

 // ActiveRecord: slow, eventually ended in a OutOfMemoryException
var query = from p in Products.All()
            select p;
var products = query.ToList();

// LinqTemplates: runs fast with at least no memory footprint
// (except for the data itself)
var db = new Northwind.Data.NorthwindDB();
var query = from p in db.Products
            select p;
var products = query.ToList();

You should use the ActiveRecord patten with caution. It is great for pulling a single record from the db, update some values and persist the changes or even for fast updating multiple records (let's say up to 1000 recors) but for lage amount of data it is not the best choice, because of the overhead.

Anyway, pulling a record from the DB just to update a value is a bad choice, for both: ActiveRecord and LinqTemplates (unless you have a good reason for it, e.g. you implemented some business logic into your DAL objects.)

Have you considered just doing an update instead?

db.Update<MyObject>()
    .Set(x => x.property1 == 5)
    .Set(x => x.property2 == "Hello World")
    .Where(x => x.ID == 1)
    .Execute();