Using BizTalk to integrate to Excel spreadsheets - Part III

by eliasen 23. December 2008 20:26

Hi all

It is time for the third posting in my series about using BizTalk to integrate to Excel spreadsheets. My first two postings are here (Installation) and here (The schema generation wizard).

This third posting is a talk about the runtime, and how it works.

The setup

To start out, I have done a simple test, and you can find my project

.

It has a simple spreadsheet and a schema for this spreadsheet (both are described in my previous post) and the setup basically just has a FILE Receive Location and a send port with a filter that takes everything from the Receive Port the Receive Location belongs to. My aim is to see how fast the Spread Disassembler is.

First, a short description of my setup:

My BizTalk installation is in a Microsoft Virtual PC 2007 virtual machine.

The host machine is a Hewlett Packard 8710w laptop with an Intel Core Duo T7700 2,4GHz CPU, 2GB of RAM and Windows XP Professional Service Pack 3 and completely updated as of 7'th December 2008.

The guest system is a virtual machine which has one 2,4GHz CPU, 1GB RAM and Microsoft Windows Server 2003 R2 Enterprise Edition SP2 - also completely updated as of 7'th December 2008.

The test

I created 999 copies of the same spreadsheet and moved them into a folder watched by the receive location. They were read, transformed into XML, and output into the output folder in 3:19 minutes. This is an average of 5 spreadsheets per second.

This took me by surprise - I had expected it to be faster. So I decided to do things more academic than looking at the time stamps of the output files. After all, there are PLENTY of functionality that could be the time consumer. So I created a BAM Activity and View, tracking when my Receive Port starts and when it ends.

A table showing the average processing time can be seen here:

Number of messages in test Average processing time per message Messages per minute
5 0,0227 seconds 2643
63 0,0337 seconds 1780
127 0,0584 seconds 1027
1966 0,2714 221

So it is pretty clear, that performance drops drastically when the load increases. I do not blame this on the Spread Disassembler, though. Since this is a virtual PC, with SQL Server on the same box as BizTalk, the mere I/O operations when writing all the output files to the hard drive conflicts with the I/O operations of BizTalk using the MessageBox. I find this a much more likely issue for the drop in performance than that the disassembler should get slower just because more messages come in.

So, to sum up, it seems that the Spread Disassembler can take a pretty heavy load - Up to 2643 messages per minute (44 messages per second). This is given less than ideal operating and hardware conditions, but optimal conditions with regards to the BizTalk Server not doing anything else at the time.

Maybe in a later post I will take a look at more complex spreadsheets/schemas and also test the performance of the assembler.

--
eliasen

Tags:

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

About the author

Jan Eliasen is 37 years old, divorced and has 2 sons, Andreas (July 2004) and Emil (July 2006).

Jan has a masters degree in computer science and is currently employed at Logica Denmark as an IT architect.

Jan is a 6 times Microsoft MVP in BizTalk Server (not currently an MVP) and proud co-author of the BizTalk 2010 Unleashed book.

BizTalk Server 2010 Unleashed


Buy from Amazon

Microsoft MVP


6 times: July 2004, July 2008, July 2009, July 2010, July 2011, and July 2012. Not currently an MVP.

MCTS

Image to show

Month List

Page List