Let’s say you have a group of settings in your database. There are some settings that are specific for each client. But those settings can also be generic and be used for all the clients. So if ClientID column contains the key of your client you use that value but if there is no such value specified the value defaults to the value specified in the column with ClientID set to NULL. So in a way your client specific value inherits from the default value and if there is no specific value the default is used.

Now the things get more complicated if you can have some setting with a client specific value and no default value and some with a default value and no client specific value.

Let’s set up a test table and test data first.

This is a follow up of my last performance related article where I compared the cost of passing XML vs. Table vs. plain Varchar parameters to a procedure without considering the cost of querying the data http://blog.bodurov.com/Performance-Test-for-the-cost-of-passing-parameters-to-a-SqlServer-2008-Stored-Procedure-as-XML-vs-Table-vs-Simple-Varchars.

In this test I added a query to the procedures to see how will the results look if we consider the query cost in the equation.

This is how my modified procedures look:

In this test I want to examine the performance implications of passing a relatively long set of data to a SQL Stored Procedure. I compare performance for passing that data as XML, as Table variable or a list of NVARCHAR variables. The test consists of 10 000 iterations. There is nothing in the procedure, so I am not testing the cost for querying the data but only the cost for passing the data to the SqlServer stored procedure. Following are my procedures.

Parameter as XML:

CREATE proc [dbo].[Test_Xml_Parameter]
    @Xml XML
as
    SELECT 'Return'
GO

Parameter as Table:

When you have to save hierarchical data inside a database you can traverse the tree with recursion and for each note call the database to save it. But that is not the most elegant solution; because your tree might be quite big and you can end up with lots of calls to the database. You may also need to wrap this whole thing inside a transaction but as we all know stretching a transaction between several calls is not the best approach.

What we actually want is to transfer the hierarchical tree inside a single stored procedure and process it there. But how can you pass a hierarchical data to a single stored procedure? Well the answer as often is XML. Let’s assume you have this hierarchical XML: