Recursive SQL query

Have you ever had to do a query on a table that joins to itself to form a hierarchy? For example the employee / manager relationships where the manager has a manager up to the top level of management.

My recent case involved categories of things where the categories could be nested to any level. I needed to find the oldest parent and the youngest parent for each leaf on the tree.

The lazy way (and often the quickest) is to write a set of statements with different levels of joins, then use your knowledge of the data to pull out what you need.

But what if you want to do in a way to impress the boss?

A recursive query will do the trick.

To get started, write a fairly simple query which we will call the Anchor query. This should get back the top level (root) of information. Then you JOIN this onto another query that does the recursion.

It is a very good idea to set a limit on the recursion so you don’t bring your SQL Server down. Actually – I think it defaults to 10,000 levels of recursion. But still, better to set a reasonable limit.

Here is an example:

 with CatHeirarchy (ProductID, CategoryID, ParentCategoryID, Name1, Name2, Level, TopParentName)
 as (
 -- Anchor definition
 select PCM.ProductID, PCM.CategoryID, C.ParentCategoryID,
 cast(C.Name as nvarchar(90)) AS Name1, Cast(C.Name as nvarchar(90)) as Name2, 0 as Level,
 CAST ('' as nvarchar(90)) as TopParentName
 from Product_Category_Map PCM
 inner join Category C on C.Id = PCM.CategoryId
 inner join Product P on P.Id = PCM.ProductId
 Union ALL
 -- recursive
 select A.ProductID, A.CategoryID, R.ParentCategoryID, cast(R.Name as nvarchar(90)) as Name1,
 CAST( A.Name2 as nvarchar(90)) as Name2, Level + 1,
 from CatHeirarchy as A
 Inner Join Category as R
 on A.ParentCategoryID = R.ID
select distinct CategoryID, Name2, TopParentName
 from CatHeirarchy ch
 , (Select MAX(Level) AS Level, ProductID
 from CatHeirarchy
 GROUP by ProductID) maxresults
 where ch.ProductID = maxresults.ProductID
 and ch.Level = maxresults.Level
 order by TopParentName, Name2
 OPTION (Maxrecursion 30)

C# WebRequests without Proxy or Delay

WebRequest objects in C# are useful creations when you want to script interaction with a web server in your application.  However, there are some common gotchas that I always have to look up to rectify.  So here are three of the most common in one handy location.

Nagle Algorithm

First, remove the use of the Nagle algorithm.  The Nagle algorithm is great for protocols like telnet where there is a chance of sending small amounts of data.  It is not so good for a protocol where packet sizes are intentionally small.  It will kill performance while queuing up bytes of data to send.  Use the following:

System.Net.ServicePointManager.UseNagleAlgorithm = false;

Expect 100 Continue

Expect 100 Continue is an HTTP 1.1 addition where a request can detect the readiness of a server before sending the a large body in a post.  The WebRequest object always sends an Expect: 100-continue in the header.  Not all web servers support handling this (i.e. lighttpd).  I suppose there is value to the 100 status code when posting large bodies but for most data transfers (i.e. SOAP, REST, XMLRPC, etc.), it doesn’t seem to be very useful.  Use the following to disable this.

System.Net.ServicePointManager.Expect100Continue = false;

WebRequest Proxy

By default, Windows will use the proxy settings internally set.   If you know your network is local, allowing the .NET framework to evaluate the default proxy settings can take unnecessary time.  You can set .NET to not use or look for any proxy by setting the following code:

WebRequest request = WebRequest.Create (resource);
    request.Proxy = null;


WebRequest.DefaultWebProxy = null;

Please remember to flush.