...and everything in between RSS 2.0
# Monday, June 10, 2013

Had an interesting, yet all too common situation last week whilst looking to tune an integration solution built on BizTalk Server. The LOB system we were integrating with had used SQL Server as it’s backend as was performing poorly…real poorly. We were seeing up to 15 sec to grab a single customer from the database!

Upon looking at the DB schema I found all the tables defined something like this:

promoted_columns

With just an internal ID as the PK and a single column to store segments of xml data. What made this design “interesting” was the lack of any indexing on the DB. Capturing the queries being issued from the LOB system we had something like:

SELECT *
  FROM [dbo].[Customers]
  WHERE XmlData.exist
    ('
        declare namespace ns0="http://BizTalkService.Customer";
        /ns0:Customer[CustomerID=''F001998A-E367-4B34-B630-3A70A91CA0BD'']
    ') = 1

These were taking anywhere from 1 sec up to 15 seconds. The execution plan was confirming what I’m sure you are already thinking: table scans! Simulating this query against our Customers table above with 100K rows gave us a baseline of around 5 sec to extract a single row.

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 16 ms.

(1 row(s) affected)
Table 'Customers'. Scan count 1, logical reads 33459, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 4914 ms,  elapsed time = 4981 ms.

Instead of xml indexing, which brings substantial storage and maintenance overhead, promoted columns were looked at. We were told by the LOB vendor that the client application optimised the queries if it found indexes defined on the table. Indexed columns would be used instead of xpath queries when they existed. But how do we get indexed columns from the table structure we had?

Here is the gist of the approach:

First, Identify the common xpath expressions being used which look appropriate to optimise. In our example this might be the CustomerID lookup. Then we create a UDF to grab this value from the xml segment stored in the row. A computed or promoted column is created using the PERSISTED keyword and an index created.

CREATE FUNCTION udf_Get_CustomerID(@xmldata XML)
RETURNS nvarchar(50)
WITH SCHEMABINDING
BEGIN
    RETURN @xmldata.value
        (
            'declare namespace ns0="http://BizTalkService.Customer";
            (/ns0:Customer/CustomerID)[1]', 
            'nvarchar(50)'
        )
END
GO

ALTER TABLE Customers
ADD CustomerID AS dbo.udf_Get_CustomerID(XmlData) PERSISTED
GO


CREATE INDEX ix_CustomerID ON Customers(CustomerID);
GO

Now we can modify our query, as the LOB client did, to use the new indexed column and dramatically increase he performance of the types of queries.

SELECT *
  FROM [dbo].[Customers]
  WHERE CustomerID='F001998A-E367-4B34-B630-3A70A91CA0BD';

Giving us stats of:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 4 ms.

(1 row(s) affected)
Table 'Customers'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 1 ms.

So we went from 4981 ms to 1 ms!

Obviously we need to consider the overhead this creates for write operations as we should when considering any indexing design. Only optimise the most costly queries that get issued most frequently. Overkill can lead to poorer performance as more resources are required to maintain the indexes. During our subsequent testing we didn't measure any noticeable impact. Further testing will quantify this for us in the coming weeks.

Of course not all scenarios can rely on the client app changing its behaviour and issuing an optimised query once it see’s proper indexing. If this describes your situation you maybe interested in Selective Xml Indexes. In this article Seth Delconte writes about the new SQL Server 2012 SP1 feature that doesn't require the client to change. An option should the vendor migrate to SQL 2012 SP1 in the future:
https://www.simple-talk.com/sql/learn-sql-server/precision-indexing--basics-of-selective-xml-indexes-in-sql-server-2012/

Monday, June 10, 2013 9:38:51 PM (AUS Eastern Standard Time, UTC+10:00)  #    - Trackback
.NET Framework | BizTalk General | SQL Server
# Friday, March 29, 2013

Just noticed the following option at the end of a configuration wizard:

Get_PowerShell_Commands

How good is that!!! – clicking on this option generates me a ps1 script to perform the same configuration using the options I just selected in the wizard. Sure we have been able to write PS scripts for most install and configuration tasks but we always had to write these from scratch. I hope this pops up more and more in configuration tooling.
Golf clap to the product team High five

Friday, March 29, 2013 10:14:22 AM (AUS Eastern Daylight Time, UTC+11:00)  #    - Trackback
PowerShell | Workflow Manager
# Wednesday, March 27, 2013

Had one of those head banging issues the other day while working on a BizTalk Server 2010 integration solution. To set the scene, we had multiple development environments and we were seeing different behaviour at runtime depending on which environment built the assembly. In particular, the mapper XSLT was different and we saw different runtime behaviour depending on which environment deployed to the integration server.

The specific issue was the order of the imported namespaces and corresponding prefixes assign to them.

image

In simple scenarios this wouldn't cause much grief, these are generated during building the map and the tooling does the work for us. However it is a show stopper when you use Inline XSLT in your map and have to reference these namespace prefixes. We found that one of the development environments was outputting these in a different order. So for example, prefix ns3, referenced a different schema and the resulting xml output became invalid (…”it works on my machine” Sarcastic smile)

After much digging and mind numbing process of elimination, I found the problem to be the installation of .NET 4.5 on one of the environments

image

Removing .NET 4.5 on that environment fixed this and now we have consistent builds and unit tests once again.

Although the .NET frameworks are designed to be installed side-by-side, I guess something was changed that produced this behaviour. What that was I don't know and I didn't have the time to investigate/reproduce/etc.…. “Integration waits for no man” they say.

Wednesday, March 27, 2013 11:33:22 PM (AUS Eastern Daylight Time, UTC+11:00)  #    - Trackback
BizTalk | VS 2010
# Wednesday, December 12, 2012

Breeze is proud to announce a brand new alliance with two integration partners, offering you a global team of over 100 passionate integration experts at your fingertips. "Impack" is the combination of three award winning, Microsoft certified integration partners. It is the collaboration between Breeze (Australia), Codit (Europe) and Matricis (North America) enabling an elite group of Microsoft qualified integration specialists to share knowledge, innovation and provide exceptional service for our customers.

Read all about it on the Impack website

Wednesday, December 12, 2012 8:35:50 AM (AUS Eastern Daylight Time, UTC+11:00)  #    - Trackback
Breeze
# Friday, July 20, 2012

Great opportunity for .Net Developers to join fast growing company

 

Breeze was recently awarded Microsoft World partner of the Year for 2012 and is looking for .Net developers x2 to work with the latest cloud technologies in Sydney. Please contact me or visit us at www.breeze.net

 

 

looking for a job

Friday, July 20, 2012 12:11:33 PM (AUS Eastern Standard Time, UTC+10:00)  #    - Trackback
Breeze
# Thursday, June 28, 2012

It wasn't so long ago that fellow Breezer, Micko, and I felt the pain trying to configure firewall rules for Service Bus relay endpoints in a locked down environment. Having jumped over those hurdles we quickly ran into another when the client API performed its online CRL checks. What hurt more was the fact that we could not turn these off Steaming mad

…that was soooo last year (well Nov 2011 to be precise).

As part of the Windows Azure Service Bus June 2012 release, one of the changes addresses the CRL check issue:

The .NET Framework enables you to configure the check for X509 certificate revocation. The default behavior for the Service Bus client library is to perform this check in the Online mode. Specifying an online check can result in a long delay while the certificate authority is contacted. To modify this behavior, you can add the following to the Web.config or App.config files for the project using the Service Bus in the appsettings section:

<add key="Microsoft.ServiceBus.X509RevocationMode" value="NoCheck" />

A very welcome addition.

Thursday, June 28, 2012 10:40:30 PM (AUS Eastern Standard Time, UTC+10:00)  #    - Trackback
Cloud Services | Windows Azure
# Tuesday, June 26, 2012

Today, Breeze proudly announces the company has won the 2012 Worldwide Award for Microsoft Application Integration Partner of the Year and Finalist for Microsoft Cloud Partner of the Year Award.

Breeze was selected from a global field of top Microsoft partners, over 3000 entrants worldwide, for demonstrating excellence in innovation and implementation of customer solutions based on Microsoft technology. Breeze was recognised for providing outstanding solutions in Application Integration and Hybrid on-premise and public cloud solutions for Centrebet

Breeze WPC 2012 Winner

Well done team!!!

Tuesday, June 26, 2012 4:07:44 PM (AUS Eastern Standard Time, UTC+10:00)  #    - Trackback
Breeze

In the latest drop (v1.7) of the Windows Azure Tools for Microsoft Visual Studio a new Server Explorer plugin has been added to manage our Windows Azure Service Bus entities. Previously our options were to use the portal, namespace management API’s or 3rd party tools. One of the best tools is Service Bus Explorer that I use in various projects, webcasts and posts.

Now we have added functionality within Visual Studio Server Explorer to manage our Service Bus Queues, Topics and Subscriptions. A simple test harness is included as well.

vs service bus explorer - 1 vs service bus explorer - 2

This is a nice, but I’ll continue to use Paolo Salvatori’s excellent tool as it still has a richer feature set. Stay tuned to the Visual Studio Tool releases though, as new stuff is always being added.

Find out more here

Tuesday, June 26, 2012 2:57:09 PM (AUS Eastern Standard Time, UTC+10:00)  #    - Trackback
VS 2010 | Windows Azure
# Tuesday, June 19, 2012

I had an interesting error crop up this week that cost me a few hours I wont get back.

The Scenario

A typical BizTalk Server integration project required the incoming message to have an xml namespace added to it before processing. No worries, lets leverage the ESB Toolkit and use the Microsoft.Practices.ESB.Namespace.PipelineComponents.AddNamespace pipeline component in a custom pipeline.

ESB_Add_namespace_-_1

All was well with the solution and both unit tests and local integration tests worked a treat.

The Issue

When we ran end-to-end testing in the TEST environment we started getting the following exceptions being thrown by the ESB pipeline component:

Description: There was a failure executing the receive pipeline...blah blah blah...
Source: "ESB Add Namespace"...blah blah blah...
Reason: There was no XML start tag open 

I noticed the source system was producing the incoming message slightly differently to the messages used in testing. Namely, the source system was appending additional CR+LF to the end of the xml message.

For example:

Mock Test Message Mock Source System
ESB_Add_namespace_-_3 ESB_Add_namespace_-_2

So I'm thinking, this can’t be the issue. Surely the ESB Toolkit component is more resilient than this?…nope! Annoyed

The Solution

The solution was to set the ExtractionNodeXPath setting to the root element as follows:

ESB_Add_namespace_-_5

This gave me what I was after (same as before *but* without the xml parse error).

One other solution is to include a NamespacePrefix in the pipeline instance configuration:

ESB_Add_namespace_-_4

Note: we must use a prefix outside of [ns0-ns6] as these are reserved by the component.

Both solutions will work in the case of trailing CR+LF and no trailing CR+LF in the incoming messages.

Tuesday, June 19, 2012 9:33:26 PM (AUS Eastern Standard Time, UTC+10:00)  #    - Trackback
BizTalk | ESB
Navigation
Archive
<August 2014>
SunMonTueWedThuFriSat
272829303112
3456789
10111213141516
17181920212223
24252627282930
31123456
Blogroll
About the author/Disclaimer

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2014
Breeze
Sign In
Statistics
Total Posts: 64
This Year: 0
This Month: 0
This Week: 0
Comments: 182
Themes
Pick a theme:
All Content © 2014, Breeze
DasBlog theme 'Business' created by Christoph De Baene (delarou)