Subsonic ActiveRecord – Connections hanging when adding lots of records

subsonicsubsonic3

I'm getting a familiar Timeout SqlException when I add a lot of new records quickly using Subsonic ActiveRecord and Sql Server.

Timeout expired. The timeout period
elapsed prior to obtaining a
connection from the pool. This may
have occurred because all pooled
connections were in use and max pool
size was reached.

I have managed to replicate this issue with the following simple block of code:

    for (int i = 0; i < 1000; i++)
    {
        var address = new Address();
        address.City = "TEST";
        address.Save();
    }

The code will add records until it reaches 100 (the Max Pool Size, probably not coincidentally) at which point it fails out with the above exception. After that, the connections are held for 5-10 minutes or so during which time all attempts to use the database give the above exception.

Interestingly, I have found that running this on my local machine in VS (same codebase against the exact same database instance) the code succeeds in adding all 1000 records. Perhaps the latency between here and my host just slows the database calls down enough?

Given that it works locally I suppose this isn't a Subsonic issue directly, but is there some mistake I am making or any way I can change my code or configuration to handle the rapid opening and closing of connections that occurs when adding a lot of records serially?

Best Answer

I ran a perf test on AR and SimpleRepo which looped 100,000 records and beat on the thing pretty hard - connection issues are really the first stop when perf-tuning.

That said - we did have an issue in pre-release with a reader being left open :). I fixed it a few months back - so can you confirm that you have the latest bits?

Also - yes latency can be weird. I don't know the details but it looks like that's it.

Related Topic