18 Common Programming Traps in MTS

icrosoft Transaction Server (MTS) is certainly not new, but there are still a lot of common design and programming errors being made. In this article I will show you 18 examples of such errors (together with proposed solutions). A few of the problems will lead to erroneous results and others will give performance problems.

I did a Gallup investigation asking one million experienced MTS programmers what they thought were the most common mistakes when designing and programming for MTS. Hmmm How do you know that I am lying here? Correct, there are not that many experienced MTS programmers in the world. In Sweden we say something like “A lot of talk and little action” and that applies perfectly in this area. “Everybody” is talking about MTS, but only a few are building applications for that environment so far. I am very sure this is changing rapidly, but it means that a lot of programmers are running into a whole new world of new problems. It’s boring seeing the same old problems, isnt it?

In this article (split into two), I will show you some common problems. I expect the reader to have at least some experience of MTS and a fair bit of knowledge about database systems, VB and COM. If you have a lot of experience, you will find many of the tips painfully “easy”, but I hope that even the “real MTS programmers” will find a tip or two, so please bear with me. If on the other hand you don’t have the experience I expect, you will find my purposely short explanation of certain techniques inappropriate.

The problems/tips are totally unordered and what is a simple one for some guys, could be totally new and important for some others. I will focus on design and programming here and not so much on deployment. There is a lot to do wrong in deployment too, but that is another story.

COM+
Did I get your attention now when I mentioned COM+? Good. Even if you will only be building for COM+ in the near future (which I believe is uncommon), I think this article will be interesting anyway. Most of the tips are usable in COM+ too. COM + MTS = COM+, at least in a way.

Metrics
As you probably know, it is hard to collect metrics about performance. A lot of factors are involved and the problem is no easier when you are trying to collect metrics for distributed systems. So please be aware that the comparisons I will show you are from my system and could be quite different on yours.

I would also like to state that I haven’t spent any time at all on optimization here. I have only written the code from top to bottom. But there is probably as much to win by optimizing both the good and the bad case, so To make the difference apparent I will do some looping. That may sometimes look stupid, but the difference will at least be obvious.

When I taught students at the university, I sometimes had them do performance tests and tuning on database systems. Very often the students got disappointed because their tuning efforts only gave 20% better performance, for example. You and I know that this is often a great deal. When a salesman tells you that you will get 5% off if you buy a new Saab you will jump up and down with joy and buy it at once!

In some of my tests, the real performance difference will drown in overhead such as calls over the network. So the “real” difference could be even larger in many cases than what I say below! (See for example problem 3, 5, 7, 8, 14 and 15.) Remember also that the difference will be larger for many cases when more users are banging transactions. All the tests have been done with only one user.

Please also be aware that I am not giving production code, but simply examples to illustrate certain behaviour. Don’t cut the code and use it in your apps! (I hope this is an unnecessary warning!)

The Test Environment
I used one Pentium II 266, 128 MB RAM as the client running the test driver, and one Pentium II 266, 128 MB RAM as the server running MTS 2 and SQL Server 7. The database was almost empty and the network was “free” except for my tests.

Enough said. Let’s move on to the problems.

Problem 1: Creating Objects Too Late in the Client
Let’s say the client needs an MTS object in a form. One typical solution before MTS was used would be to declare the object at the module level and create the object in the load-event. But now the programmer knows that he will use an MTS object and he also knows that it is good to think JIT activation and ASAP deactivation. So he creates the object at the beginning of the event that will need the object.

'The event, for example a click on a button
Dim obj As TestApp.IPerson
Set obj = CreateObject("TestApp.Person")
obj.DoSomething
Set obj = Nothing

This is no more effective than:

'Declaration
Private m_obj As TestApp.IPerson
'Form load
Set m_obj = CreateObject("TestApp.Person")
'The event, for example a click on a button
m_obj.DoSomething
'Form unload
Set m_obj = Nothing

But won’t the first example be less resource consuming? Well, the difference is not that big. At first, the object is not activated until DoSomething is called. The client will only get a reference to a context wrapper. No locks in the database or such will be held. In my test I fired the event 1000 times and the second code snippet was 1600% faster.

Problem 2: Haven’t Realized the Transaction Isolation Level
The default transaction isolation level for ADO against SQL Server is Read Committed, but when MTS is involved it will be a higher level, namely Serializable. Not thinking about that at the design stage could really hurt you! (If you are unfamiliar with transaction isolation levels, see a general textbook about database systems or the SQL Server manual.)

If for example you do something like:

SELECT SUM(numericcolumn) FROM table

