Shared Database Connection

[hepkat]

I would find it very useful if I could provide my own SQL connection to the classes and open the connection once for multiple transactions and procedures. Opening the database connection can be very expensive in terms of processing time.

I know this has been posted elsewhere as well, but I thought I would mention it in the proper forum.

Thanks.

[mike.griffin]

Ya, this suprises me because connection pooling does just that, what database are you using? Just curios ...

[hepkat]

MS Sql Server 2000.

I know that connection pooling drastically reduces overhead. But what I'm hoping to do to pass the instance of the sqlconnection as a ref object into an instance of the business entity. This connection is then used for all transactions that business entity performs. It allows me to call one 'Open' call and then do multiple transactions.

Does that make sense? Tell me if I'm wrong about the performance increases this will give. I'm open to any suggestions.

[mike.griffin]

Our dOOdads architecture already handles that, if you use the Transaction then a single connection is used.

Code: Select all

        public void Transactions()
        {
            TransactionMgr tx = TransactionMgr.ThreadTransactionMgr();

            try
            {
                Employees emps = new Employees();
                emps.AddNew();
                emps.FirstName = \"Jimmy\";
                emps.LastName = \"Lunch Box\";

                Products prds = new Products();
                prds.AddNew();
                prds.ProductName = \"dOOdads\";
                prds.Discontinued = false;

                tx.BeginTransaction();
                emps.Save();
                prds.Save();
                tx.CommitTransaction();
            }
            catch(Exception)
            {
                tx.RollbackTransaction();
                TransactionMgr.ThreadTransactionMgrReset();
            }

Everything you do between Begin/Commit uses the exact same connection.

[dmonsky]

Tests on my systems (in my environment) seem to indicate there is overhead that can be significant in some cases when issuing the Open & Close repeatedly. Some backend processes that are not interactive have been tested by me using a shared connection that remains open during intensive reads which have a much better execution time. In my original post some time ago when I posed the feasability of a 'shared' connection I was experiencing a big performance hit when using doodads versus my previous DAL which optionally could share an already 'opened' connection.

But in my environment these processes are very read intensive on many different doodad entities with no updates. I perform analysis against historical data and most of the time I need to be deep in the analysis by the processes before I can determine if I really need to continue with the analysis against an item and just move on to the next.

I tinkered with altering the Enlist and Delist methods to grab a singleton connection object that was held open and shared amongst different doodad entities during read operations. I ending up having problems and abandoned the idea wanting to wait for EntitySpaces before revisiting this issue.

[mike.griffin]

Okay, I understand, hopefully soon I can get some sample code posted for EntitySpaces

[mike.griffin]

This feature is in our next release 1.1.4, due out in a week or so ...