How the transaction property for WF tracking can make a huge performance difference
The past two days I have been working on a performance problem we were having with a workflow implementation. The workflow was quite simple, A WCF call coming in, then registering the request data in a SQL database and return a ticket that the request was received. The ticket can be used by people to get information later on the status of their request. (A pretty common SOA pattern)
The workflow used a TransactionScope activity to guard the persistence of the workflow with the registration call of the custom activity in the database. The workflow runtime had the SqlPersistence service loaded and we use the SqlTrackingService to enable administrators of the system to monitor and see workflow progress of certain requests.
The time to register a request appeared to be around the 18 seconds per request when we have 20 concurrent users?!? We expected this to be around 1 second Max, depending on the server load.
After some digging around We detected that if we remove the tracking service the response times where back to what we expected, somewhere around the 0.6 seconds. So how can this happen?
The answer to this is one very important property that can be set for the tracking service configuration called Transactional. This property was set to false, by one of the developers. This simple setting added around 17 of overhead in SQL communication(with the load we already had on SQL, so can be much less in other specific cases). The property is used to determine if each tracking record that needs to be saved to SQL is done one call at the time or if all work is queued and persisted in one work batch. As you can imagine this will reduce the number of roundtrips to SQL significantly and when the workflow and database server are as busy as with the work we do, times can add up pretty fast.
So when you see a slow performance in workflow and you have tracking enabled, Look at the transactional property and check if it is not set to false (default is true, so someone would explicitly change this)
Cheers,
Marcel
Follow my new blog on http://fluentbytes.com