in an MTS object that takes part of the first transaction, no other transaction could do anything that will change the result of the operation above. And this applies as long as the first transaction lives. Let’s say that the first transaction is long and takes one second to finish, then no other transaction could update the numeric column in any row in the table. Of course, you should not insert new rows either, as that will change the result. In certain e-commerce applications that could be a huge bottleneck.

Something else to think about is the amount of locks that will be held. When you are trying to build scalable applications it is extremely important not to waste resources. When the SQL-statement from above is used in an MTS object running in a transaction you will get a lot of locks. In my test I did this on a table with 1000 rows (which is of course almost nothing) and when I counted the rows in the syslockinfo table I got 352 locks instead of 0, which I got when the same SQL statement was used from an MTS object not running in a transaction.

I made a similar test from the base-client by running an operation called GetById(vlngID) 100 times with different values for the id to use when reading from a table in the database (and without doing SetComplete after each). In a transaction this resulted in 70 locks before I was finished. Without the transaction there were no locks.

Exercise for the reader: Does it matter if you just for convenience use “Require Transaction” everywhere?

You can downgrade the isolation level by using an optimizer hint (like NOLOCK for example) but take care because youre on your own then.

Problem 3: Not Using CreateInstance
The way to create objects in VB is to use New or CreateObject. The recommendation for MTS is to use CreateObject from the base-client (see next problem for more information about the problem with New), BUT when inside an MTS object you should not use CreateObject to create another MTS object. Nor should you use New. Instead you should use CreateInstance on the ObjectContext interface. Otherwise the new object will run in another activity, separate from the first one. It will probably also run in another STA (Single Threaded Apartment). This leads to two problems. First, if the objects are transactional, you will get two transactions. Not good. Secondly, you will get a proxy/stub-pair between the objects leading to worse performance. Let’s take some code that is the same in both cases first:

'In an MTS object: 
'Declaration
Implements ObjectControl
Private m_objCtx as ObjectContext
'One of the operations of the
'ObjectControl-interface that I implement:
Private Sub ObjectControl_Activate()
Set m_objCtx = GetObjectContext
End Sub

And now the bad example:

Public Sub DoSome()
Dim obj As TestApp.IAnotherMTSobject
'Incorrect:
Set obj = CreateObject("TestApp.AnotherMTSobject")
obj.DoSomeUpdate
'...
m_objCtx.SetAbort 'Since something failed...
End Sub

If in DoSomeUpdate you do some update against the database and a SetComplete, then the update is committed even though you didnt like it! The code should look like this instead:

Public Sub DoSome()
Dim obj As TestApp.IAnotherMTSobject
'Correct:
Set obj = _
m_objCtx.CreateInstance("TestApp.AnotherMTSobject")
obj.DoSomeUpdate
'...
m_objCtx.SetAbort 'Since something failed...
End Sub

Now, both DoSome and DoSomeUpdate run in the same transaction and nothing will be saved to the database in this example. When running both the operations 100 times, the second is 110% faster since there will not be a proxy/stub-pair between the objects.

Problem 4: Not Using New
Very often when developers have heard about the problems with using New and MTS (and the trouble is that there is a risk that you won’t get a context wrapper since the SCM (Service Control Manager) under some circumstances won’t be involved), they stop using New even to create objects that are not MTS objects, such as ADO recordsets. Of course, CreateObject works too, but it is slower since the ProgID used with CreateObject has to be translated into a CLSID by the SCM. (Another drawback is that if you misspell the ProgID, the compiler won’t tell you about it.)

Set rst = CreateObject("ADODB.Recordset")

is slower than

Set rst = New ADODB.Recordset

I did a little test by running a loop in an MTS object, creating 1000 ADO recordsets. When I used New instead of CreateObject in the loop, it went 200% faster.

Problem 5: Not Using SPM
Most MTS programmers have heard about the “SPAM” or SPM (Shared Property Manager), but few use it. (SPM could be used for sharing global data between different MTS objects, and users. See a textbook about MTS for more information.)

Let’s say you have static data like the VAT percentage, then it is not a good idea to read it from the database all the time when you can cache it in SPM. When I did 100 calls from a base-client to an MTS object getting the VAT-percentage it was 130% faster to use the SPM instead of going to the database all the time.

My example is a very simple situation. You can easily come up with scenarios where the percentage will be much higher.

Problem 6: Believing that Objects Are Serialized When Moved Between Processes and Hosts
A common first approach to moving data is to use objects and collections of objects. This is good, but not when it comes to distributed systems. The reason is that the data is not serialized automatically. The universal marshaller can’t do that. So you will only send references over the network and when a receiver of a reference calls the object, the real call will go by the reference back to the original location.

A very typical scenario where this is obvious is if you are in an MTS object and want to fetch 1000 rows from the database and for each row build an object (you are object-oriented right?) and add the object to a collection. When finished you return the collection to the client. At the client, inspection of four properties on each object will take place. This is slower than just returning a disconnected recordset to the client! (A disconnected recordset doesn’t have the same marshalling problem. It will marshal just fine.)

Client code for the slow case:

Dim objPersons As TestApp.IPersonsCol
Dim objPerson As TestApp.IPerson
Dim colPersons As Collection
Set objPersons = CreateObject("TestApp.Persons")
Set colPersons = objPersons.GetAll()
For Each objPerson In colPersons
With objPerson
lngId = .Id
strFname = .FName
strLname = .LName
strPhone = .Phone
End With
Next

Client code for the fast case:

Dim objPersons As TestApp.IPersonsRst
Dim rstPersons As ADODB.Recordset
Set objPersons = CreateObject("TestApp.Persons")
Set rstPersons = objPersons.GetAll()
With rstPersons
Do While Not .EOF
lngId = !id
strFname = !fname
strLname = !lname
strPhone = !phone
.MoveNext
Loop
End With

The fast case is more than 3000% faster. The orthodox approach to object-orientation sometimes comes at a high price here, right?

Problem 7: Too Many Server Packages
Many developers think that an MTS package should be the same as a package in their UML (Unified Modeling Language) diagrams. So for example they will have MTS packages called Application, Domain and Data Access. If those packages are not library packages, there will be process communication between them leading to proxy/stub-pairs and a lot of unnecessary overhead.

When I tested this I had only two packages. The base-client did 100 calls to package A and in each call the operation did a call on an operation in another object (sending a ByVal string). If the second object lived in the same package as the first one, it was 1500% percent faster than if they lived in separate server packages.

You will not have this difference if the second object lives in a library package, since library packages load in the process of the first package.

Problem 8: Stored Procedures Not Useful Any More
This is a misunderstanding that occurs sometimes. Just because you have MTS and n-tier applications, it doesn’t mean you are not allowed to use stored procedures. In fact, you will pay a performance penalty if you dont!

From a base-client I called an MTS object 100 times. Each time the object did three updates against the database. In the second case the object only did one call to a sp, which did the same three updates. The second case was 50% faster. And this was when I had MTS and the database server running on the same machine. Do you think the difference will be less if I move the database to another server?

Problem 9: Not Remembering That the Administrator Rules
It’s not something you like to think about, but the administrator of the MTS server decides a lot about your application. J

If you design an MTS component for a specific transaction setting, then it could lead to a catastrophe if the transaction setting is incorrect. The same goes for security. A simple solution to this could be to check in the operation that you have the environment that you think, otherwise you raise an error.

If Not m_objCtx.IsInTransaction Then
Err.Raise jnskErrNotInTransaction, _
"TestApp.CriticalStuff", _
"This component is not correctly " & _
"installed regarding " & _
"transaction support!"
End If

You can use the same technique as shown above to check IsSecurityEnabled.

Dont forget that there are good reasons for the declarative way of handling transaction and such. Do not try to overrule that by code. I’m just trying to show that in specific situations, it could be good to know a little about the environment.

Problem 10: Catching Errors Without Setabort
It is very easy to make mistakes with error handling when you build an old client server application, but this will usually only affect one user at a time. If you make similar mistakes in distributed applications, you can affect the lives of hundreds of users, for example, by locking out a resource such as a table in the database.

Take this code snippet in a transactional MTS object for example.

  strSQL = "UPDATE table SET column = 'value'"
con.Execute strSQL
con.Close
'Let's assume an error will happen here
'...
m_objCtx.SetComplete
SetAbortTooLate_Err:
Err.Raise jnskErrSomething, "...", "..."
m_objCtx.SetAbort

The above will cause something bad to happen. I didnt forget to catch the error, but the SetAbort will not happen since it comes in too late. Since the MTS object is transactional, a lot of locks will be held in the database until the object is deactivated or the transaction dies (by default after 60 seconds).

Let’s assume the error is presented to the user in a message box. If the user presses OK within 60 seconds, the update will be committed. If not, all the locks (and there can be many in this case!) will be held for 60 seconds and then the transaction will be rolled back automatically.

You dont need this behaviour. Be very sure you catch all errors, use SetAbort and use it before raising the error to the client!

