Actually, setting the isolation level locally has yielded a marked increase in performance, from 20s per transaction to 5s per transaction.
Microsoft's literature on the subject even recommends using the technique in this article: http://support.microsoft.com/default.aspx?scid=kb;en-us;295570 Distributed Transactions If you mark your component's transaction attribute as Required, Supported, or Requires New, COM+ creates a transaction for you. When a transaction is created, COM+ must specify the starting isolation level that is to be used for that transaction. In COM+ 1.0, the default isolation level is SERIALIZABLE. In COM+ 1.5, you can specify the default isolation level. You can use the SET ISOLATION LEVEL SQL command or optimizer hints to increase or decrease the isolation level during the transaction. If you set the ADO Connection object's IsolationLevel property to try to change the isolation level, you do not affect a component in a COM+ declarative transaction because this setting only affects the next transaction that you begin. > -----Original Message----- > From: Discussion of advanced .NET topics. [mailto:ADVANCED- > [EMAIL PROTECTED] On Behalf Of Sorin Rojea > Sent: 19 October 2005 16:19 > To: [email protected] > Subject: Re: [ADVANCED-DOTNET] COM+ Transaction Isolation Level > > AFAIK XP uses COM+ 1.5 where the distributed transaction isolation level > can be set, as opposed to Win2k which uses COM+ 1.0 , where the isolation > level is hardcoded to SERIALIZABLE. > In COM+ 1.0 the isolation level can be changed only if you manually start > a distributed transaction and enroll that in an existing transaction > stream. > > Setting the isolation level at the ADO connection level will not help, > once the connection is enlisted in a distributed transaction is the > distributed transaction isolation level the one that takes precedence. > > I'm suggesting to either consider using COM+ 1.5 which comes with XP and > Win 2003 server, or do the workaround that I mentioned above if you need > to deploy on Win2k. > > Best of luck, > Sorin Rojea, > Senior Software Developer, > http://www.iq-l.com > > > -----Original Message----- > From: Bradly Kelly [mailto:[EMAIL PROTECTED] > Sent: Monday, October 17, 2005 4:34 AM > To: [email protected] > Subject: [ADVANCED-DOTNET] COM+ Transaction Isolation Level > > I developed an application using COM+ Distributed Transactions on my XP > machine, using Isolation level Read Committed. Unbeknownst to me this > is not supported on the target machine, a Windows 2000 server. The > performance is dismal after defaulting to isolation level Serializable. > > My C# application calls several COM components, across two databases, > and each of these calls passes a connection string. I have read in a > few places that adding a SQL statement at the top of each stored proc > that is called, to 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED' will > help my performance. Instead of doing that, I'm thinking of executing > this as a command in each method, just after the connection is opened, > to cover cases where the method calls more than one stored proc, and to > avoid change control on all the stored procs. > > Has anyone else dealt with this constraint before, and will my intended > work-around actually work? > > > > > ________________________________________________________________________ __ > _________________________ > > "The information contained in this e-mail is confidential and may contain > proprietary information. > It is meant solely for the intended recipient. Access to this e-mail by > anyone else > is unauthorised. If you are not the intended recipient, any disclosure, > copying, > distribution or any action taken or omitted in reliance on this, is > prohibited and > may be unlawful .No liability or responsibility is accepted if > information or data is, > for whatever reason corrupted or does not reach its intended recipient. > No warranty is > given that this e-mail is free of viruses. The views expressed in this e- > mail are, unless > otherwise stated, those of the author and not those of FirstRand Bank > Limited or its management. > FirstRand Bank Limited reserves the right to monitor, intercept and block > e-mails addressed > to its users or take any other action in accordance with its e-mail use > policy. > Licensed divisions of FirstRand Bank Limited are authorised financial > service providers > in terms of the Financial Advisory and Intermediary Services Act 37 of > 2002." > > ________________________________________________________________________ __ > _________________________ > > =================================== > This list is hosted by DevelopMentor(r) http://www.develop.com > > View archives and manage your subscription(s) at > http://discuss.develop.com > > =================================== > This list is hosted by DevelopMentor(r) http://www.develop.com > > View archives and manage your subscription(s) at > http://discuss.develop.com ___________________________________________________________________________________________________ The information contained in this e-mail is confidential and may contain proprietary information. It is meant solely for the intended recipient. Access to this e-mail by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted in reliance on this, is prohibited and may be unlawful .No liability or responsibility is accepted if information or data is, for whatever reason corrupted or does not reach its intended recipient. No warranty is given that this e-mail is free of viruses. The views expressed in this e-mail are, unless otherwise stated, those of the author and not those of FirstRand Bank Limited or its management. FirstRand Bank Limited reserves the right to monitor, intercept and block e-mails addressed to its users or take any other action in accordance with its e-mail use policy. Licensed divisions of FirstRand Bank Limited are authorised financial service providers in terms of the Financial Advisory and Intermediary Services Act 37 of 2002. ___________________________________________________________________________________________________ =================================== This list is hosted by DevelopMentor® http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com
