SharePoint, SQL Server

Nuggets from Working with Large Lists White Paper

Other than the obvious benefit of learning the best methods for dealing with large lists, there are a couple related nuggets covered in the white paper titled Working with Large Lists in Office SharePoint Server 2007 (discovered from the SharePoint Product Group blog) I wanted to blog about which I thought were interesting.

First is concerning the PortalSiteMapProvider.  As mentioned in the paper, this is a not very well known class.  I didn’t know about it, either.  According to the paper, “it was originally created to help cache content for navigation”.  The author, however, uses a method in the class called GetCachedListItemsByQuery to retrieve list items.  To summarize why I like this class (and method), it basically provides logic to manage retrieving items and optimizing that retrieval by using the server cache.  If the items are in the cache, it doesn’t hit the database.  If the items are out of date, it refreshes.  If the items aren’t there, it goes to the database to retrieve them and stores them in the cache for later.  I’ve written lots of code to manage this for various objects in the past so I love seeing something built in for me to take advantage of.

The other nugget I pulled out of the paper isn’t specific to working with large lists but rather gives some nice insight into how list data is stored and retrieved.  All data for all lists is stored in one table within SQL Server.  I did not know this…which is not surprising since I haven’t yet spent time diving into the behind the scenes nuts and bolts.  This is a nice and simple solution and makes it easy to understand where to find data, but it has its consequences.  I don’t think I can explain it any better than the author, so here’s what he had to say:

“After there are approximately 5,000 items in a list, SQL Server will typically choose to lock the entire table for the duration of that change. In this event, all other reads and writes for all lists in all site collections are queued until the previous transaction is complete and the lock is released. This locking behavior occurs whether or not list items are recursively nested so that there are not more than 2,000 items in an individual container.”

Definitely something to keep in mind if you’re seeing some performance degradation.

It’s a nice white paper.  Go read it.


Site Column to Crawled Property to Managed Property

Apparently the lifecycle of a site column (or other property for that matter) to become a crawled and then managed property isn’t easy to remember as I’ve had to “discover” it more than once.  Let’s get it in writing this time…

When a site column is first created, you can do all the search crawling you want.  Full, incremental, doesn’t matter.  It won’t show up as a Crawled Property until it’s actually used and there’s data associated with it.  Once it has data that’s using it, a simple incremental crawl will pick it up and add it to the Crawled Property listing.

Now that it’s a Crawled Property we can create a Managed Property that maps to it, i.e. create a Metadata property mapping.  One thing worth noting when the mapping is created.  When you look to select the crawled property to map to the managed property, you’ll see at least two properties with the name of your site column.  You’ll want to select the one that begins with “ows_”.  After creating the mapping, the Managed Property will be available for you to use, with a caveat.

Caveat:  There won’t be any data stored in the search index associated with this new property right away.  Content that is associated with property mappings is grabbed by the crawl on a document by document basis.  So over time, incremental crawls will eventually get all the content, but the only way to ensure all content is picked up by the new mapping is to do a full crawl.


New Icon on New Items

I got a question today asking what the criteria was for how long the !New icon is displayed on new items in a list.  Hell if I know…So I did some research.

Taking a look at the onet.xml (located in 12 hivetemplateglobalxml) I did a search for the new.gif and found it surrounded by an <IfNew> element.  A search on MSDN leads to this document which states:

“Returns TRUE if the item is considered new. Usually, this means that the item was created after midnight the day before. This element renders its contents if the item was created today, that is, after only one day has passed, the number of days being a registry setting for which the default value is 1.”

I had to read that a few times, but it boils down to two days.

To check that, run:

stsadm -o getproperty -propertyname days-to-show-new-icon -url “<site_url>

It returns 2.

If you want to change it, run:

stsadm -o setproperty -pn days-to-show-new-icon -pv <number_of_days> -url “<site_url>

SharePoint, Windows Workflow

Authorized Types

I ran into this when using SharePoint Designer to create a simple workflow.  When I went to deploy the workflow to my site, I got a bunch of errors that looked like this:

(0, 0) Type System.Workflow.ComponentModel.DependencyProperty, System.Workflow.ComponentModel, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35 is not marked as authorized in the application configuration file.)

The solution is pretty simple:  Add a new authorizedType entry to the System.Workflow.ComponentModel.WorkflowCompiler section in the web.config for the type in question.  For the error above I needed to add:

<authorizedType Assembly=”System.Workflow.ComponentModel, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35″ Namespace=”System.Workflow.*” TypeName=”*” Authorized=”True” />

Kind of odd the System.Workflow types aren’t in there already, but I am running in a minimal trust mode.  Maybe it’s different for full.