Shared Database Connection

Is there a feature you'd like us to add? Post it here.

Shared Database Connection

Postby hepkat on Tue Apr 26, 2005 2:37 pm

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.
hepkat
Lurker
 
Posts: 9
Joined: Fri Apr 22, 2005 11:16 pm

Postby mike.griffin on Tue Apr 26, 2005 3:16 pm

Ya, this suprises me because connection pooling does just that, what database are you using? Just curios ...
User avatar
mike.griffin
Site Admin
 
Posts: 3290
Joined: Sat Apr 03, 2004 6:10 am
Location: Indianapolis, IN

Postby hepkat on Wed May 11, 2005 5:42 pm

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.
hepkat
Lurker
 
Posts: 9
Joined: Fri Apr 22, 2005 11:16 pm

Postby mike.griffin on Wed May 11, 2005 5:52 pm

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.
User avatar
mike.griffin
Site Admin
 
Posts: 3290
Joined: Sat Apr 03, 2004 6:10 am
Location: Indianapolis, IN

What about the overhead of the Open & Close

Postby dmonsky on Wed May 11, 2005 7:46 pm

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.
dmonsky
Sergeant
 
Posts: 26
Joined: Fri Sep 24, 2004 2:47 pm
Location: Louisville, KY

Postby mike.griffin on Wed May 11, 2005 8:16 pm

Okay, I understand, hopefully soon I can get some sample code posted for EntitySpaces
User avatar
mike.griffin
Site Admin
 
Posts: 3290
Joined: Sat Apr 03, 2004 6:10 am
Location: Indianapolis, IN

Postby mike.griffin on Wed Sep 14, 2005 12:52 am

This feature is in our next release 1.1.4, due out in a week or so ...
User avatar
mike.griffin
Site Admin
 
Posts: 3290
Joined: Sat Apr 03, 2004 6:10 am
Location: Indianapolis, IN


Return to Enhancements

Who is online

Users browsing this forum: No registered users and 2 guests

cron