Quick win - check your table variable use

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. [Read More]

Automating Web Performance Stats Collection in .NET

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 [Read More]

Excluding nodes from XML data before returning from SQL Server

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 CREATETABLEExample(IDINTEGERIDENTITY(1,1)PRIMARYKEY,XmlFieldXML)INSERTExample(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. [Read More]

Round up of 2011

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 [Read More]

OS CodePoint Data Geography Update Sept 2011

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. [Read More]

Getting Started with Riak and .NET

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. [Read More]
Riak  .NET  csharp  nosql 

sp_executesql change between 2005 and 2008

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@SQLNVARCHAR(256)SET@SQL='sp_help @obj'EXECUTEsp_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: Msg102,Level15,State1,Line1Incorrectsyntaxnear'sp_help'.Adding the “EXEC(UTE)” before the stored procedure name in @SQL resolves the issue in 2005. [Read More]

The importance of "Working Set"

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. [Read More]

Upcoming SQLSoton UG - 6th July 2011

The next SQLSoton (twitter) UserGroup meet is coming up on Wednesday 6th July. Matt Whitfield (twitter) 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

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. [Read More]