Subsonic ActiveRecord - Connections hanging when adding lots of records

Question

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?

Answer 1

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.

Answer 2

I was having the same problem using MySQL. Instantiating a new active record object then calling save caused a connection to be opened that never closed.

Found a fix that seems to work (thanks to kamsar http://github.com/subsonic/SubSonic-3.0/issues#issue/69) Apparently the issue is due to an unclosed Reader?!

Changing line 197 in SubSonicRepository.cs (3.0.0.3) to "using (DbDataReader rdr = provider.ExecuteReader(query))" (and wrapping the using in an appropriate scope to about like 218) seems to have solved it for me.

So the code ended up looking like this and no more connection leak!!!

            //var rdr = provider.ExecuteReader(query);
            using (System.Data.Common.DbDataReader rdr = provider.ExecuteReader(query))
            {
                if (rdr.Read())
                    result = rdr[0];
                // repopulate primary key column with newly generated ID
                if (result != null && result != DBNull.Value)
                {

                    try
                    {
                        var tbl = provider.FindOrCreateTable(typeof(T));
                        var prop = item.GetType().GetProperty(tbl.PrimaryKey.Name);
                        var settable = result.ChangeTypeTo(prop.PropertyType);
                        prop.SetValue(item, settable, null);

                    }
                    catch (Exception x)
                    {
                        //swallow it - I don't like this per se but this is a convenience and we
                        //don't want to throw the whole thing just because we can't auto-set the value
                    }
                }
            }

Answer 3

InnerSphere has the right answer. I couldn't vote up because I don't have the Rep Score and couldn't figure out how to simply reply/comment to his post.

The only thing to add is if you pull the code from GitHub, this fix is already included.

No problems after compiling and using the DLL created from the trunk code.

Answer 4

I had to add rdr.Close() right after obtaining the result, i.e.:

if (rdr.Read())
  result = rdr[0];

rdr.Close();