Archive for January, 2011

Pulling a Britney

Tuesday, January 11th, 2011

A couple years back I was asked to develop an application for managing facility construction for an O&G firm.  The previous incarnation of the application was written using custom domain objects against an Oracle 8 RDBMS.  I agreed with the approach in using domain objects, but decided I would make my life easier and use an earlier version of what was to become hcOPF so I didn’t have to write the persistence logic and could use object binding.

Things proceeded well, but the SME (Subject Matter Expert) kept asking for more, and I ended up managing a pretty large object graph within the application.  It was then that I got a mysterious error message saying I had exceeded the query limit.  Me with my little application?  It seemed ludicrous that Oracle couldn’t handle a few queries, until the DBA told me it was 100 simultaneous queries per connection.  At first I couldn’t believe that I was using so many, until I did some investigation.  At that time I was creating a query whenever I needed to load a list or a single object, so it didn’t take long with an object graph of any depth to use that many queries.  I quickly implemented ThcQueryPool, and pretended that incident never happened.

That is, until quite recently.  On my current project, I needed to support Stored Procs and executing queries directly, and wanted to do so in a database independent manner.  That is, after all, one of the advantages of using an OPF.  I implemented this support in the form of interfaces that could be implemented by the TQuery and TStoredProc type components in each supported DAL (data access layer).  To speed up implementation, I created a couple methods on the ThcFactoryPool to return either a IhcStoredProc or IhcQuery.  I assumed this interface would be reference counted, and the components would be destroyed at the end of the routine in which they were used.  I was wrong, and just discovered this when SQL Server gave me a similar kind of message.  All I can say is “OOPS, I did it again!”.

There is a good explanation of this issue on StackOverflow.

In order to fix this situation I have implemented _AddRef, and _Release methods in the ADO DAL objects so reference counting is now used.  I will either have to propagate this solution to the other DALs, or re-factor to enable the use of Query and StoredProcedure object pools.