Pulling a Britney

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.

2 Responses to “Pulling a Britney”

  1. Jeroen Pluimers Says:

    Adding this _AddRef/_Release stuff can be tricky. See a TInterfacedDataModule blog article I wrote back in 2009: http://wiert.wordpress.com/2009/08/10/delphi-using-fastmm4-part-2-tdatamodule-descendants-exposing-interfaces-or-the-introduction-of-a-tinterfaceddatamodule/

    –jeroen

  2. Larry Hengen Says:

    @Jeroen,

    Thanks for the tip! I will make a note to incorporate your article findings into my implementation.

Leave a Reply