AdaTheDev

.NET Project File Analyser

2013-03-21T09:53:00.001Z

I've started knocking together a little app to automate the process of trawling through a folder structure and checking .NET project files (C# .csproj currently) to extract some info out of them. The DotNetProjectFileAnalyser repo is up on GitHub. I've been working against Visual Studio 2010 project files, but could well work for other versions assuming the project file structure is the same for the elements it currently looks at - I just haven't tried as yet.
Currently, it will generate an output file detailing for each .csproj file it finds:

Build output directory (relative and absolute) for the configuration/platform specified (e.g. Debug AnyCpu). Useful if you want find which projects you need to change to build to a central/common build directory.
List of all Project Reference dependencies (as opposed to assembly references). Useful if you want to find the projects that have Project References so you can switch them to assembly references

Usage

DotNetProjectFileAnalyser.exe {RootDirectory} {Configuration} {Path}

{RootDirectory} = start directory to trawl for .csproj files (including subdirectories)
{Configuration} = as defined in VS, e.g. Debug, Release
{Platform} = as defined in VS, e.g. AnyCpu

Example:

DotNetProjectFileAnalyser.exe "C:\src\" "Debug" "AnyCpu"

More stuff will go in over time, with ability to automatically update csproj files as well to save a lot of manual effort.

GB Post Code Importer Conversion Accuracy Fix

2013-03-13T23:09:00.001Z

In a post last year (Ordnance Survey Data Importer Coordinate Conversion Accuracy) I looked into an accuracy issue with the conversion process within the GeoCoordConversion DLL that I use in this project (blog post). Bottom line, was that it was a minor with an average inaccuracy of around 2.5 metres and a max of ~130 metres by my reckoning. I've since had a few requests asking if I can supply an updated GeoCoordConversion DLL with fixes to the calculations.

After getting in contact with the owner of the GeoCoordConversion project, they've kindly added me as a committer. I've now pushed the fixes up to it, rebuilt the DLL (now v1.0.1.0) and pushed up the latest DLL to the Ordnance Survey Importer project on GitHub.

SQL Server Table Designer Bug With Filtered Unique Index

2013-03-08T13:11:00.001Z