Problem 11: Getobjectcontext In Initialize
A common way of programming for MTS is to check if you have got an ObjectContext-instance or not. If you use this style (I’m not very fond of it actually) you can use the same code inside and outside MTS without any recompilation. An example:

If m_objCtx Is Nothing Then
Set obj = CreateObject("TestApp.Person")
Else
Set obj = m_objCtx.CreateInstance("TestApp.Person")
End If

The above, in combination with getting the context object in Initialize, will lead to undesired results. The Initialize procedure is trying to get the context object too early. So you will not get any. Therefore the m_objCtx variable will be Nothing and you will always be using CreateObject. If you read problem number 3, then you know what will happen.

Private Sub Class_Initialize()
'Not good:
Set m_objCtx = GetObjectContext
End Sub

Instead you should implement ObjectControl interface and use the Activate procedure to set the m_objCtx as shown below:

Implements ObjectControl
Private Sub ObjectControl_Activate()
'Good:
Set m_objCtx = GetObjectContext
End Sub

Problem 12: Too Many COM Exceptions
Perhaps you have heard that Err.Raise is slow? What happens is that it is converted to a COM Exception, and a COM Exception will give you several roundtrips. Often programmers say that when an error occurs, it is not important to give the user incredible performance. I agree.

The reason I write about this problem is that it is a common programming style to use COM Exceptions instead of return values, to inform about success/failure of an operation. (Failure in this case is not a real error.) If you use this style then you can call a Sub, stating something. In, for example 70% of situations, everything is fine. In the remaining 30% of situations, you were wrong and you get an exception. In my test calling an MTS object 1000 times from a base-client, the “old” approach of using a function returning True or False gave 150% better performance than the “new” approach when the operation generates an exception.

“New” exception style:

  On Error Goto FalseStuff
obj.DoSomething
'Do the "True"-stuff...
Exit Sub
FalseStuff:
If Err = jnskErrFalseStuff Then
'Do the "False"-stuff...
Else
'...
End If

“Old” (but faster) C style:

Dim bolOK As Boolean
bolOK = obj.DoSomething()
If bolOK Then
'Do the "True"-stuff...
Else
'Do the "False"-stuff...
End If

Problem 13: Msgbox
You probably know that you should use Unattended Execution for your COM servers, especially if they are to be used in MTS. But when you would like to do some quick and dirty debugging of an MTS object, then you perhaps deselect Unattended Execution and use som MsgBox-statements in the MTS object. That works fine if you also run the package as Interactive User. Everybody is happy.

Let’s assume you find and correct the bug (you always do, right?), but you forget one of the MsgBox-statements. When the code is executed in production again, the message box will perhaps be apparent on the server (depending on which user is running the package), but definitely not on the client. If the object is transactional, there will be a wait-state for the transaction until it gets the timeout. There may be a lot of locks, making the rest of your users unhappy to say the least.

Of course, the client will hang, as will the operation in the object, until somebody acknowledges the message box. If you don’t run the package as Interactive user, then there will be no visible message box to acknowledge. The message box will be put on a virtual screen. If you use Unattended Execution for your COM server (which you definitely should!), the problem will not appear since the MsgBox will not be “used.” Instead there will be an event that you can inspect in the Event Viewer.

In the scenario above, there were a lot of “ifs.” Perhaps you think the case is only theoretically possible? I know from painful experience that this happens. Why not make it theoretically by wrapping your usage of the MsgBox-statement into a Sub of your own where you inspect the environment to see if it is MTS, to escape this possible problem in your production application!

Problem 14: “Old” Handling of Surrogate Keys
In 1989 I was working on a large project where we had a problem with our surrogate keys. (As a matter of fact we had several problems. Most projects do.) We used a table that had information about which should be the next value for each surrogate key. The problem we had was that this table became an incredible bottleneck.

Nowadays you don’t have to take care of this yourself. In SQL Server for example you can use Identity if you think that functionality is good enough for you. Despite this, you will often see the old way of doing things if you read books and such. If you do it yourself you will of course get flexibility, but be aware that you can suffer when it comes to performance. And if performance goes down here, the throughput will too.

A common reason for using the old approach is that you would like the client to know what the new key value was. When you use Identity you will not get that information automatically through ADO to your disconnected recordset. You can for example use a stored procedure to do the insert and return @@identity to tell the client which was the new value. (Whether or not the client needs to know the new value is not the only factor to consider. The choice is not trivial.)

In a test I compared the old and new solution, and when I let a base-client make 100 calls to an MTS object that created a new row in a table with a surrogate-key, I got 200% better performance when I used a stored procedure that returned the @@identity-value than when I wrote the code myself in the MTS object with a table that remembered which was the next value for the id column.

If you use a SPM-based solution you could make the difference less, but then you get new problems, as you would if you had more than one MTS server. You can of course also move the code to a stored procedure, but there will probably still be a time difference.

Observe also that the client doesn’t always have to know the new id value. Then you have one less reason for not using the built-in functionality in the database product.

Problem 15: Sloppy SQL
You have heard that the stuff you put in MTS is called the Business Rules Tier. The database is degraded to a place where you just spool out the state. Don’t listen to this any more. You have to be as careful with how you handle the database as usual. If you write bad SQL, you will get bad performance. It is still as simple as that.

In my test, my base-client made 100 calls for the name of a dog for a certain persons name. In the first case the MTS object called the database to find the id for a persons name first. Then it went to the database again to fetch the dogname, using the persons id as a foreign key. In the second case the MTS object did a join between the person and dog table and used the persons name as the criteria. The second case went 40% faster.

First case:

strSQL = "SELECT id FROM person WHERE fname = 'Jimmy'"
With rstPerson
.Open strSQL
If .RecordCount > 0 Then
strSQL = _
"SELECT dogname FROM dog WHERE person_id = " & _
!id
rstDog.Open strSQL
NameOfDog = rstDog!dogname
End If
End With

Second case:

strSQL = "SELECT dogname FROM person INNER JOIN " & _
"dog ON person.id = dog.person_id WHERE fname = 'Jimmy'"
With rst
.Open strSQL
If .RecordCount > 0 Then
NameOfDog = !dogname
End If
End With

In my test I ran the database and MTS on the same machine (see part 1 of this article for further information about the environment for the tests). The difference will of course be larger if I move the database to another machine.

Problem 16: A Lot of Small Property Calls
In the “old” days of object orientation we were told that objects had behaviour and state. A common implementation would be to do some stuff and then check what the new state would be. Something like this could be typical:

With objPerson
.GetById intI
strFname = .FName
strLname = .LName
strPhone = .Phone
End With

Unfortunately this style is not very effective in the distributed scenario since you get a lot of roundtrips. In my test (when I looped 100 times) the version below was 33% faster, and please observe that the number of properties is not very large. I guess you sometimes have objects with more than four properties? You sometimes have to weigh pureness in object orientation against effectiveness.

objPerson.GetById intI, strFname, strLname, strPhone

OK, the second version is not very good-looking, especially not if you will send a lot of properties, but then you can use other techniques to send all the values in one call.

The first style is also an example of stateful programming and the second is an example of stateless programming. MTS really favours the second and when it comes to transactions, there is no discussion about it. Get in fast and out even faster!

Problem 17: Thinking That Byref Is Faster as Usual
For several years you have heard that calling functions with parameters will be more effective if you transfer the parameters by reference than by value (at least when the datatypes are large). That is obvious since you will not have to do any time consuming copying of data, just transfer a reference. Don’t think this is still valid in all situations.

In my test my base-client called an operation in an MTS object 10 times. Each time transferring a 200 K string. In the first case the string was transferred ByRef and in the second case ByVal was used. The second case was 150% faster. The reason is simple. Marshalling will only have to take place in one direction, namely at the call and not on the return also.

My recommendation is (and as a matter of fact was before MTS too) to use the semantically “correct” way of transferring data. If the receiver needs to update the data, use ByRef, if not use ByVal. Simple, secure and actually faster in some situations nowadays.

(As a little comment to the above I would like to state that I am sorry that VB doesn’t support [out] as in COMs IDL. Therefore you can’t just return data, except as [retval] of course. Remember this, for example, when you want to create a large array on the client, but the array has to get its data on the server. Instead create a small array on the client and ReDim it on the server or return it as the return value of the function.)

Problem 18: Too Little Testing
Last but not least, distributed applications require more testing. Period. Don’t make the mistake of only testing outside MTS or with everything (client code, MTS objects and database) on a local machine. You have to test in a configuration like the target one. Otherwise you will get surprises at an unpleasant time, namely when your customer is running your application.

In my test I got 1200% better performance when I tested a lot. OK, I’m only joking, but this could be true. Worse yet, if you do not test enough, you can be sure the application will have nasty bugs!

My main goal with this article (part one and two) was to give you some tips about how to avoid some nasty problems with MTS and how to raise performance. If you said something like “hmmm, perhaps I should try that” at least once while reading the article, then I’m satisfied.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: