by Thomas | Apr 29, 2010 | BizTalk
I’ve recently experienced (and largely solved) some serious performance issues with the BizTalk WCF Adapter for SQL Server (aka WCF-SQL). This post describes the problem and the solution that I discovered.
The BizTalk application in question has a fairly simple data flow:
- Receive a file containing multiple data records (i.e. an interchange) in XML format
- Use the standard XML Disassembler pipeline component to split the interchange into multiple messages
- Assign a static ESB Toolkit 2.0 itinerary to each message (still in the pipeline)
- Execute the itinerary as follows:
- Map to canonical format (itinerary step 1 – messaging)
- Execute a custom orchestration “service” to send the message to a SQL Server stored procedure (itinerary step 2 – orchestration)
- Route the message to an off-ramp
In this application I was doing things “the ESB Toolkit way” so everything was fairly dynamic. The maps were identified and executed on the fly and the stored procedure was called through a dynamic one-way port configured on the fly by an ESB resolver. If you’re not using the ESB Toolkit, keep reading – these tips still apply to you.
There’s really not much to the application. A batch of records comes in, gets split up into individual messages, and each message gets sent to a stored procedure in SQL Server using the WCF SQL adapter. Except the performance was terrible. On my (not-so-quick) machine, a batch containing 100 records was taking over one minute to process!
I ruled out stored procedure performance as a factor by simply changing it to immediately return without doing any work. Surprisingly, that barely increased the speed (a few seconds at most) even though the stored procedure call now returned instantly.
I discovered a couple of things with SQL Profiler that led to the solution.
First, we were sending an XML message to the stored procedure, so the parameter was typed as ‘xml’ (the SQL Server XML data type). However, BizTalk can’t send messages to SQL Server in that format. It always sends them as a Unicode string. SQL Server (or the .NET SQL client that underlies the adapter) was automatically inserting a CONVERT() on each call to turn the Unicode string into a variable of type ‘xml’, then executing the stored procedure. To avoid this “magic” conversion, we converted the stored proc parameter to NVARCHAR(MAX) and added a CONVERT() inside the stored proc. That moved the CONVERT() into the stored proc where SQL Server could pre-compile, optimize and cache it along with the rest of the code.
Always type your stored procedure parameter(s) as NVARCHAR(MAX) when sending an XML message to SQL Server.
Second, the major performance loss was related to the fact that this adapter is based on WCF and the fact that we were using a dynamic send port. I realized that for every call to the stored procedure, there was also a second dynamic SQL call to obtain metadata about the stored proc’s parameters. This was effectively doubling the number of calls to SQL Server, and running a relatively slow query to boot.
For those of you who have worked with WCF, hopefully you know that creating WCF proxy clients is a relatively expensive operation. It is always best to cache proxy objects or at least a ChannelFactory, or take advantage of the built-in caching added in .NET 3.0 SP1. Details on all of that are here. The important thing is that if BizTalk is not able to cache the WCF proxy objects that it uses to talk to the WCF SQL adapter, then performance is definitely going to be bad.
That’s where the dynamic port comes in. Since the port is dynamically configured on every call to SQL Server, the proxy objects are not cached. This explained a lot! On every call we were taking a hit from creating and setting up a WCF proxy object, then taking a second hit because the WCF adapter has to obtain metadata about the stored procedure before it calls it.
Avoid dynamic ports with the WCF adapters, and in particular the WCF SQL adapter, in favor of static ports with a dynamic Action.
The solution in my case was to create a static WCF-Custom port configured for the SQL adapter, leaving the Action setting blank (because we call multiple stored procedures). Instead of fully configuring the port on the fly, I now dynamically configure only the Action property. This produced a 45-50% increase in performance.
The end result of these changes was that processing 100 messages went from over 65 seconds to about 20 seconds.
The final tip is only relevant when you are using a fully dynamic send port with any of the WCF adapters on BizTalk 2009 and is described in this post. Here’s another post on how to do it with the ESB Toolkit. Performance can be modestly improved by explicitly setting the EnableTransaction and IsolationLevel context properties. In my fully dynamic scenario, this improved performance by about 25%. I am not clear how these settings interact with the SQL binding’s own useAmbientTransaction property.
When using dynamic ports with the BizTalk 2009 (only) WCF adapters, set the EnableTransaction and IsolationLevel context properties.
Our application is now performing at the speed that we expected, and hopefully these tips will give your own apps a nice speed boost too.
Like this:
Like Loading...
by Thomas | Jan 30, 2009 | BizTalk
I neglected to post about this at the time, but back in June 2008 I had an article published in .NET Developer’s Journal titled “A Walk Through the Process: Creating Services with Contract-First Design Using BizTalk Server 2006 R2 and Windows Communication Foundation.” The article is available on the .NET Developer’s Journal website and the sample code is attached to this post.
BizTalk makes a great platform for true contract-first service development because it is designed around messaging. One of the first things one usually does on a BizTalk project is to load or create XML schemas. With BizTalk Server 2006 R2’s support for Windows Communication Foundation, it’s a natural platform upon which to build services using contract-first design.
The article assumes that you have worked with BizTalk before, but otherwise it is a step-by-step walkthrough of the process to create schemas, create sample orchestrations to carry out the work behind the service interfaces and to publish the schemas as WCF services.
designserviceswithbts2006r2-wcfsample.zip
Like this:
Like Loading...
by Thomas | Mar 6, 2008 | BizTalk
BizTalk Server 2006 R2 makes it easy to expose WCF services from BizTalk’s messaging or orchestration engines via MSMQ, TCP or HTTP (or custom WCF bindings). For SOAP web services, similar to the old Web Services Publishing Wizard, the WCF Service Publishing Wizard can be used to expose a schema or orchestration as a WCF Web service hosted in IIS. The WCF service’s .svc file, written by the wizard and used by the WCF hosting support in IIS, always points to a common BizTalk WCF assembly (depending on choice of basic or WS-* binding) versus one specific to your BizTalk application.
Since the .svc file for the BizTalk WCF web service is “generic,” BizTalk needs a way to link the WCF Web service, hosted in IIS, to a port and receive location in the BizTalk messaging engine (in a different process). This mapping is based on the service’s relative URL, the part after the domain name (like ‘/MyBTSWCFWebService/MyBTSWCFWebService.svc’). If there is a mismatch in the virtual directory name or SVC file name in IIS vs. the relative URL string entered in the receive location in BizTalk, the two will not make the connection, and you will receive an error when you attempt to connect to the web service. If you try to bring up the service in Internet Explorer, you’ll see an ASP.NET error similar to “Receive location for address “/MyBTSWCFWebService/MyBTSWCFWebService.svc” not found. (The BizTalk receive location may be disabled.)”.
The very important thing to note is that the URL matching is case-sensitive!
Not knowing this may lead to a scenario that can consume most of a day in frustration (trust me on this one). Being unaware that the virtual directory and SVC filename are case-sensitive in BizTalk, let’s say that you create the virtual directory on a new BizTalk workstation with a different case than what is configured in your common port bindings XML file (assuming that you maintain one). After deploying the BizTalk application and attempting to view the service description through Internet Explorer, you find that you receive an error stating that the receive location does not exist or is disabled.
Now, if you somehow realize that you need to correct the case, you go back and delete the virtual directory, recreate it with the correct case, and try to view the service again. Unfortunately, you see the same error again! Careful inspection will reveal that throughout the error page text, some occurrences of the virtual directory name are in the correct case, and others show up in the incorrect case of your original virtual directory name.
I can tell you that no amount of restarting IIS, redeploying the BizTalk application, re-creating the virtual directory, rebooting, or even un-configuring and re-configuring BizTalk will get you past this error! It is really nasty.
As a last hope, we started a mass search of the registry and a complete text search of the entire hard disk — and that finally resulted in the answer: the ASP.NET Temporary Files cache (<windir>Microsoft.NETFrameworkv2.0.50727Temporary ASP.NET Files). Through all of the re-creating, restarting and reinstalling, the same cached files were still being used every time! Blowing away everything in the ASP.NET cache folder solved the problem. My co-worker James ran the hard disk text search and discovered this when he came back the next morning and checked out the search results.
[In case you’re curious, the specific location of the bad vdir name in the cache is Temporary ASP.NET Files<vdirname><random alphanum><random alphanum><svcfilename>.svc.<random alphanum>.compiled.]
Hopefully this will save some of you many, many hours of frustration!
Like this:
Like Loading...
by Thomas | Jan 28, 2008 | BizTalk
I’ve recently been working with an existing WCF service that does not properly export its metadata. [Note to WCF service developers: when testing your service, you need to explicitly test metadata export along with the actual service functionality. There are many ways to break it.] The Service Model Metadata Tool (a.k.a svcutil.exe) can connect to the service for metadata download (svcutil.exe /t:metadata <url>), but the result is a brief WSDL that contains no type information (no messages, no bindings, no schema at all).
The company that built the service uses the Web Services Contract First (WSCF) tool from Thinktecture to generate serializable .NET proxy classes and WSDL files from XML schemas. The service contract is defined by the XSD’s. [Note: WSCF does not directly support WCF.] Since we can’t download metadata from the service, the developers gave us a copy of their XSD’s and WSDL file (the latter generated by WSCF). Should be no problem to consume them.
In my case the service client is BizTalk Server 2006 R2, using its built-in support for WCF. BizTalk can generate a proxy (message and port types and schemas) for a service using the WCF Service Consuming Wizard. In a great example of unfriendly UI design, to reach this tool you must first have a BizTalk project open. Right-click the project and select “Add” then “Add Generated Items…”, then choose the “Consume WCF Service” option. The wizard can import directly from a running service, or it can import directly from a WSDL and associated XSD’s. The second option was perfect for our situation. Almost.
When I ran through the wizard, the wizard ended with the error “Error consuming WCF service metadata. Object reference not set to an instance of an object.” Not so good. One solution to this is quite simple, fortunately. There may be other variations in the WSDL or XSD’s that can cause the same error, but check this out first. In your WSDL file, make sure that the <xsd:schema> element has a targetNamespace attribute. It doesn’t matter what attribute value you use, just make sure it looks like <xsd:schema targetNamespace=”anything”>. The WSCF tool did not generate this attribute. Once it was in place, the wizard quickly generated the proper items in the project.
Another note: the WSCF-generated WSDL was also missing a <service> element. Without a service defined, BizTalk will generate empty binding files for the service. Be sure to include something like this in your WSDL:
<service name=”service1″>
<port name=”BasicHttpBinding_IService1″ binding=”tns:BindingName”>
<soap:address location=”http://localhost:8080/service1″ />
</port>
</service>
Make sure that the name in the binding attribute corresponds to the name attribute of your <binding> (or <wsdl:binding>) element, otherwise you’ll still end up with empty binding files.
When it’s working (most of the time), BizTalk 2006 R2 makes it really easy to both consume and publish WCF services. I’m glad that we are finally starting to move beyond the WSE and ASMX days!
Like this:
Like Loading...