A colleague was getting a duplicate key error when trying to add a new column to a table via the Table Designer in SQL Server Management Studio (2008R2 - not tested on other versions), despite there being no violating data in the table. After a bit of digging around, I tracked the problem down to what appears to be a bug in Table Designer when there is a unique, filtered index in place on the table and the table is being recreated (i.e. you're adding a new column, but not at the end after all the existing columns).

Steps to reproduce

In SSMS in Tools -> Options -> Designers -> Table and Database Designers, uncheck the "Prevent saving changes that require table re-creation" option

Create table:

CREATE TABLE [dbo].[Test]
(
Column1 INTEGER NOT NULL,
Column2 INTEGER NOT NULL,
Column3 INTEGER NULL
);

Create dummy data:

INSERT dbo.Test(Column1, Column2, Column3) VALUES (1, 1, 1);
INSERT dbo.Test(Column1, Column2, Column3) VALUES (1, 1, NULL); -- OK as Column3 is NULL

Now run the following, duplicate key error is correctly thrown:
```
-- Errors, Duplicate Key exception as expected
INSERT dbo.Test(Column1, Column2, Column3) VALUES (1, 1, 2); 
```
So at this point we have 2 rows in the table, no violations of the unique filtered index.
Right click the table in SSMS -> Design
Insert a new column "Column4" before Column3 and the press Save.

The error that occurs is:

'Test' table
- Unable to create index 'IX_Test_Column1_Column2'.  
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Test' and the index name 'IX_Test_Column1_Column2'. The duplicate key value is (1, 1).
The statement has been terminated.

So what it appears to be doing, is losing the WHERE filter on the index. This can be confirmed by clicking "Generate change script" in the Table Designer instead of Save - at the end of the generated script:

CREATE UNIQUE NONCLUSTERED INDEX IX_Test_Column1_Column2 ON dbo.Test
 (
 Column1,
 Column2
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Now, if there was no data in the table, or there were no rows with the same Column1 and Column2 value combination when you go into Table Designer, then you can save the table change and be blissfully unaware that the filter has been lost from the index. i.e. repeat the repro steps again, but this time move step 3 and 4 (insert dummy data) to the end of the process. The previously OK 2nd data row will now error upon insert.

Personally, I almost never use the Table Designer and as a safeguard, will be recommending to the rest of the team that the "Prevent saving changes that require table re-creation" option is checked as a basic guard.

Update: The following Connect items relating to this issue:
The filter expression of a filtered index is lost when a table is modified by the table designer
2008 RTM, SSMS/Engine: Table designer doesn't script WHERE clause in filtered indexes
Referring to comments in that 2nd item, my "Script for server version" setting was set to "SQL Server 2008 R2".

Sounds like this may have been addressed in SQL 2012, but still a problem in 2008/2008R2.

MongoDB ASP.NET Session Store Provider v1.1.0

2013-03-03T22:05:00.000Z

Since I created the MongoDB ASP.NET Session State Store Provider (v1.0.0), a few things have moved on in the MongoDB C# Driver. I've pushed a number of changes up to the project on GitHub (which I've incremented to v1.1.0), so it now uses v1.7.0.4714 of the driver. There is no change to way it is configured in web.config, so if you are using v1.0.0 of my provider it should be painless. Of course, I'd recommend thorough testing first :)

The changes relate to:

SafeMode is obsolete. I've replaced with WriteConcern.
C# Driver Jira case
WriteConcern C# Driver reference
MongoServer.Create is obsolete. I've replaced with MongoClient.GetServer.
MongoClient C# Driver reference
QueryComplete is obsolete.

web.config recap
These web.config settings have not changed, so should continue working as before.

<configuration>
  <connectionStrings>
    <add name="MongoSessionServices" connectionString="mongodb://localhost" />
  </connectionStrings>
  <system.web>
    <sessionState
        mode="Custom"
        customProvider="MongoSessionStateProvider">
      <providers>
        <add name="MongoSessionStateProvider"
             type="MongoSessionStateStore.MongoSessionStateStore"
             connectionStringName="MongoSessionServices"
             writeExceptionsToEventLog="false"
             fsync="false"
             replicasToWrite="0" />
      </providers>
    </sessionState>
  </system.web>
</configuration>

replicasToWrite is interpreted as follows: < 0 = ignored. Treated as 0.
0 = will wait for acknowledgement from primary node.
> 0 = will wait for writes to be acknowledged from (1 + {replicasToWrite}) nodes.

Please Note, per the MongoDB Driver docs, if (1 + {replicasToWrite}) equates to a number greater than the number of replica set members that hold data, then MongoDB waits for the non-existent members to become available (so blocks indefinitely).

It still treats write concerns as important, ensuring it waits for acknowledgement back from at least one MongoDB node.

As always, all feedback welcome.

Importing twitter archive into ElasticSearch

2013-02-10T19:53:00.000Z

I recently downloaded my full history of tweets from Twitter (via the "Request your archive" button on the Settings page), which gives the data in CSV and JSON formats. The download comes with a simple web UI so you can browse locally, all your historical tweets, as well as perform basic searches. When I tried performing a wildcard search, it resulted in an unresponsive script warning and nobbled the browser tab.

Cue ElasticSearch

I've kept meaning to have another hack around with ElasticSearch for a while now, as I started looking into it in 2011 and was very impressed with it. So I decided to knock up a little .NET app to take the .js files provided by Twitter in the export, extract the tweet data and push into ElasticSearch. It seems like a good little starter project to learn more about ElasticSearch and actually could give me some useful insights into all the tweets I've made. I'm sure I'm going to find some really good links I tweeted and then couldn't find again!

At the moment there's a very basic GUI (and I mean basic!) exposing the functionality to import the tweet data into ElasticSearch. I plan to stick a basic search GUI together too and then...well, I'm not quite sure yet, but I'm sure as time goes on I'll see more ways to extract interesting information from my tweet history.

The ElasticTweets project as I've called it, can be found on GitHub here - feel free to use and abuse it, and let me know how you get on!

Configuring Azure Storage Emulator SQL Server Instance

2012-05-28T10:20:00.002+01:00

If you're using Windows Azure Storage, you are almost certainly going to be running the storage emulator during development, instead of working directly against your storage account up in the cloud. This emulator (which comes in the Windows Azure SDK - see the "other" category), allows you to test locally against local instances of Blob, Queue and Table services.

As per the Overview of Running a Windows Azure Application with Storage Emulator reference, the emulator needs a local SQL Server instance. By default, it's configured to run against a SQL Server Express 2005 or 2008 database.

If you want to point it at a different instance of SQL Server, for example a shared development database server, you can do this using the DSInit command line tool. I originally came across this MSDN on How to Configure SQL Server for the Storage Emulator, which led me to try the following in the Windows Azure Command Prompt:

DSInit /sqlInstance:MyDevServerName\MyInstanceName

This tried to create the storage database, but failed with the following:

Creating database DevelopmentStorageDb20110816...
Cannot create database 'DevelopmentStorageDb20110816' : A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

One or more initialization actions have failed. Resolve these errors before attempting to run the storage emulator again. These errors can occur if SQL Server was installed by someone other than the current user. Please refer to http://go.microsoft.com/fwlink/?LinkID=205140 for more details.

The correct way when trying to use a different database server instead of the local machine, is to use the SERVER switch instead:

DSInit /SERVER:MyDevServerName\MyInstanceName

See the full DSInit Command-Line Tool reference.

When you then run the Storage Emulator, it will target that database server/instance. You can easily cleardown/reset that database by right clicking the Windows Azure Emulator icon in the taskbar, select "Show Storage Emulator UI" and the click "Reset". NB. Just to be clear, this will delete everything in your local storage emulator database.

An added "gotcha" to watch out for, if you have the storage account connection stored in a web/app.config and want to specify to use the local emulated instance, you need to use

UseDevelopmentStorage=true

exactly as it appears here. If like me, you initially give an ending semi-colon, you will get a FormatException stating with the error: "Invalid account string".

Error Creating Azure Blob Storage Container

2012-05-24T09:19:00.001+01:00

I received a rather...vague...error when trying out a bit of .NET code to connect to a Windows Azure Blob Storage account, and create a new container in which to store some blobs.

The code

CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
    "DefaultEndpointsProtocol=https;AccountName=REMOVED;AccountKey=REMOVED");

CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
            
CloudBlobContainer container = 
    blobClient.GetContainerReference("TestContainer1");

container.CreateIfNotExist(); // This error'd

The error

A StorageClientException was thrown saying "One of the request inputs is out of range.". An inner WebException showed that "The remote server returned an error: (400) Bad Request."

The cause

I'd assumed (incorrectly) that a container name could be pretty much any string. But that's not the case. As per this MSDN reference, a container name must:

be in lowercase (this was the cause in my case)
start with a letter or a number and only contain letters, numbers and hyphens (multiple consecutive hyphens are not allowed)
be between 3 and 63 characters long

The "Naming and Referencing Containers, Blobs and Metadata" reference is worth a bookmark.

ASP.NET MVC Performance Profiling

2012-05-23T09:33:00.000+01:00

Building up a profile of how a web application functions, all the database interactions that take place and where the server-side time is spent during a request can be a challenging task when you are new to an existing codebase. If you're trying to address the generic/non-specific "we need to improve performance / it's slow" issue, you need to get a good picture of what is going on and where to prioritise effort.

There are a number of ways to identify specific problems depending on the technologies. For example, if your application is backed by SQL Server, you can query a set of DMVs to identify the top n worst performing queries and then focus your effort on tuning those. Identifying a badly performing query and tuning it can obviously yield huge benefits in terms of the end user's experience. But this doesn't necessarily flag up all the problems. If you are looking at a particular page/view within the application, then you could start a SQL Profiler trace to monitor what's going on during the lifecycle of the request - this is another common and valuable tool to use. Personally, I usually have SQL Profiler open most of the time during development. If you're developing against a shared dev database with others, you can filter out other people's events from the trace - injecting your machine name into the connection string as the ApplicationName, and then filtering on this is one of a number of ways to achieve this which works nicely.

MiniProfiler For The Win

Recently, I've started using another extremely valuable tool within an ASP.NET MVC solution - MiniProfiler which is (quote):

A simple but effective mini-profiler for ASP.NET MVC and ASP.NET

It was developed by the team over at StackOverflow. Simply put, it can render performance statistics on the page you are viewing that detail where the time was spent server-side, fulfilling that request. But the key thing for me, is it provides an ADO.NET profiler. Say you're using LINQ-to-SQL - by wrapping the SqlConnection in a ProfiledDbConnection before then passing it to the constructor of a DataContext, info on the SQL queries executed within the lifetime of a request are then also included in the statistics displayed. (It can also profile calls via raw ADO.NET / Entity Framework etc, minimal effort required).

Make the invisible, visible

Since integrating this into an MVC application, the benefits have been priceless. The key thing for me is: VISIBILITY. It provides extremely value visibility of what is happening under the covers. Going back to the start of this post, if you're new to a codebase, then having this information provided to you as you browse is invaluable. It enables you to identify problems at a glance, and increases visibility of problems to other developers so the "life expectancy" of those problems is lower - they're a lot less likely to hover undetected just under the radar if the information is being pushed right in front of the developer on screen. It also helps you build up a picture of how things hang together.

MiniProfiler includes functionality to flag up N+1 and duplicate queries, a common potential problem you could encounter with ORMs if you're not careful. If a view were performing 100 low hitting queries, these may not show themselves as queries to be tuned. But the fact that 100 database roundtrips are being made, could scream out that perhaps they could be replaced with a single roundtrip and a performance improvement gained there.

I'm now a big fan of MiniProfiler, especially due to it's simplicity to integrate into a codebase. Working on ASP.NET MVC/ASP.NET applications? You might want to give it a try!

Trust your developer instinct and experience

2012-04-13T11:59:00.000+01:00

That feature you're implementing, that bug you're fixing, that performance improvement you're trying to make...it's using up a fair few brain CPU cycles trying to analyse how best to implement it. Scalability, performance, ease of maintenance, clean code...just some of the things you are mentally scoring your potential approaches against to determine which route to take. And that's without any mention of business domain logic. We've all been in the situation where you start heading down one path only to find a hurdle. It's OK though, you're an athlete (very debatable in my case) - you can jump over that hurdle. So you do. But then you find a 7 foot high wall with barbed wire on top. No problem - you have a ladder and wire cutters; it's a bit unwieldy carrying them around with you everywhere, but hey - over you go. All good. Until you come across Chuck Norris, who is angry about that "Yo' mamma.." joke you made about his mother. That wall you climbed...that was protecting his private property. Your ladder? Oh, that's still on the outside of the wall - you couldn't pull it over with you so you jumped down without it. Brace for pain.

Those voices in your head

There's always unexpected problems and hurdles that crop up. It's all part of being a developer after all. But maybe at the point you got your ladder and wire cutters out to climb over that wall, you heard a little voice inside say "Hmm, this doesn't quite feel right" or perhaps you just felt something niggling away with you about the approach. That voice, your instinct and experience as a developer built up over your career, is worth listening to. When it pipes up, sit back and take 5 to just take a bird's eye view.

Trust that instinct and experience - it's usually right. At the very least, it's highlighting the need to at least re-justify to yourself that you are heading down the right path.

Ordnance Survey Data Importer Coordinate Conversion Accuracy

2012-03-14T22:11:00.000Z

Update 13 March 2013: Please see latest blog post (fix) on this here.

Thanks to a comment on my original post around my project that imports Ordnance Survey CodePoint data into SQL Server, I was made aware of a potential issue with the (awesome) third party GeoCoordConversion DLL I use to convert the Eastings/Northings coordinates supplied in the Ordnance Survey data files, into Latitude/Longitude coordinates. The issue relates to an inaccuracy in the conversion process, specifically to do with integer divisions instead of double.

So, this blog post is to document my analysis of that and try to quantify what, if any, impact there is on the accuracy of the conversion of Eastings/Northings to Latitude/Longitude. So what I've done is:

Import the Ordnance Survey data set using the released version (1.0.0.0) of the GeoCoordConversion library (i.e. the version my project uses)
Downloaded the source code for GeoCoordConversion and corrected the parts of the calculations where the raised issue noted an inaccuracy
Reran the data import into a separate table using the "corrected" conversion code
Ran the following query to try and quantify how much of a difference it actually makes:

;WITH CTEDiff AS 
(
    SELECT orig.OutwardCode, orig.InwardCode,
        orig.GeoLocation.STDistance(new.GeoLocation) Dist
    FROM PostCodeData orig
 JOIN PostCodeDataWithFix new ON orig.InwardCode = new.InwardCode 
            AND orig.OutwardCode = new.OutwardCode
)
SELECT MIN(Dist) AS MinDifferenceInMetres, 
    MAX(Dist) AS MaxDifferenceInMetres, 
    AVG(Dist) AS AverageDifferenceInMetres, 
    SUM(CASE WHEN Dist = 0 THEN 0 ELSE 1 END) AS NumberOfDifferences
FROM CTEDiff

Results

The MAXIMUM distance between the 2 conversions for a given postcode is: 124.9 metres.
The MINIMUM distance between the 2 conversions for a given postcode is: 0 metres.
The AVERAGE distance between the 2 conversions for a given postcode is: 2.5 metres.
The TOTAL NUMBER of differences was 1,652,458 (out of 1,705,177 conversions).

This map shows the case that had the biggest difference (postcode: NR34 8JW). Plotted points are as follows:
(A) = position from conversion process before fix
(B) = position from conversion process with fix
(C) = position Google Maps gives for location
Note (B) and (C) are in the same spot, so that's why you can't see both!

View Larger Map

Summary

For me, and what I see as potential uses for this data, I personally don't see this as much of an issue especially with an average difference of 2.5 metres. I will add a comment on to the GeoCoordConversion project issue page, referencing this post. Though there doesn't look to be any activity on the project, so whether a fix can be applied is yet to be seen. That's my preferred route at present, to see if the author of that project is around to apply a fix as I currently don't have full knowledge of what I changes I can make to the source code and what would be acceptable in order to distribute the amended source code as part of my project (my project is released under a different, but compatible license...but things might get murkier when it comes to amending/distributing those amendments). In a nutshell, I just don't have time to try to make head and tail of what I can legitimately do (and I don't want to change the license for my project)!

