...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
# Monday, June 04, 2012

Might be an oldie, but I only found this out now…

Open up IE and paste in the following address > javascript:alert(navigator.userAgent)

frameworks

frameworks_-_closeup

Sure, there is always a trip down to C:\Windows\Microsoft.NET\Framework to be certain, but I like this one too.

Monday, June 04, 2012 10:48:06 PM (AUS Eastern Standard Time, UTC+10:00)  #    - Trackback
.NET Framework
# Thursday, April 07, 2011

Hit this little hurdle recently while creating WCF Data Service against Azure Table Storage. At the moment only a handful of operators are supported by the client library when using the Table Storage Service.

Supported Query Operators

LINQ operator

Table service support

Additional information

From

Supported as defined.

 

Where

Supported as defined.

 

Take

Supported, with some restrictions.

The value specified for the Take operator must be less than or equal to 1,000. If it is greater than 1,000, the service returns status code 400 (Bad Request).

If the Take operator is not specified, a maximum of 1,000 entries will be returned.

First, FirstOrDefault

Supported.

 

What this means is that we can not perform LINQ queries that group, order by, distinct or even return single entity properties from the query (we must always return the entire entity). In most situations the solution is to construct our LINQ queries that first make use of the supported operators and then use AsEnumerable() followed by any operations that are not supported. This generates two parts to the LINQ query. The first part (everything before the AsEnumerable) gets sent to the backend (Azure Table Storage in this case) and the remaining parts execute locally against the results of the first (in-memory). This helps get over the road-block but as you can image you are bringing a greater chunk of data down to the client and continuing processing there.

Some examples:

Using Distinct()

var query = myTableServiceContext.MyEntity.Where(e => e.Category == someCatgeory).AsEnumerable().Select(c => c.Name).Distinct();
 
Select next 5 entities after a given date and time (using OrderBy together with Take)
 
var query = myTableServiceContext.MyEntity.Where(e => e.Category == someCatgeory & e.StartDate > DateTime.UtcNow).AsEnumerable().OrderBy(o => o.StartDate).Take(5);

For further details check out the online documentation.

Thursday, April 07, 2011 8:32:00 PM (AUS Eastern Standard Time, UTC+10:00)  #    - Trackback
.NET Framework | WCF | Windows Azure
# Wednesday, April 06, 2011

Just thought I might share some useful dev tools I have either found or have had recommended to me.

The first is a must if you are doing any LINQ action in your code (…and most of us are in some degree these days).
Check out LINQPad. I am blown away how useful this tool has been. Think SQL Management Studio for LINQ!

linqpadscreen

Another great tool I have been using lately is Neudesic’s Azure Storage Explorer

ase4_blobs

Essential for generating and managing Azure table storage data during development.
Plays nicely with both developer storage and Azure storage accounts.

Wednesday, April 06, 2011 2:53:00 AM (AUS Eastern Standard Time, UTC+10:00)  #    - Trackback
.NET Framework | Windows Azure
# Thursday, July 01, 2010

