After reading about Nick Haslam’s tests using TPC-H data I thought I’d see how quick the new server I was building was (before it went into production!).
The server is an IBM x3650 M3 with eight disks installed: four small SSDs and four bigger SATA and SAS drives. Each of the disks is a RAID1 mirror of identical pairs.
|Data and log separate SSDs
|Data and log on single SAS
The initial test was fast but not as fast as Nick Haslam’s load to multiple files on a single C (presumably SATA) drive. I had expected to be faster but of course a bulk data load requires mainly write operations, and SSDs are notoriously slower on write operations. In addition these are running as a RAID1 array so each write requires two write operations, and the controller is set for write-through (ensure the data is written before complete).
As a comparison I reran the load with all the database files loading on the same SAS drive (a 10k rpm Savvio RAID 1 pair). As this is on the same controller it would provide a reasonable comparison of SSD vs SAS write performance.
So not as fast as writing to unmirrored disks but not massively slow either.
A while back I wrote about the limitations of LINQ to SQL when querying data in XML columns. It was frustrating but I hadn’t figured out an elegant solution at the time.
Then at then end of 2010 I attended a DDD8a session which re-opened my eyes to SQL CLR functions and how much better these were than SQL Scalar functions.
And today I linked the two problems in my head – the solution is right there! Write some SQLCLR functions which allow you to query an XML column at the server, rather than having to retrieve the full XML value and search it on the client.
Here is the VB.NET code I wrote as a first test to get the first element (or child element) of a given name and return the value.
'Get value of an Xml column element (including child elements)
Public Shared Function XmlGetElementValue(ByVal column As SqlXml,
ByVal elementName As SqlString) As SqlString
'Check for nulls
If column.IsNull Then Return SqlString.Null
If elementName.IsNull Then Return SqlString.Null
'Create an Xelement object from the XML
Dim e As XElement = XElement.Parse(column.Value)
'Try to find an element of given name
Dim found = e.Descendants(elementName.Value).FirstOrDefault
If found IsNot Nothing Then
Return New SqlString(found.Value)