March 2004 - Posts
A long time ago, I started working on an Outlook plugin to allow you to synchronize your appointments, contacts, and tasks with a web service to allow you to maintain the same data across multiple Outlook instances without the use of Exchange. I've mentioned many times about releasing some source code, but finally I'm getting around to it.
The goodies.
A word of caution though. Use this at your own risk. It isn't ready for a full blown release and I've been too busy to clean it up and fully test it. It is currently setup to work with Outlook 2003... changing around some of the references is probably needed to load the Office XP Interop libraries. I also need to remember where the registry key is to tell Outlook to load the plugin.
I'm hoping that towards the end of the semester, things will calm down and I can get back to work on it. Mainly releasing it in hopes that some other people will take a look at it and fix some of my garbage and what not. If people actually want to contribute to it, I'd probably look into getting a Vaultpub repository for it.
After my post yesterday regarding recursion or temp tables, Frans Bouma posted an excellent read on doing hierachy trees in SQL by representing it as a basic binary tree.
This is exactly what I needed for handling our organizational structure at work. I'm in the process of rewriting the application in J2EE and I'm taking the liberty to restructure a couple of the tables that have been problematic. One of them is the organization table. A lot of queries are run against it, and most of them are pretty ugly. Examples:
1) Often, you need to know the structure of the area the person is in. Sometimes the “root” is above them, sometimes it is where they are. This procedure takes their org area, traverses up until it finds the top level of that section (marked by a flag), then traverses back down. We have a spare table setup with the user's ID and rows for the organizations in their area. A lot of redunancy. It is refreshed every time they log in. Since the organization can only go so deep, it is populated using a series of 6 insert/selects (even if it doesn't need to go that deep).
2) As work goes through the approval process, it needs to constantly check if something is ready to go to the next level when something is approved. Something could go to 3 teams, one is done, one just finished, and one hasn't finished. It can't go to the next level until the 3rd one is approved. This gets ugly as things get higher. It creates a temporary tables, does its 6 insert/selects to get all the areas below the level it is supposed to go to, and it checks to see if these areas were assigned to, if they have completed the work, and if it has all been approved.
Using the technique mentioned in Joe's post, I could easily take the first one from about 8-9 queries and a spare (somewhat bloated) table to 1 select query with a join. The second one, I could easily eliminated the creating of the temporary table and cut nearly a dozen queries. It would be useful in countless other areas as well. Definitely what I needed... and my post wasn't even about work related issues!
I have a little dilemma. For nGallery, we want to update it so the “picture count” returns the number of pictures in the album and all of its subalbums, not just in that one album. One thing that complicates it is that you can have subsubalbums, or even subsubsubalbums. Do there is no quick and easy way to “select count(*) from albums where it is this album or is child of this album”.
There are two ways I could do this... with a recursive stored procedure or a stored procedure that uses a temporary table. Which does SQL Server handle faster though? Well, shouldn't say faster... faster != better, necessarily. The recursive function would just parse the album “tree” (get to think back to my fun binary tree exercises in Data Structures classes! seriously, loved that class). The one that uses a temporary table would just loop adding the IDs of albums under the main one until none are left, then just do a “select count(*) from pictures where albumID in (select id from #tmp)”.
I've always thought temporary tables was slow because it had a lot of overhead, but I also tend to think of recursion as a little bit riskier... plus, it could have its own memory overhead, and I don't know if SQL Server has a stack limit.
mszCool posts about some great log parsing tools for IIS 6.
That's exactly what I've been wanting for so long! I remember back in the day when I had Apache dumping my access log into mysql with a little log handler I hacked up. Was nice to do dynamic queries on activity, amount transfered between certain times, or traffic based on IP or path. Really fun stuff!
More Posts