.NET Geek : Performance

My Favorite SQL Server 2k8 Feature

Kim — Fri, 23 Oct 2009 02:31:00 GMT

Since I'm typing this on my new Eee 1101HA I'm going to keep it short. (I hate these mini keyboards)

Keeping an eye on our production Sql Server box is an integral part of my daily routine. I would not categorize myself as a DBA as I don't do much administrative work related to our databases. I'm dealing mainly with issues that are related to development and making sure our system runs efficiently. One of those tasks is to make sure that our database is properly indexed and maintaining indexes as the database grows. Anyone who has done any significant work against a database knows the importance of proper indexing and that proper indexing is a balancing act.
Clustered, non clustered, covering, fill factors and page splits are just a tiny subset of the things that you have to keep on top of.

My absolute favorite feature in Sql Server 2008 is filtered indexes. In a nutshell you create an index with a filter which is used to determine what goes into the index and what not.
Let's say you have a table with some bit flags. The selectivity on these columns might be too low to be of any real use for even the smartest query analyzer and you'll often see scans when filtering on these bit flags.
In some of our tables we have bit flags where the vast majority of the records have these bit flags set to false. In these scenarios filtered indexes just shine. After creating a filtered index on only the true values of the bit flag we had queries that would take minutes to execute drop to a few milliseconds.

For more information and some important issues related to maintaining filtered indexes, you should check out the following excellent posts.
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Filtered-indexes-and-filtered-stats-might-become-seriously-out-of-date.aspx
http://blogs.technet.com/josebda/archive/2009/03/17/indexing-best-practices-for-sql-server-2008.aspx

Importing Large Xml Files to SQL Server Using SqlBulkCopy

Kim — Mon, 15 Jun 2009 21:00:00 GMT

Say you have a large Xml file that contains relatively tabular data that you want to import into SQL Server. There are several ways to go about this. Let’s look at a couple of options.

Load the file into an XDocument. Extract elements from the DOM using Linq and then use ADO.Net to insert the data into the database.
Load the data into a DataSet using ReadXml and save the data to the database
Read through the data using an Xml reader and save each record to the database

Options number 1 and 2 requires that we load the entire document into memory before processing. We will work under the assumption that the files we receive are too large to load into memory. That leaves with option number 3 which allows us to read the file in a fast forward only mode where we only hold a small portion of the file in memory. Once an item has been processed and the reader moves forward, the previously read data is unavailable to the reader. The main bottleneck now is how do we push the data in a really efficient way into the database. First of all let’s look at some sample data.

<?xml version="1.0" encoding="utf-8"?><lab_results>  <result type="A01" origin="xb102">    <name>aaa</name>    <description>sample description1</description>  </result>  <result type="A02" origin="xb103">    <name>bbb</name>    <description>sample description2</description>  </result></lab_results> 

The files we are about to process contain lab results. Each file will contain somewhere between 5-8 million result elements. Each result element will contain approximately 150 characters. 150 characters * 2 (Unicode) * 5 million = about 1.5GB per file. Obviously we can’t read that into memory all at once. Another detail is that the file contains type and origin codes which needs to be mapped to their appropriate values in the database.

Here’s the sample table structure that will have to hold the data we import.

LabResultOrigin and LabResultType are standard lookup tables. Here’s some sample data so we can conceptualize the conversions we will need to make later.

LabResultType

TypeId	Description
1	A01
2	A02
3	B01

LabResultOrigin

OriginId	Description
1	xb102
2	xb103
3	xz101

For example, when we process the Xml file we will need to convert the from the textual result type of “A01” to 1.

Importing the data

When importing using SqlBulkCopy there are two ways to feed it data. One is to use a DataTable which we discarded above because of the size of the files. Another option is to use a DataReader. The problem is that there is no class in .Net that reads xml and implements the IDataReader interface.

That means we will have to roll our own. Looking at the IDataReader interface it seems we will have to implement a boatload of methods. Or do we?
Here’s a neat little trick that was mentioned in the passing in a comment by “jezemine” in this blog post.

Create a class that implements IDataReader.
Press Alt+Shift+F10 to implement the interface members (with a throw NotImplementedException)
Pass your class to SqlBulkCopy. It will break on a NotImplementedException of course.
Go ahead and implement the method it crashed on.
Repeat steps #3 and #4 until all required methods have been implemented.

Only 3 out of more than 20 methods in IDataReader are actually used by SqlBulkCopy. Not too bad.

We will create an XmlDataReader class that implements IDataReader so that we can pass it to SqlBulkCopy. The calling code will look something like the following:

using (XmlTextReader xmlTextReader = new XmlTextReader(message.FileName))

using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.TableLock))

    SetupBulkCopy(sqlBulkCopy);

    var reader = new LabResultDataReader(xmlTextReader);

    sqlBulkCopy.WriteToServer(reader);

As you can see, there is no mention of an XmlDataReader, but rather a LabResultDataReader. I decided to split the reader into two distinct pieces. One is a general purpose XmlDataReader that can be used for other xml structures and not only the one in the sample. A special purpose LabResultDataReader is derived from XmlDataReader and this class knows how to handle the structure of the lab result xml file.

In order to tell SqlBulkCopy how to map between the data in the file and the columns in the database we need some plumbing as seen in the call to SetupBulkCopy().

private static void SetupBulkCopy(SqlBulkCopy sqlBulkCopy)

    sqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(0, "TypeId"));

    sqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(1, "OriginId"));

    sqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(2, "ResultName"));

    sqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(3, "Description"));

    sqlBulkCopy.DestinationTableName = "LabResult";

Note that we didn’t include the LabResultId column in the mapping above. The reason for this is that the LabResultId is an Identity column and is generated automatically by SQL Server.

The general purpose XmlDataReader is as follows:

public abstract class XmlDataReader : IDataReader

    private readonly string m_rowElementName;

    private readonly XmlReader m_xmlReader;

    private readonly int m_fieldCount = -1;

    private bool m_disposed;

    protected IEnumerator<XElement> m_enumerator;

    public abstract object GetValue(int i);

    /// <summary>

    /// Initialize the XmlDataStreamer. After initialization call Read() to move the reader forward.

    /// </summary>

    /// <param name="xmlReader">XmlReader used to iterate the data. Will be disposed by when done.</param>

    /// <param name="fieldCount">IDataReader FiledCount.</param>

    /// <param name="rowElementName">Name of the XML element that contains row data</param>

    public XmlDataReader(XmlReader xmlReader, int fieldCount, string rowElementName)

        m_rowElementName = rowElementName;

        m_fieldCount = fieldCount;

        m_xmlReader = xmlReader;

        m_enumerator = GetXmlStream().GetEnumerator();

    public bool Read()

        return m_enumerator.MoveNext();

    public int FieldCount

        get { return m_fieldCount; }

    public XElement CurrentElement

        get { return m_enumerator.Current; }

    /// <summary>

    /// http://msdn.microsoft.com/en-us/library/system.xml.linq.xstreamingelement.aspx

    /// </summary>

    /// <param name="m_xmlReader"></param>

    /// <returns></returns>

    private IEnumerable<XElement> GetXmlStream()

        XElement rowElement;

        using (m_xmlReader)

            m_xmlReader.MoveToContent();

            while (m_xmlReader.Read())

                if (IsRowElement())

                    rowElement = XElement.ReadFrom(m_xmlReader) as XElement;

                    if (rowElement != null)

                        yield return rowElement;

    private bool IsRowElement()

        if (m_xmlReader.NodeType != XmlNodeType.Element)

            return false;

        return m_xmlReader.Name == m_rowElementName;

    protected virtual void Dispose()

        if (m_disposed)

            return;

        m_enumerator.Dispose();

        m_disposed = true;

    #region Members not required by SqlBulkCopy

    #region IDataReader Members

    public bool NextResult()

        throw new NotImplementedException();

    public int RecordsAffected

        get { throw new NotImplementedException(); }

    public string GetDataTypeName(int i)

        throw new NotImplementedException();

    // Deleted tons of methods not required...

Now let’s have a look at the special purpose LabResult reader.

public class LabResultDataReader : XmlDataReader

    private const string XmlTagRow = "result";

    private const int FieldCount = 4;

    private const int InvalidItemId = -1;

    public LabResultDataReader(XmlReader xmlReader)

        : base(xmlReader, FieldCount, XmlTagRow){ }

    public override object GetValue(int i)

        switch (i)

            case 0:

                return CurrentElement.Attribute("type").EnumFromValue<ResultType>();

            case 1:

                return CurrentElement.Attribute("origin").EnumFromValue<Origin>();

            case 2:

                return CurrentElement.Element("name").value;

            case 3:

                return CurrentElement.Element("description").value;

            default:

                throw new InvalidOperationException("Column count mismatch.");

There are two main features of the XmlDataReader. The first is that it flattens the Xml hierarchical structure to resemble a row. For each iteration of the enumerator, it extracts the <result> element and all its children. The special purpose reader (in our example the LabResultsDataReader) maps the nested <result> tag to a set of column values. The second significant feature is the streaming nature of the reader. We never hold more than a single <result> element in memory at any given time.