All feedback welcomed

Quick win - check your table variable use

2012-03-05T09:30:00.000Z

Quick wins are awesome. Making a change that takes minimal effort and yields a significant performance improvement is very satisfying.

This particular potential quick win relates to the use of table variables vs. temporary tables. Have a non-trivial stored procedure that produces some intermediary results and stores in a table variable which then goes on to be used further in the stored procedure? Consider evaluating a switch to a temporary table instead. This very quick change, can give a great performance boost for such little effort due to the points outlined on MSDN (quote):

table variables are not supported in the SQL Server optimizer's cost-based reasoning model. Therefore, they should not be used when cost-based choices are required to achieve an efficient query plan. Temporary tables are preferred when cost-based choices are required. This typically includes queries with joins, parallelism decisions, and index selection choices.

Queries that modify table variables do not generate parallel query execution plans. Performance can be affected when very large table variables, or table variables in complex queries, are modified. In these situations, consider using temporary tables instead......Queries that read table variables without modifying them can still be parallelized.

Indexes cannot be created explicitly on table variables, and no statistics are kept on table variables. In some cases, performance may improve by using temporary tables instead, which support indexes and statistics.

I've seen cases where this alone, with no other modifications, results in noticeable better performance. This could then be built on further by adding supporting indices. Of course, you should be considering whether you need temporary tables/table variables at all in your stored procedure. If you can remove the need entirely, then look to do that.

