[Source: http://geekswithblogs.net/EltonStoneman]
Following on from the sample for processing Excel uploads with nServiceBus, I have a comparable version using BizTalk on MSDN Code Gallery here: BizTalk and nServiceBus Excel Upload. The BizTalk (2006 R2) sample uses a FILE receive port with a simple pipeline component to disassemble the Excel file into separate messages. A SQL send port subscribes to the messages, and with an outbound map calls the AdventureWorks stored procedure to insert each product.
Processing Excel in BizTalk is nothing new, but I wanted to do a comparison against the nServiceBus example, and also see how BizTalk and nServiceBus could be integrated. My original thinking was that the trigger in the nServiceBus solution relies on a FileSystemWatcher, which is less reliable and less flexible than BizTalk’s FILE adapter. A hybrid solution could use the FILE adapter to receive and parse the upload, then send each row as an AddProduct message to MSMQ, which the nServiceBus handler subscribes to.
Running this on the same environment (a Windows Server 2003 VM running under VirtualBox in Ubuntu 9.04), the BizTalk solution processes the 3,500 row Excel file in 3 mins 10 seconds (compared to 4m 15s for the distributed NSB running with 5 threads), and the 12,000 row file in 11m 14s (compared to 14m 0s). I was surprised to find the BizTalk solution running more quickly, as the original NSB was using non-recoverable messaging, so all messages were in memory, while BizTalk had the latency of writing to the message box.
Performance Comparison
This is not an intended to be a thorough benchmark of NSB and BizTalk – both sample projects are basic, untuned implementations, and the tests are on a single box rather than two or three. But for comparison, I ran the 3,500 row upload repeatedly under different configurations:
1. NSB with distributor – host and distributor using 20 threads
2. NSB with distributor – host and distributor using 20 threads, recoverable messaging
3. NSB without distributor – host using 20 threads
4. NSB without distributor – host using 20 threads, recoverable messaging
5. BizTalk FILE receive and SQL send
6. NSB parsing Excel file, BizTalk subscribing to AddProduct messages – MSMQ receive using batches of 100
7. BizTalk parsing Excel file, NSB without distributor subscribing to AddProduct messages – host using 20 threads
Which gave these averaged results:
Boosting the number of threads dramatically improved the NSB performance, and removing the distributor halved the duration. I would expect that running the distributor on a separate node would yield similarly good results on each processing node. The hybrid BizTalk/NSB configurations were the slowest – to be expected, as you have the latency of the message box and the latency of MSMQ saving to disk. NSB configurations without recoverable messaging were only marginally slower than the recoverable version, which seems incorrect based on Udi Dahan’s performance benchmark, so I’ll need to look into that further.
There’s plenty of scope for improving performance in both solutions. BizTalk can be endlessly tuned (the 2004 guidelines still apply as a good starting point). By modifying the NSB solution to send the AddProduct messages in one opration and using 80 threads for the handler, duration fell to 25 seconds – 140 messages per second.
Integrating BizTalk and nServiceBus
Despite having the worst performance, integrating BizTalk and nServiceBus is a viable option which may be very useful in some cases. It would allow you to leverage BizTalk’s adapter suite and mapping functionality to join LOB systems into an nServiceBus estate. Integration is actually very straightforward. By default NSB uses MSMQ, so to publish messages from BizTalk to an NSB subscriber just means configuring an MSMQ send port with the expected queue, and mapping your message to NSB format – which is an envelope containing one or messages:
<?xml version="1.0" ?>
<Messages xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://tempuri.net/ExcelUpload.Messages">
<AddProduct>
<BatchId>7a80fed4-9d30-44fe-95b0-08218c5f328e</BatchId>
<RegistrationIndex>58</RegistrationIndex>
<RegistrationsInBatch>254</RegistrationsInBatch>
<BatchSourcePath>E:\ExcelUpload\1.0.0.0\Binaries\Drops\ProductUpload.xls</BatchSourcePath>
<OriginatorDestination>ExcelUpload.Client.InputQueue@WIN2003R2-VM</OriginatorDestination>
<Name>new product 58</Name>
<ProductNumber>xlu-np-58</ProductNumber>
<SafetyStockLevel>100</SafetyStockLevel>
<ReorderPoint>20</ReorderPoint>
<StandardCost>10</StandardCost>
<ListPrice>15.5</ListPrice>
<DaysToManufacture>60</DaysToManufacture>
<SellStartDate>2005-02-27T00:00:00.0000000</SellStartDate>
</AddProduct>
</Messages>
The MSMQ adapter lets you specify Recoverable and Transactional flags, so your BizTalk-generated messages have the same durability options. When an NSB handler is listening at the queue, it processes the BizTalk messages in the same way as NSB messages.
Publishing NSB messages to BizTalk is trickier, as the Send and Publish methods from NSB check to see if there are any subscribers before they write to the queue (compare this to the Notify method in RhinoServiceBus). If a BizTalk MSMQ receive port is the only handler, no subscribers will be registered with NSB and the message won’t be published. So you need to either call Send with a named queue (Bus.Send<AddProduct>("ExcelUpload.AddProductService.1.InputQueue", m =>...), or send a subscription message from BizTalk to register a subscriber. In the the second option you’d need to specify the message type in the subscription:
<?xml version="1.0" ?>
<string>ExcelUpload.Messages.StartBatchUpload, ExcelUpload.Messages, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null</string>
Running the Sample
(If you haven’t seen the original NSB sample, have a read as it details the pre-requisites).
To run the BizTalk solution, download ExcelUpload.BizTalk.Binaries.zip, unzip it and:
· Copy ExcelUpload.PipelineComponents.dll to your pipeline components directory – e.g. C:\Program Files\Microsoft BizTalk Server 2006\Pipeline Components
· If you haven’t done so from the previous sample, run uspInsertProduct.CREATE.sql
· Run uspInsertProduct.CREATESYNONYM.sql
· Import the MSI into BizTalk
· Run the MSI
· Start send port SendInsertProduct.SQL (you’ll need to change the connection details)
· Enable receive location ReceiveStartUpload.FILE.XLS (monitors c:\drops\ExcelUpload)
· Drop an Excel file
As before, you’ll need to clear down the database between runs if you drop the same file repeatedly.
If you want to run the hybrid solutions, you should use the binaries in the new download rather than the original, as these run without a distributor. Run start.cmd and there are three windows – Client (the file watcher), Host (the StartBatchUpload handler) and AddProductService (this is the AddProduct handler).
To run configuration 6 – NSB parsing the file and BizTalk handling the AddProduct messages:
· Run start.cmd
· Kill the AddProductService console
· Disable receive location ReceiveStartUpload.FILE.XLS
· Enable receive location ReceiveAddProduct.MSMQ
· Unenlist send port SendAddProduct.MSMQ
· Start send port SendInsertProduct.SQL
· Drop an Excel file
To run configuration 7 – BizTalk parsing the file and NSB handling the AddProduct messages:
· Run start.cmd
· Kill the Host and Client consoles
· Enable receive location ReceiveStartUpload.FILE.XLS
· Disable receive location ReceiveAddProduct.MSMQ
· Start send port SendAddProduct.MSMQ
· Unenlist send port SendInsertProduct.SQL
· Drop an Excel file
Source Code
The source and referenced assemblies are in ExcelUpload.BizTalk.Source.zip. There is a VS 2008 solution for the nServiceBus components, and a VS 2005 solution for the BizTalk components.