If you look at the code above and wondered what are those .EnumFromValue<> methods on the XmlAttributes, you were right. These are just Extension methods that I added for convenience.

public static T EnumFromValue<T>(this XAttribute attribute)

    string value = attribute.Value;

    if (string.IsNullOrEmpty(value))

        return default(T);

try

        T converted = (T)Enum.Parse(typeof(T), value, true);

        return converted;

    catch (Exception)

        return default(T);

As you can see, despite that this turned into a not so short post, there’s not that much code required for this to work. Hopefully this will give you enough to assist you in building your own high performance ETL solution.

A few drawbacks of the proposed solution. I have only had to process a very limited set of files. In the case where many files of different schema would have to be parsed and processed, the design of having a designated derived class for each type of file could become a pain point. There is no support for parsing files with a schema that is not known at compile time. This implies that if the schema changes or a new type of file needs to be imported, a new build is needed.

Suggestions, comments, improvements etc. are welcome.

Hard Drive Speed and Visual Studio Performance

Kim — Fri, 02 Nov 2007 02:22:00 GMT

Scott Guthrie has just posted a post on how to increase the performance of Visual Studio. (The title of this post is actually his) He has posted several blog posts on how to improve the performance of Visual Studio here and here. .NET tip of the day also has a couple of posts on VS performance here and here. In his latest post he points out the importance of the speed of the hard drive.

Despite spending most of my time in Visual Studio I've had my fair share optimizing databases. When monitoring databases for perf problems you try to find which if any of the subsystems cause bottlenecks. Is the server CPU bound, disk bound etc.

Turns out some skills transfer well to other environments. :-) I am doing my development on a notebook. (A hefty one. One that can compete if not outperform most desktops) Despite having a powerful machine it seemed to disk bound. eg. the hard drive subsystem is the main bottleneck and the other sub systems are under utilized waiting for IO completion. I was already running with a 100GB 7200 rpm Hitachi 2.5" drive and I wasn't sure what I could do. I won't upgrade this machine until late 2008 which is when I estimate the next substantial hardware upgrade will be available. After a short monitoring session my suspicion was confirmed. I'm wasting time waiting for IO. Since this is a notebook I can't set up a RAID configuration or throw in a 10K rpm drive.

A few months ago Hitachi released a new 200GB 7200 rpm disk. So what would that buy you you might ask. A lot! Hard drive performance isn't only dependent on the platter rotation speed, but also on the density of the data on the platters. The denser (can you say that?) the data the more data is read/written per rotation.

I ran out and got the 200GB version and I can just say that the performance increase is substantial. Hitachi claims close to 20% performance increase, but I haven't done any benchmarks. My gut feeling is that it's less, but that might be because of other factors as well.

Share this post :

Visual Studio "View Code" soo slow...

Kim — Fri, 08 Jun 2007 04:00:00 GMT

I don't know exactly why, but for some time, the first time I display a code window within Visual Studio there's intense disk activity for about 10 seconds. From that point on opening other source files is a snap.
It's late so I'm going to make this a short one.

Locating the problem:
1) I fired up SysInternals fileMon.
2) set it to monitor only the C: drive
3) turned off some disk intensive applications (Folding@Home, MSN Desktop search...)
4) Created a new project in Visual Studio
Stared at the fileMon log for about 30 seconds while Visual Studio was writing to the file:
C:\Documents and Settings\kim\Local Settings\Application Data\Microsoft\VisualStudio\8.0\1033\ExpansionsXML.xml
5) Googled for ExpansionsXML.xml

Turns out this is Visual Studio's cache for snippets. The size was about 800KB. I deleted the file and created a new project. Visual Studio created a new file, but this time the size was 210KB. It still took close to 10 seconds to display the first source file. I deleted all the snippet templates from:
C:\Program Files\Microsoft Visual Studio 8\VC#\Snippets\1033\*.*
and
C:\Program Files\Microsoft Visual Studio 8\VB\Snippets\1033\*.*

Now opening a source file was instantaneous! Not that I use snippets a lot (I'm using CodeRush), but I still wanted the snippets to stay around so I restored the deleted snippets.

At this point I took a step back since something was puzzling me. Why does Visual Studio need to recreate the snippet cache every time I open Visual Studio? I only have a small amount of custom snippets and I almost never update my snippets.
How can I tell Visual Studio to stop caching the snippets if the cache is present?

I made the ExpansionsXML.xml read-only and that was sign enough for Visual Studio to stay away from the file!!!
I'm only smacking my head since this has been bugging me for a few days...