The Internet Ramblings of Jacob

I have been working on a fairly large scale financial reporting system for one of our clients and, in this regard, have begun getting really deep into Reporting Services. One of the decisions we made early in the game was to create an all-encompassing data view that would be the datasource for all of the reports we generate. This view joined probably 10-15 tables to report out a lot of different information needed by the reports, all of which worked fine. The problem came in when I incorporated User Defined Functions (UDFs) into the data view.

I used probably 6 different UDFs to return everything from the Federal Fiscal Year (FFY) of a service line item to the authorized amount (and subsequent disbursed amount) for the same line item. The subset of data we had included around 35,000 records and we pulled by FFY. For FFY 2008, which had approximately 3500 records, the data pull took well over 2 minutes and invariably crashed our reports on the server.

I began looking into how to optimize this view because, obviously, if the report is crashing, nobody is signing off and nobody is getting paid.

The problem is that we were using scalar UDFs, meaning UDFs that returned a single value. On its surface, this may not seem like a huge deal but, the way they are used, each returned record called the UDF. So, with 5 UDFs for each record and 3500 records returned for a FFY, the server hit was noticeable. A lot.

So what made a lot more sense was to create a sort of in-memory table with the information I needed and join to it in the view and reference the fields that way.

To make more sense, I'll show you the code I created for the FFY lookup. Remember, before, I called a scalar UDF to get the FFY for each row ... 3500 times (or more).

So the first step was to create the in-line table UDF. Here is my function (I'll explain it after the code):

There are a couple of things to notice about this. First, you set the UDF up differently than a scalar UDF in a couple of ways. For instance, you have to return a TABLE variable . In my example, I am defining the table at this step and creating a primary key in order to create an index (so that it would hopefully pull even faster). I am also SCHEMABINDING to hopefully make it pull that much faster. This is essentially defining a TABLE variable that I can then insert into (notice the bold INSERT command at the bottom). The business logic of the UDF isn't really that important, but I left it in so you could get an idea of what I was trying to do. Plus, if you work with people that use Federal Fiscal Years, this could be useful to you.

Once I have the UDF defined, I can join to it just like any other table:

I went through and eliminated external calls where unnecessary (i.e., where i could just recreated the functionality within the SELECT of the view) and used the table UDFs to replace the scalar UDFs in all other situations. When I did this, the select for my 3500 records went from almost 3 minutes to 37 seconds. In other words, I saw around a 75-80% performance boost.

I think scalar UDFs might be fine for small datasets and probably have their place. But, if you are dealing with large amounts of data, I would probably opt for table UDFs instead.

Just got back from TechEd Developers and the Tweener event. Man, what a good time. Lots of discussions with a lot of people and some new information that will make my life easier as a SharePoint developer. So, of course, I'll share.

COOL THING #1:

The first thing, and this gave me goosebumps, is that you can now install WSS 3.0 SP1 ON YOUR VISTA BOX!!! That is just crazy. Here is a screenshot after my successful implementation:

Notice the "jacob-laptop" URL? That is awesome.

You can find the full instructions on how to do this by using this link:

http://community.bamboosolutions.com/blogs/bambooteamblog/archive/2008/05/21/how-to-install-windows-sharepoint-services-3-0-sp1-on-vista-x64-x86.aspx

One thing that I ran into that wasn't really stated in the above instructions is the problem that occurs if you have Vista's User Account Control (UAC) turned on. I, like a good computer user, had it turned on. There are probably more than a few developers that turned it off as soon as they got their Vista laptops. But, well, I'm a goodie two-shoes. Anyway, if you have the UAC turned on, you will think everything works but then run into a problem once you try to set up your sites. Primarily, you can't set up a new web application, which means you can't add a new site collection, which means you can't create any sites. In other words, all you'll have set up is Central Administration. I tried launching IE as an administrator and that wouldn't work. I created new administrator accounts and tried using those accounts and that wouldn't work. I couldn't get anything to work. But, as soon as I switched off the UAC, it worked.

So, if you have this problem, too, go into your Control Panel and click on User Accounts. At the bottom of the dialogue box that comes up, you will see a link to "Turn User Account Control on or off". Click there, change the checkbox, and click OK. You should be fine after that.

*DISCLAIMER*  the tool provided by bamboo solutions is basically a registry hack (i think) and is not supported by Microsoft. Obviously, when you move code you develop against this installation of WSS up against a real server, you will want to test it again. But this is pretty neato and will especially allow presenters to have a much faster installation of WSS to work with in their presentations (that is what I used it for). 

COOL THING #2:

The other cool thing to happen last week is the release of the Visual Studio 2008 Extensions for SharePoint 2007 (version 1.2):

http://www.microsoft.com/downloads/details.aspx?FamilyID=7bf65b28-06e2-4e87-9bad-086e32185e68&displaylang=en

You can now develop SharePoint Web Parts (and other custom SharePoint applications) in Visual  Studio 2008 (instead of just in VS05).

With my laptop running WSS 3.0 and the new extensions installed, a friend created a running web part for SharePoint in 2 minutes while I was talking to the crowd at the Tweener presentation. I didn't even know he had done it. I just turned abound and BAM a Web Part.

If you are a SharePoint Developer, you probably have goosebumps by now, too. And are probably already downloading these tools for your own development.

My baby loves the computer books...