I am getting a few reports that after a recent windows update (or installing .NET Framework 4.0) the ESSO service fails to restart. Microsoft have released a hotfix to address this (http://support.microsoft.com/kb/2252691)

Microsoft Reports:

This issue occurs after installing .NET Framework 4.0. The registration of the assembly used by ENTSSO to access SQL Server does not specify the correct version of the .NET Framework. When .NET Framework 4.0 is installed, the assembly will try to use the newer framework and then fail to load

To resolve this manually:

32-bit Server

1.       Open a command window
2.       Go to C:\Windows\Microsoft.NET\Framework\v2.0.50727
3.       Type: regasm “C:\Program Files\Common Files\Enterprise Single Sign-On\ssosql.dll”

64-bit Server

1.       Open a command window
2.       Go to C:\Windows\Microsoft.NET\Framework64\v2.0.50727
3.       Type each of the following and hit ENTER:

32bit:  regasm “C:\Program Files\Common Files\Enterprise Single Sign-On\win32\ssosql.dll”
64bit:  regasm “C:\Program Files\Common Files\Enterprise Single Sign-On\ssosql.dll”

Note
On a 64-bit server, regasm will need to be run for both the 32-bit and 64-bit versions of ssosql.dll.

Hope this helps smile_wink

Thursday, July 01, 2010 3:05:59 PM (AUS Eastern Standard Time, UTC+10:00)  #    - Trackback
.NET Framework | BizTalk General
# Tuesday, June 29, 2010

In my last post, I was exploring a different approach to implementing a simple windows service. In that solution, I used Windows Workflow to implement a folder monitor service. Part of the solution required me to gather target folder details from configuration. I needed more than the out-of-the-box appSettings collection of key-value pairs. Normally I would use a second xml configuration file and basically deserialise that into my custom collection class. However, I was in exploration mode so I decided to give the System.Configuration classes another go.

I wanted the client to easily manage the target folders to monitor and customise the alert that gets logged in BAM (see the last post). I wanted something like this:

<folderMonitors>
<folderMonitor targetFolder="E:\Data\BizTalk\Monitor1" fileMask="*.xml" timerInterval="1" processingInterval="2">
<alert alertInterval="5" sender="BIZTALK" destination="LOB1" messageType="Order" errorType="Folder Monitor" errorCode="OFFLINE"/>
</folderMonitor>
<folderMonitor targetFolder="E:\Data\BizTalk\Monitor2" fileMask="*.xml" timerInterval="1" processingInterval="2">
<alert alertInterval="5" sender="BIZTALK" destination="LOB2" messageType="Invoice" errorType="Folder Monitor" errorCode="OFFLINE"/>
</folderMonitor>
</folderMonitors>

I was surprised by the amount of code I needed to write. Basically you have to:

  1. Define a ConfigurationSection
  2. Define the ConfigurationElementCollection and implement the indexers and ARC methods
  3. Define the actual ConfigurationElement (the folder monitor structure you see above)
  4. Declare the configuration section in the app.config

Here is the source code   

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
 
namespace Breeze.BizTalk.WorkflowLibrary
{
    public class FolderMonitorSection : ConfigurationSection
    {
        #region Static Accessors
        /// <summary>
        /// Gets the configuration section using the default element name.
        /// </summary>
        public static FolderMonitorSection GetSection()
        {
            return GetSection("folderMonitorConfig");
        }
 
        /// <summary>
        /// Gets the configuration section using the specified element name.
        /// </summary>
        public static FolderMonitorSection GetSection(string sectionName)
        {
            FolderMonitorSection section = ConfigurationManager.GetSection(sectionName) as FolderMonitorSection;
            if (section == null)
            {
                string message = string.Format("The specified configuration section (<{0}>) was not found.", sectionName);
                throw new ConfigurationErrorsException(message);
            }
            return section;
        }
        #endregion
 
        #region Configuration Properties
 
        [ConfigurationProperty("folderMonitors", IsDefaultCollection = true)]
        public FolderMonitorConfigElementCollection FolderMonitors
        {
            get { return (FolderMonitorConfigElementCollection)this["folderMonitors"]; }
            set { this["folderMonitors"] = value; }
        }
 
        public override bool IsReadOnly()
        {
            return false;
        }
        #endregion
    }
 
    [ConfigurationCollection(typeof(FolderMonitorConfigElement), CollectionType = ConfigurationElementCollectionType.BasicMap)]
    public class FolderMonitorConfigElementCollection : ConfigurationElementCollection
    {
        protected override ConfigurationElement CreateNewElement()
        {
            return new FolderMonitorConfigElement();
        }
 
        protected override string ElementName
        {
            get { return "folderMonitor"; }
        }
 
        public override ConfigurationElementCollectionType CollectionType
        {
            get { return ConfigurationElementCollectionType.BasicMap; }
        }
 
        public override bool IsReadOnly()
        {
            return false;
        }
 
        #region Indexers
 
        public FolderMonitorConfigElement this[int index]
        {
            get { return BaseGet(index) as FolderMonitorConfigElement; }
            set
            {
                if (BaseGet(index) != null)
                {
                    BaseRemoveAt(index);
                }
                BaseAdd(index, value);
            }
        }
 
        public new FolderMonitorConfigElement this[string name]
        {
            get { return BaseGet(name) as FolderMonitorConfigElement; }
        }
 
        #endregion
 
        #region Lookup Methods
 
        protected override object GetElementKey(ConfigurationElement element)
        {
            FolderMonitorConfigElement cfg = element as FolderMonitorConfigElement;
            return cfg.TargetFolder;
        }
 
        public string GetKey(int index)
        {
            return (string)BaseGetKey(index);
        }
 
        #endregion
 
        #region Add/Remove/Clear Methods
 
        public void Add(FolderMonitorConfigElement item)
        {
            BaseAdd(item);
        }
 
        public void Remove(string name)
        {
            BaseRemove(name);
        }
 
        public void Remove(FolderMonitorConfigElement item)
        {
            BaseRemove(GetElementKey(item));
        }
 
        public void RemoveAt(int index)
        {
            BaseRemoveAt(index);
        }
 
        public void Clear()
        {
            BaseClear();
        }
 
        #endregion
    }
 
    public class FolderMonitorConfigElement : ConfigurationElement
    {
        #region Constructors
 
        public FolderMonitorConfigElement()
        {
        }
 
        #endregion
 
        #region Configuration Properties
 
        [ConfigurationProperty("targetFolder", IsRequired = true)]
        public string TargetFolder
        {
            get { return (string)this["targetFolder"]; }
            set { this["targetFolder"] = value; }
        }
 
        [ConfigurationProperty("fileMask", IsRequired = true, DefaultValue = "*.*")]
        public string FileMask
        {
            get { return (string)this["fileMask"]; }
            set { this["fileMask"] = value; }
        }
 
        [ConfigurationProperty("timerInterval", IsRequired = true, DefaultValue = "3")]
        [IntegerValidator(ExcludeRange = false, MaxValue = 1440, MinValue = 1)]
        public int TimerInterval
        {
            get { return (int)this["timerInterval"]; }
            set { this["timerInterval"] = value; }
        }
 
        [ConfigurationProperty("processingInterval", IsRequired = true, DefaultValue = "5")]
        [IntegerValidator(ExcludeRange = false, MaxValue = 1440, MinValue = 1)]
        public int ProcessingInterval
        {
            get { return (int)this["processingInterval"]; }
            set { this["processingInterval"] = value; }
        }
 
        [ConfigurationProperty("alert")]
        public AlertConfigElement AlertConfig
        {
            get { return (AlertConfigElement)this["alert"]; }
            set { this["alert"] = value; }
        }
 
        public class AlertConfigElement : ConfigurationElement
        {
            public AlertConfigElement()
            {
            }
 
            [ConfigurationProperty("alertInterval", IsRequired = true, DefaultValue = "20")]
            [IntegerValidator(ExcludeRange = false, MaxValue = 1440, MinValue = 1)]
            public int AlertInterval
            {
                get { return (int)this["alertInterval"]; }
                set { this["alertInterval"] = value; }
            }
 
            [ConfigurationProperty("sender")]
            public string Sender
            {
                get { return (string)this["sender"]; }
                set { this["sender"] = value; }
            }
 
            [ConfigurationProperty("destination")]
            public string Destination
            {
                get { return (string)this["destination"]; }
                set { this["destination"] = value; }
            }
 
            [ConfigurationProperty("messageType")]
            public string MessageType
            {
                get { return (string)this["messageType"]; }
                set { this["messageType"] = value; }
            }
 
            [ConfigurationProperty("errorType")]
            public string ErrorType
            {
                get { return (string)this["errorType"]; }
                set { this["errorType"] = value; }
            }
 
            [ConfigurationProperty("errorCode")]
            public string ErrorCode
            {
                get { return (string)this["errorCode"]; }
                set { this["errorCode"] = value; }
            }
        }
 
        #endregion
 
    }
 
}

And my app.config file looks like this

<?xml version="1.0" encoding="utf-8" ?>
<
configuration>

  <
configSections>
    <!--
Custom config section declaration for Folder Monitor-->
    <
section
    
name="folderMonitorConfig"
      type="Breeze.BizTalk.WorkflowLibrary.FolderMonitorSection, Breeze.BizTalk.WorkflowLibrary, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null"
    />
  </
configSections>

  <!--
Custom config section for Folder Monitor-->
  <
folderMonitorConfig>
    <
folderMonitors>
      <
folderMonitor targetFolder="E:\Data\BizTalk\Monitor1" fileMask="*.xml" timerInterval="1" processingInterval="2">
        <
alertalertInterval="5" sender="BIZTALK" destination="LOB1" messageType="Order" errorType="Folder Monitor" errorCode="OFFLINE"/>
      </
folderMonitor>
      <
folderMonitor targetFolder="E:\Data\BizTalk\Monitor2" fileMask="*.xml" timerInterval="1" processingInterval="2">
        <
alertalertInterval="5" sender="BIZTALK" destination="LOB2" messageType="Invoice" errorType="Folder Monitor" errorCode="OFFLINE"/>
      </
folderMonitor>
    </
folderMonitors>
  </
folderMonitorConfig>
 
 
</
configuration>

Tip: If you do end up GAC’ing the assembly your configuration classes are under, don’t forget to update the type in the <configSections> to use the new PublicKeyToken value. smile_wink

Once all that was setup (Birthdays and Christmas sorted out along the way) I was happy with the end result. In my WF I simply used the static accessor to get my configuration collection and bound that to the Repeater Activity in my workflow. Because of the amount of work involved, I think it would of been much better to stick with my usual approach. That is, using a second custom configuration file and deserialise it into my custom collection class. The real test will be what I choose next time I am asked to implement this type of thing…custom config file or custom config section?

Tuesday, June 29, 2010 10:13:26 PM (AUS Eastern Standard Time, UTC+10:00)  #    - Trackback
.NET Framework | Windows Workflow
Navigation
Archive
<October 2014>
SunMonTueWedThuFriSat
2829301234
567891011
12131415161718
19202122232425
2627282930311
2345678
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)