Automating Web Performance Stats Collection in .NET

2012-02-20T21:36:00.000Z

You have a web application. You're a .NET developer. Maybe you already have some automated UI testing in place via Selenium, or maybe you don't. What you want to do is automate the collection of some performance metrics about your application.

Q. How would you go about doing that in .NET?
A. Use the following recipe for success.

Ingredients

BrowserMob Proxy by Webmetrics, which (quote) is:
A free utility to help web developers watch and manipulate network traffic from their web applications
Selenium, which (quote):
automates browsers. That's it.
BrowserMob Proxy .NET Library, a .NET library to provide a simple way to work with BrowserMob Proxy and Selenium, written by David Burns (blog | twitter) and myself (you're already on my blog | twitter).

Preparation

Download the BrowserMob Proxy from GitHub
Download the BrowserMob Proxy .NET Library from GitHub (binaries, or get the source and build yourself)
Reference Selenium in your .NET project (available via nuget, or from seleniumhq)
Reference the BrowserMob Proxy .NET Library in your .NET project

Make and Bake

Example:

using AutomatedTester.BrowserMob.HAR;
using OpenQA.Selenium;
using OpenQA.Selenium.Firefox;

namespace AutomatedTester.BrowserMob.Example
{
    public class ExampleClass
    {
        public void ExampleUse()
        {
            // Supply the path to the Browsermob Proxy batch file
            Server server = new Server(@"C:\BrowserMobProxy\bin\browsermob-proxy.bat");
            server.Start();

            Client client = server.CreateProxy();
            client.NewHar("google");

            var seleniumProxy = new Proxy { HttpProxy = client.SeleniumProxy };
            var profile = new FirefoxProfile();
            
            profile.SetProxyPreferences(seleniumProxy);
            // Navigate to the page to retrieve performance stats for
            IWebDriver driver = new FirefoxDriver(profile);
            driver.Navigate().GoToUrl("http://www.google.co.uk");

            // Get the performance stats
            HarResult harData = client.GetHar();           
 
            // Do whatever you want with the metrics here. Easy to persist 
            // out to a data store for ongoing metrics over time.
            
            driver.Quit();
            client.Close();
            server.Stop();
        }   
    }
}

What's great is that if you already have some Selenium tests in place, you can add in the collection of performance metrics quickly and easily. This gives you the ability to collate performance metrics over time - a perfect way to identify problem areas to investigate and to quantify performance improvements you make. To learn more about what is in the performance data, check out these links which go into more detail about the HAR format (HTTP Archive) - this is what Webmetric's BrowserMob Proxy returns, which we expand out into a POCO structure (HarResult type).

BrowserMob Proxy allows you to do some pretty funky stuff, such as:

blacklisting / whitelisting content
simulate network traffic / latency

The .NET library wrapper we've made available supports this functionality. Hopefully, this will come in useful for those in the .NET world!

Excluding nodes from XML data before returning from SQL Server

2012-01-16T10:36:00.000Z

This post follows on from a question I recently replied to, for how to exclude a specific node from an XML column value before returning it, using TSQL.

Example setup

CREATE TABLE Example
(
ID INTEGER IDENTITY(1,1) PRIMARY KEY,
XmlField XML
)

INSERT Example (XmlField) VALUES ('<Root><ChildA>Value I want to see</ChildA><ChildB>Value I do not want to see</ChildB></Root>')

So if you want to return the XML minus the ChildB node, how do you do it?

Modify it

Literally, using the modify method that is supported on the XML data type. To quote MSDN, modify:

Modifies the contents of an XML document. Use this method to modify the content of an xml type variable or column. This method takes an XML DML statement to insert, update, or delete nodes from XML data. The modify() method of the xml data type can only be used in the SET clause of an UPDATE statement.

That last statement, to me at least, seems slightly open for misinterpretation as strictly speaking it isn't only valid in an UPDATE statement - you can use it as part of a standalone SET statement on an XML variable (though yes, to UPDATE that variable).

So, while you can't do something like this:

SELECT XmlField.modify('delete /Root/ChildB')
FROM Example
WHERE ID = 1

as it results in the following error:

Msg 8137, Level 16, State 1, Line 1
Incorrect use of the XML data type method 'modify'. A non-mutator method is expected in this context.

you can instead do this:

DECLARE @xml XML
SELECT @xml = XmlField
FROM Example
WHERE ID = 1

-- Delete the node from the XML in the variable
SET @xml.modify('delete /Root/ChildB')

SELECT @xml

That does the trick.

Dealing with multiple rows

If you wanted to do something like this from the database side when returning multiple rows then you'd need to either:

create a scalar UDF to perform the removal. e.g.
```
CREATE FUNCTION dbo.RemoveNode(@xml XML)
 RETURNS XML
AS
BEGIN
 SET @xml.modify('delete /Root/ChildB')
 RETURN @xml
END
GO

SELECT dbo.RemoveNode(XmlField) AS XmlField
FROM Example
```
Note the parameter to the modify method must be a literal string, so you can't parameterise the UDF to make it a bit more generic by using that parameter in the call to modify().
SELECT the xml values into a temp table/table variable, do an UPDATE like this:
```
UPDATE #Temp
SET XmlField.modify('delete /Root/ChildB')
```
and then SELECT from the temp table/table variable.
Use SQLCLR

Of course, the typical question should be asked - do you need to do this on the database side, or can you return the XML as-is from SQL Server and modify it accordingly in your calling code? In the original case acting on a single XML value, it's OK but when you start looking into scalar UDF's etc for multi-row requirements then you should be careful to check performance.

Round up of 2011

2012-01-02T22:32:00.000Z

Another year is done and dusted so it's that time again - time to reflect on the past year.

Quick Timeline

March: Mongo UK - London
A chance to attend my first NOSQL conference, having recently entered in the world of MongoDB for some projects. Picked up some good pointers and food-for-thought.
Just some words: NOSQL, hard seats, gonna-need-a-bigger-venue, enlightening.
April: SQLBits 8 - Beside The Seaside - Brighton
Highlight of the year for me, conference-wise (was unable to make SQLBits 9 - Liverpool). Fantastic venue, truly awesome speakers/technical content and a really great crowd of people - a true community. Out of all conferences I've gone to over the years, SQLBits never fails to stand out and this one took things to a whole new level.
Just some words: Crappy code, community, awesome, invaluable, bean bags.
April: Microsoft Community Contributor Award
I really enjoy chipping in on sites like StackOverflow and the MSDN forums, and this was a nice (humbling) surprise when I got the email to say I'd received this acknowledgement (blog).
Just some words: Surprise
July: Job Change
After a great 4 and a half years, it was time to move on to a new role. Took the opportunity to work with fellow SQLSoton attendee Matt Whitfield (twitter) - who by the way, has some great (FREE) SQL Server tools which can be found here.
Just some words: New challenge, acquisition, end of an era.
September: Mongo UK - London
A chance to brush up on the latest in the world of MongoDB after a few months away from it.
Just some words: NOSQL, comfy seats, information gems, lego man USB stick.
Monthly: SQLSoton User Group
A really good year of UserGroup meets down here in Southampton every month. Thanks to Mark Pryce-Maher (twitter) for his time and effort, and all the speakers who have taken the time to give a talk. From the first meeting just over a year a go - a small group, in a tiny side room, with no projector - to now - a larger crowd, in a bigger hall, with a projector - it's obvious SQLSoton is providing a valuable SQL Server community service to the Southampton/South Coast area. Check out all the UK SQL Server UserGroups over at SQLServerFAQ.
Just some words: Community, pizza, swag, network.

Top 3 Blog Posts By Views

* OK, so that's the top 4, not the top 3. But that's because I suspect #3 is actually skewed by people looking for "an entirely different kind of site"...I know I had to be careful which links to click when googling for Cassandra (as in the NOSQL database, obviously) and .NET!

Open Source Projects
I started on a few little side projects, which I stuck up on to GitHub for the world to see and (hopefully) use. First came a .NET app to load some of the freely available geographical datasets from Ordnance Survey into SQL Server (GitHub) which relates to blog post #4 above. Next came a MongoDB-backed ASP.NET session state store (GitHub). It was a lot of fun working on these, even though they are pretty trivial things, and was great to actually get some nice feedback from people who found and started using them.

Bottom Line
2011 was a good year. A lot of learning and new challenges meant I was kept on my toes. Looking back though, there are definitely things I'm not pleased about. I dropped the blogging ball in the second half of the year - activity pretty much flatlined for various reasons. So I'm disappointed about that. To quote a tweet by John Sansom (twitter)...

blogging is like going to the gym. Once you fall out of a routine it's tougher to get back into your rhythm

True that. Looking forward to 2012!

OS CodePoint Data Geography Update Sept 2011

2011-09-15T21:44:00.000+01:00

History:
GB Post Code Geographic Data Load to SQL Server using .NET
OS CodePoint Data Geography Load Update

Following a comment on my original post listed above, it appears the structure of the CodePoint data file has changed. This means that the importer will fail when attempting to import the latest data files supplied by Ordnance Survey as the columns in the files have changed.

I've pushed an update to the project on GitHub to address this - it now takes a sensible approach so the index positions are no longer hard coded. Instead, the CodePoint importer now takes an extra argument - the path to the CSV file that contains the column header listing as supplied in the supporting docs in the download. The column indices we are interested in (for Easting, Northing and PostCode) are worked out from this. Obviously makes it a bit more future proof, so if the structure changes again in the future, it should pick up on that automatically.

This is an update to the CodePoint import only at the moment as I haven't had time to check the Scale Gazetteer import as yet. See the project/readme on GitHub for more info.

Getting Started with Riak and .NET

2011-08-18T21:44:00.001+01:00

Earlier in the year, I started playing around with MongoDB using .NET and wrote up a Getting Started guide. Now it's Riak's turn.

Nutshell
Riak is a distributed, schemaless, data-type agnostic key-value "NoSQL" database written primarily in Erlang.

Check out the "What is Riak?" page on Basho's wiki.

Riak does not run on Windows so you'll need to choose your supported OS of choice to install Riak on. Having used Ubuntu before for MongoDB, I went down that route so my notes here are oriented that way. So, after getting a Ubuntu VM set up...

Step 1
Install the latest custom .deb package, as per the Basho guide.

Step 2
Before you start Riak up, you need to tweak the configuration in Riak's app.config file. Follow the Basic Cluster Setup guide, but note that when you update the IP address for the http interface per those instructions, also make the same change to the pb_ip config value - this is for the Protocol Buffers interface. For performance, I wanted to use the Protocol Buffers interface instead of the HTTP REST API and had some initial problems as the pb_ip configuration was still set to 127.0.0.1.

You then need to edit vm.args, per the setup guide.

Step 3
Now you're ready to fire up Riak. Run:

$ riak start

Then make sure all is well using:

$ riak ping

If all is well, it will respond with "pong".

Step 4
Now, back in the comfort of Windows (my Linux-fu is seriously lacking) we're ready to get going. There is no official .NET client library at time of writing, but Basho list a few community contributed projects. When I first looked, only 2 were listed and these appeared to be inactive with no updates since last year. It was a bit disappointing to see the lack of support for those of us in the .NET world. I then discovered CorrugatedIron, very hot-of-the-press, under development by OJ Reeves (Twitter | Blog) and Jeremiah Peschka (Twitter | Blog).

The simplest way to get everything you need to get going is via NuGet.

Nu-what?

NuGet. It's a Visual Studio extension that you can download from here. Once installed, fire up VS and create a new .NET 4.0 Framework project. Then in the Solution Explorer, right click the project and select "Manage NuGet Packages...".

In the dialog, search online packages for: CorrugatedIron.

Click Install, and it will download all the assemblies/dependencies you need. When it's finished, you'll see you have references to CorrugatedIron, Newtonsoft.Json and protobuf-net.

Open up app.config and you'll see some initial example config for CorrugatedIron. Configure that accordingly to point at your running Riak node. For example, my Riak node is running on a VM called "Riak1", with the REST interface listening on port 8098 and the protocol buffers interface listening on port 8087:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="riakConfig" type="CorrugatedIron.Config.RiakClusterConfiguration, CorrugatedIron" />
  </configSections>
  <riakConfig nodePollTime="5000" defaultRetryWaitTime="200" defaultRetryCount="3">
    <nodes>
      <node name="Riak1" hostAddress="Riak1" pbcPort="8087" restScheme="http" restPort="8098" poolSize="20" />      
    </nodes>
  </riakConfig>
</configuration>

Step 5
A quick check to make sure you can communicate with your Riak node:

static void Main(string[] args)
{
    IRiakCluster cluster = RiakCluster.FromConfig("riakConfig");
    IRiakClient client = cluster.CreateClient();
    RiakResult result = client.Ping();
    Console.WriteLine(result.IsSuccess);
}

Fingers crossed, and you will get a successful response! From here, you can start playing around with Riak from .NET and find your way round the client.

CorrugatedIron is still very new, so keep an eye on the site and/or on the GitHub repository. Give it a whirl, and be sure to let the guys know how you get on - I'm sure they'd be keen to hear from those using it and the project deserves support from those of us in the .NET world.

Useful tool
It's useful to have some form of GUI over Riak so you can actually "see stuff". I've been using Rekon which I found useful to get up and running with Riak. See the download/install instructions on it's GitHub repository page.

Hopefully, this post will help anyone else wanting to give Riak a try from a .NET perspective!

sp_executesql change between 2005 and 2008

2011-06-27T20:28:00.000+01:00

Today I tripped over what turned out to be a difference in the way sp_executesql behaves between SQL Server 2005 and 2008 when executing a string containing a parameterised stored procedure call.

Take this simplified example:

DECLARE @SQL NVARCHAR(256)
SET @SQL = 'sp_help @obj'
EXECUTE sp_executesql @SQL, N'@obj NVARCHAR(100)', 'sp_help'

In SQL Server 2008 (10.0.4000.0), the above executes successfully.
In SQL Server 2005 (9.00.1399.06), it throws the following exception:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'sp_help'.

Adding the "EXEC(UTE)" before the stored procedure name in @SQL resolves the issue in 2005. As standard, I (usually) standardise on ensuring all stored procedure calls are made with "EXEC(UTE)" even when it is the only statement in the batch. Obviously, in this case it was overlooked and tripped me up!

The importance of "Working Set"

2011-06-24T20:55:00.000+01:00

One of the things that I see cropping up pretty often is this thing called "working set". After recently chipping in on another StackOverflow question on the subject of "What does it meant to fit 'working set' in RAM?", I thought it was a good subject for a blog post. This is really just a copy and extension of my input on that question and focused in certain parts on MongoDB, but is also as relevant to other databases.

"Working set" is basically the amount of data and indexes that will be active/in use by your system at any given time.

Why is it important to keep your working set in RAM?

Accessing RAM is quick. Accessing disk is slow. When querying your data store, if all the data and indexes typically accessed are in RAM, then performance is blisteringly quick. If it's not in RAM, then disk access is required and that is when performance suffers. Hence it is important to ensure you have enough to hold your working set. The moment your working set exceeds the about of RAM you have, you will start to notice the performance degradation as it has to pull stuff back off disk, so it's important to monitor the situation and react.

Crude Example

Suppose you have 1 year's worth of data. For simplicity, each month relates to 1GB of data giving 12GB in total, and to cover each month's worth of data you have 1GB worth of indexes again totalling 12GB for the year.

If you are always accessing the last 12 month's worth of data, then your working set is: 12GB (data) + 12GB (indexes) = 24GB.

However, if you actually only access the last 3 month's worth of data, then your working set is: 3GB (data) + 3GB (indexes) = 6GB.

You need to understand your data set, scenario and the usage patterns, in order to work out a ball park estimate of your working set. Don't expect a black and white answer for what your working set is in your environment, from someone who doesn't know these things.

What if my working set sky-rockets?

Add more RAM. This can be a case of adding more into your existing node(s). Or, if you need non-trivial increases, making use of sharding to split the data over a number of nodes and just bring more nodes online as you need. This provides incredible potential to scale out your workload.

The key point is to ask yourself: do I have enough RAM for my working set? If the answer is: "I don't know", then get yourself to the position of knowing.

Upcoming SQLSoton UG - 6th July 2011

2011-06-23T14:15:00.001+01:00

The next SQLSoton (twitter) UserGroup meet is coming up on Wednesday 6th July. Matt Whitfield (twitter | blog) will be presenting some "notes from the field" and Alex Whittles (twitter | blog) will be giving a talk on automating cube documentation with SSRS, DMV & spatial data.

Don't forget to register!

Oh, and did I forget to mention? There's an XBox up for grabs. See you there? Thought so :)

If you can't make it, untake it

2011-06-22T11:33:00.000+01:00

There's some great free events out there; SQLBits community day and the upcoming SQL In The City day by Red Gate are just two examples. With the high demand for spaces at events like these, it is well worth getting in there ASAP to reserve your space before they all go and you end up on a waiting list.

But if you find you can no longer make it, make someone else's day by cancelling your place so that it can be freed up for those on the waiting list. You may not personally be losing out by not taking the time to cancel your place, but someone else will be.

So...."if you can't make it, untake it"

P.S. Yes, I know, not my greatest blog title ever...

MongoDB ASP.NET Session State Store Provider

2011-05-03T21:03:00.000+01:00

Please note: v1.1.0 of this Sesssion State Provider is now available. See blog post: http://www.adathedev.co.uk/2013/03/mongodb-aspnet-session-store-provider.html

I've pushed up an initial drop of a custom ASP.NET session state store, backed by MongoDB, to my GitHub repository. After a quick test it seems OK, though could do with a real hammering before being deemed production worthy! So if you want to use it, just make sure you give it a good test through and most importantly, let me know how it goes! It's based on the sample custom session state provider in this MSDN article, basically just with the MongoDB specific bits swapped in.

Session state is stored in a "Sessions" collection within a "SessionState" database. Example session document:

{
    "_id" : "bh54lskss4ycwpreet21dr1h",
    "ApplicationName" : "/",
    "Created" : ISODate("2011-04-29T21:41:41.953Z"),
    "Expires" : ISODate("2011-04-29T22:01:41.953Z"),
    "LockDate" : ISODate("2011-04-29T21:42:02.016Z"),
    "LockId" : 1,
    "Timeout" : 20,
    "Locked" : true,
    "SessionItems" : "AQAAAP////8EVGVzdAgAAAABBkFkcmlhbg==",
    "Flags" : 0
}

Inline with the MSDN reference ("ODBCSessionStateStore" changed appropriately to "MongoSessionStateStore"):

If the provider encounters an exception when working with the data source, it writes the details of the exception to the Application Event Log instead of returning the exception to the ASP.NET application. This is done as a security measure to avoid private information about the data source from being exposed in the ASP.NET application.

The sample provider specifies an event Source property value of "MongoSessionStateStore". Before your ASP.NET application will be able to write to the Application Event Log successfully, you will need to create the following registry key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\MongoSessionStateStore

If you do not want the sample provider to write exceptions to the event log, then you can set the custom writeExceptionsToEventLog attribute to false in the Web.config file.

The session-state store provider does not provide support for the Session_OnEnd event, it does not automatically clean up expired session-item data. You should have a job to periodically delete expired session information from the data store where Expires date is in the past, i.e.:

db.Sessions.remove({"Expires" : {$lt : new Date() }})

Example web.config settings

<configuration>
  <connectionStrings>
    <add name="MongoSessionServices" connectionString="mongodb://localhost" />
  </connectionStrings>
  <system.web>
    <sessionState
        mode="Custom"
        customProvider="MongoSessionStateProvider">
      <providers>
        <add name="MongoSessionStateProvider"
             type="MongoSessionStateStore.MongoSessionStateStore"
             connectionStringName="MongoSessionServices"
             writeExceptionsToEventLog="false"
             fsync="false"
             replicasToWrite="0" />
      </providers>
    </sessionState>
  </system.web>
</configuration>

A few points to note, to give some control over fault tolerance and consistency:

if you want updates to the MongoDB store to be fsync'd before returning, set fsync="true"
if you have a replica set and want updates to be persisted to multiple nodes before returning, set the replicatesToWrite setting to the appropriate number.

SafeMode is currently always enabled.

If you give a whirl, please let me know how you get on.

Microsoft Community Contributor Award 2011

2011-04-29T20:22:00.000+01:00

For a number of years now (since 2002 to be exact), I've been chipping in with my 2 pence on various online Q&A sites. As I mention in one of my first blog posts from just over a year ago, "Ways to improve technical skills" and one back in August on "The importance of being earnest", participating in sites like StackOverflow and the MSDN forums is a great way to keep you on your toes technically. I've learnt a lot from picking through other people's questions and challenges that they are facing, and attempting to provide some input. It gives exposure to real world problems outside of the ones I face in my own job and that I may not otherwise face. I've found it really does broaden my horizons and help me to learn more about my favourite technologies - SQL Server, C# and more recently MongoDB have been the main focus for me. Not only that, but I actually really enjoy it.

Bearing in mind these reasons for me taking part in online communities like this and what I've personally got out of doing so, I found it quite humbling to have received the Microsoft Community Contributor Award 2011. It came as a surprise; I certainly wasn't expecting it. From what I understand, one way to be selected to receive the award is to be recommended by an MVP. If that was how I came to be selected (which I'm not sure it was), then my thanks go to whoever did that for me - it was a nice surprise to open my emails to!

5 Tips For Attending Conferences

2011-04-04T12:01:00.000+01:00

With SQLBits 8 being this week in Brighton and it being set to be the biggest and best one yet, it got me thinking.

If I was a first timer to a conference like this, what should I expect?

So here's my top 5 tips:

Talk.
It's easy to be daunted by a room full of people who all seem to be in little groups chatting away. You think everyone else there knows someone, except you. Wrong. Of course there are people that know each other, but there will be others who don't. Don't be afraid to try and strike up a conversation with people. See it as an investment - the more people you chat to, the more people you will know at future conferences. On twitter? Play "Tweep Bingo" - try and hunt down the people you follow - that way you've got an instant "foot in the door" so to speak (unless you ignore / don't read any of their tweets in which case why are you following them?!).
Listen.
Well, wouldn't be worth talking if you didn't listen would it? The main point here is, when you're in a session, focus on listening. Don't try to keep notes of everything the speaker says because:
a) you'll get cramp in your hand, and you'll get it bad (personal experience, QCon 2010)
b) you'll be focusing on hearing and writing as opposed to listening and learning
There is an amazing amount of content, let your brain try to sponge it up, not your notepad. That's not to say don't make any notes, just don't fall into the trap of trying to get everything.
The videos will more than likely be posted online anyway for you to watch back at your leisure (they are for SQLBits).
Bearings.
Find out where the rooms are that you want to attend sessions in and make sure you know where you are going for your next session to give yourself time to get there. You don't want to be sitting in a room at the start of one session when they start talking about Squirrels when you actually wanted to hear the talk on Meerkats the other side of the building (no I haven't been to an animal conference before, but yes I have found myself in the wrong room - personal experience #2 from QCon 2010). Plus, it pays to be prepared to get to the very popular sessions - I remember Conor Cunningham's session at SQLBits 6 and the room was packed to the rafters.
Etiquette.
Switch off your phone or stick it in silent mode. Don't run the risk of annoying the rest of the room by having it go off mid-session.
Socialise.
A lot of conferences have after parties or evening events. Go along to them if you can, they're a great way to meet up and have some banter with others in an often very different environment. There's often free food and drink to be had if that sways you!

OS Data Importer Supports Scale Gazetteer Dataset

2011-03-27T21:54:00.001+01:00

I've just pushed some updates to GitHub for the Ordnance Survey Data Importer .NET app I've been working on every now and then (see my previous posts: GB Post Code Geographic Data Load to SQL Server using .NET and OS CodePoint Data Geography Load Update).

Aside from a little re-jigging to potentially pave the way to load more Ordnance Survey data files to SQL Server in the future, it now supports importing the 1:50000 Scale Gazetteer data file to SQL Server (available to download from here). To quote the Ordnance Survey site:

The 1:50 000 Scale Gazetteer is a reference tool or location finder, similiar to the index in a road atlas. It can be used as a simple list to find out relevant coordinates and six-figure grid references for a town or area.

At the time of this post this data file contains 259,113 locations taken from the OS Landranger Map series.

Example Use
To load the 1:50000 scale gazetteer data file that has been downloaded and extracted to C:\OSGazetteerData\50kgaz2010.txt, into dbo.Gazetteer table in MyDatabase on server SQLServerA, and load county codes into table: County and feature codes into table: Feature, run the following from the command prompt:

OSCodePointDataImport.exe GAZETTEER SQLServerA MyDatabase dbo Gazetteer County Feature "C:\OSGazetteerData\50kgaz2010.txt"

The Gazetteer database table will consist of the following columns:

SeqNo INTEGER PRIMARY KEY
PlaceName VARCHAR(60)
CountyCode CHAR(2) FK to County.Code
FeatureCode VARCHAR(3) FK to Feature.Code
Longitude FLOAT
Latitude FLOAT
GeoLocation GEOGRAPHY

The County lookup table will consist of the following columns:

Code CHAR(2) PRIMARY KEY
Name VARCHAR(60)

The Feature lookup table will consist of the following columns:

Code VARCHAR(3) PRIMARY KEY
Description VARCHAR(50)

The source is on GitHub here: https://github.com/AdaTheDev/Ordnance-Survey-Code-Point-Data-Importer

MongoDB Journaling Performance - Single Server Durability

2011-03-18T10:34:00.001Z

Note: Relates to v1.8.0 release.

v1.8 of MongoDB (production release hot off the press, available from here) brings single server durability to MongoDB via its support for write-ahead journaling. The following summarises the key points from the journaling documentation, administration guide and from my initial playing around.

you have to specify the --journal switch for mongod in order to enable journaling (was previously --dur prior to production release)
journal files are created within a journal subfolder of your dbpath, up to 1GB each in size
in the event of a crash, when mongod restarts it will replay the journal files to recover automatically before the server goes online
if mongod shuts down cleanly, the journal files are cleared down. Also note that you shouldn't end up with a lot of journal files as they are rotated out as they are no longer needed
batch commits are performed approximately every 100ms (note the mention in the docs that this will be more frequent in future)
preallocation of journal files may be done when mongod starts up. If it decides this is worth doing, it will fully provision 3 x 1GB files BEFORE accepting any connections - so there could be a couple of minutes delay after starting up before you can connect. These preallocated files are not cleared down when mongod is shutdown - so it won't have to create them each time.
when running with journal files on a SATA drive, I found that it chose not to preallocate them. When I set up a junction to map the journal folder onto a separate 15K SAS drive, it did then choose to preallocate the files.

Durability vs. performance - what's the cost?
I wanted to get an idea of how much this journaling costs in terms of performance. Obviously there's going to be a hit, I just like to get a rough feel for just how much. So I took a test 295MB CSV data file containing 20 million rows of data with 2 columns: _id (integer) and val (random 5 character string) and loaded into a fresh database, with/without journaling enabled.

Tests were run on a single machine, Intel Xeon W3520 Quad Core, 10GB RAM, Disk 1=7200RPM SATA, Disk 2=15K SAS, Win 7 64Bit. MongoDB data is on Disk 1 (slower but larger disk).

Journaling?	Journal location	Files preallocated?	Import time (s)	Avg. Import Rate/s	Performance
No	n/a	n/a	291	68729
Yes	Disk 1 (same as data)	No	442	45249	-34%
Yes	Disk 1 (same as data)	Yes (manually*)	422	47393	-31%
Yes	Disk 2 (separate from data)	Yes	448	44643	-35%

(*) mongod always chose not to preallocate when the journal directory was on the slower disk (same as the data) so I had to manually preallocate the files by copying the ones that were created when the faster disk was used.

I couldn't run the test with the journals on disk 2 without them being preallocated because they were always preallocated and you can't delete them all while mongod is running.

Summing up
In my tests, I found:

using --journal resulted in about a 30-35% drop in throughput for my mongoimport job (just under 70K docs/s down to less than 50K docs/s)
preallocating of journal files (as you'd expect) helps as it doesn't have to create the files as it's going along
setting up a junction on Windows to map the journal directory onto a separate (and faster) disk to where the data is resulted in slower performance, presumably due to the overhead of the junction redirects. If there's another/better way of doing this I'd be interested to know. Also I haven't run this on Linux, so maybe there would be less of a hit on that. Personally, I'd like to see support for the journal folder to be explicitly configurable so you can point it at a separate disk.

Based on these results, for me, the decision on whether to use journaling or not would come down to how much I actually need single server durability. Is it critical for my specific use? Could I live without it and just use replica sets? What is the value / importance of it for my data vs. raw performance? Let's not forget this is a new feature in MongoDB. As stated in the docs, there are a number of cases in 1.8.1 for performance improvements with regard to journaling. Definitely something to keep an eye on.