Short solution for short problems

MOSS 2007 list performance drop

Observation : MOSS 2007 list performance drop

Recently, we discovered that the performance of the MOSS 2007 implemenation of our client dropped significantly for a specific list. The list contains some 1200 records. Most of them contain attachments. The loadtime for the default view would extend to over 20 seconds. Switching views made things even worse!


First, the server performance was consulted while loading the list. No special or irregular behaviour of the processor or memory. Services were checked to find out if a service like for example the search was performing a heavy index. No special observations. Then we placed indexes on the key columns. This made no visible difference in performance.

How is it possible that only that list is slow in loading, while other list and doclibs in that same site have a normal loadtime? It had to be something list-specific. And what is specific to a list?  It's views...

Interpretation : Optimizing the GROUP BY

After scrutinizing the views of that list, we discovered that the there were massive groups in certain views. The "group by" statement resulted in records being categorized in over 100 groups. Setting a limit to the number of groups improved performance drastically!
When showing 100 groups on a page, the loadtime took 15-20 or even more seconds. When tuning down the number of groups to 50, the page loaded under 10 seconds.

We tested further and discovered that the number of items under a group hardly alter the page loading time when the groups are collapsed on start. A page with 1 group and 500 items under it will load many times faster than a page with 500 groups and just 1 item under each of those groups.
The key is be wise with GROUP BY statements.

Conclusion : Relation between GROUP BY statement and load time

  1. Group your items in less then 50 categories for optimal performance
  2. Show as few groups as possible on a page (max 50 groups/page)
  3. Place column indexes on the columns you group by 

Applying these principles solved our issue on the spot...

We hope this information will help you further optimizing your WSS/MOSS 2007 environment.

Daily WSS: Sites


An administrator or a user with sufficient rights has the possibility to create new sub-sites. He can choose a template and colour theme provided by the organization. You can create for example sub sites for each project. These sites are created from a template and contain automatically the correct layout and content. The sites can have different security then their mother site or children.
There is a possibility to save the site as template, with or without content.
It is possible to create these sites with web services, hence an external application can create these sites. From your site, you could create a new sub site when a new project is launched. Each site has a search functionality that can search the complete content of the site, including the content of the documents. Site content is created with web parts. You can have different web parts, HTML content, pictures list, document library web part (a view on the content of the library), custom web parts, etc.

Daily WSS: Content types

Content Types

Content types are types created by your organization. They can describe a document type or list item. A content type has its own workflow and specific metadata. These types can be reused throughout the entire site and are managed at site level.

For example: a financial form, it has a workflow that sends an email to the financial assistant, and has extra fields like department, total cost … When somebody creates a document of this type; it inherits these properties and activates the workflow.
You can still add extra metadata or specific workflows on items.

Reference calculated fields

 When we use advanced functions, sometimes we need to reference one calculated field to an other. In Sharepoint this is possible.
And when we accidently make circular references we get this wonderful error:

The formula contains a circular reference (the calculated column refers to itself).  You must remove or change the circular reference.


Here is an example of a formula based on an other calculated field:



This fomula checks if the field contains a "\", and if it does, it takes the substring on the right of the "\",
else it displays and empty string: "".

You can find everything on functions that you can use in calculated fields on: http://office.microsoft.com/en-us/sharepointtechnology/HA011609471033.aspx

Using advanced functions in calculated fields

Recently I had a nice challange in Sharepiont 2007.
I had to group a view by a field, but not just group it on the text but on part of the value.
Eg. The field contained a value like this: A chapter\a subchapter.
The view had to be grouped by chapter and secondly by subchapter.This is the simple solution to the problem: 

I created 2 new calculated fields in the sharepoint list.
The first field called chapter, the second subchapter.
The chapter field had to contain the part of the title field before the "\". And if there was no "\" in the text,
then it had to be the complete string. This is the code I used to create this field: 



First we check if the "\" is present in the string, if not we just use title as it is. If "\" is present, then we take the left of the "\".

Here are some examples:

A testing code\b subtest 2     --> becomes: A testing code

A testing code                      --> becomes: A testing code


The second field had to contain the part of the string after the "\" and in case there was no "\" it had to be empty. Here the code I used:



First we check if the "\" is present in the string, if not we return "". If "\" is present, then we take the right of the "\".

Here are some examples:

A testing code\b subtest 2     --> becomes: b subtest 2

A testing code                      --> becomes:


Then I grouped the view on these 2 fields. 

Since I'm a programmer myself, I know that examples are the best way to learn a code. So here's one more.

This time I had to get some values out of a text field called Soort with this format: text1\text2\text3 .
I had to show text2 and text3 in different columns. The first calculated field called Project contains the string text2.
The code to get text2 out of the string text1\text2\text3 is the following:



So this code return: text2. The next calculated field called Subproject had to contain the string that is in place of text3.
Since Sharepoint code does not have the possibility to split or use loops we had to come up with the following code:

=RIGHT(RIGHT(Soort,LEN(Soort) - FIND("\",Soort)),LEN(RIGHT(Soort,LEN(Soort)-FIND("\",Soort))) - 
FIND("\",(RIGHT(Soort,LEN(Soort) - FIND("\",Soort))))) 


I have not reviewed these functions, I just know that they work.
Maybe it is possible to get the results in an easier way. Feel free to share it with us.


Workflow in Moss 2007

Workflow possibilities with Microsoft



Microsoft offers default 3 possibilities:

VS 2005, Sharepoint designer and out of the box solutions. 

v     Out of the box solutions: By default there are 5 workflows present after installation of MOSS: Approval, Collect Feedback, Collect Signatures, Disposition Approval, Translation Management.

v     Sharepoint Designer: This graphical design allows you to create different actions. This can be used by web designers, there's no need to posses any .Net knowledge.

v     VS 2005: The possibilities in VS 2005 are huge. You can accomplish almost everything, but it has to be developed. These workflows are usable throughout the entire portal. When it is not possible with Sharepoint designer, we have to use VS .net 2005. You'll need in dept .Net and VS2005 knowledge to create workflows.

Comparing Workflow Development Processes

The figure below illustrates the various steps that need to be performed to create, deploy, associate, and run a workflow using each of the authoring tools. In general, the largest difference between the two tools is this:

v   Workflow authoring in the Visual Studio 2005 Designer for Windows Workflow Foundation is performed by a professional developer, who is creating a workflow template that can be deployed across multiple sites, and contains custom code and activities. The developer then turns the workflow template over to a server administrator for actual deployment and association.  

v   Workflow authoring in Office SharePoint Designer 2007 is done by someone other than a professional developer, such as a web designer or knowledge worker, who wants to create a workflow for a specific list or document library. In this case, the designer is limited to the workflow activities on their ‘safe list’, and the workflow cannot include custom code. The workflow author deploys the workflow template directly to the list or document library as part of the workflow authoring process.


For a more detailed comparison of the capabilities and advantages of each tool, see Workflow Development Tools Comparison.

Extra info:


Auditing in MOSS 2007

MOSS 2007 auditing. In many cases it is useful to know how many times a document is opened.Sometimes even when and who? Moss 2007 offers auditing as a standard solution for this need. This is what Microsoft has to say about Auditing: Office SharePoint Server 2007 goes even further and enables auditing at the list or document library level, and control over what types of events should be recorded in the audit log. Office SharePoint Server 2007 also provides a reporting function that uses Microsoft Office Excel workbooks to display and keep records of audit logs. Therefore, Office SharePoint Server 2007 enables you to take advantage of the Windows SharePoint Services auditing support without having to write any custom code. In MOSS 2007 it is possible to track several events on list items.This is done with information management policies. Here’s a good definition about the extra’s Sharepoint server 2007 provides: Office SharePoint Server 2007 provides an administrative user interface that allows you to enable and configure auditing without having to write any code. And office SharePoint Server 2007 provides a reporting aspect. More specifically, Office SharePoint Server 2007 makes it possible to generate Excel workbooks that contain the information about audit events within the Windows SharePoint Services audit log.  So MOSS makes it easy for us to track and report usage of documents and lists. Here how Microsoft describes the configuration of auditing: 

We start by examining the built-in Office SharePoint Server 2007 support for configuring auditing within a site collection. Go to the site setting page of any site within a server farm that has Office SharePoint Server 2007 installed. You find that many links are added here by Office SharePoint Server 2007-specific features. Inside the Site Collection Administration section, locate a link with the caption Site Collection audit settings as shown in Figure 7.


Figure 7. Site Collection Administration



Clicking the Site collection audit settings link opens an Office SharePoint Server 2007-specific application page named AuditSettings.aspx. This page provides a user interface (Figure 8) for enabling and configuring auditing settings for the current site collection. As shown in the Figure 8, the AuditSettings.aspx application page makes it possible to configure site collection auditing at various levels of granularity.


Figure 8. AuditSettings.aspx page



In addition to configuring auditing at the site collection level, Office SharePoint Server 2007 also enables you to configure auditing at a much more detailed level. This support is made possible through the information management policies feature that is included in Office SharePoint Server 2007.

An information management policy consists of a set of rules that a site administrator can define and then apply to a certain type of content. The rules for an information management policy are created and configured in terms of policy features. Features that are provided by default with Office SharePoint Server 2007 include support for auditing, expiration, document labels, and bar codes. The programming model for policy features is also extensible, which allows developers to create their own custom policy features. For developer resources, see the appendix at the end of this article.

Office SharePoint Server 2007 enables you to create and configure an information management policy for a specific instance of a list or document library. You can also create an information management policy for a content type, which then applies to list items and documents in any list or document library that is defined in terms of that content type. The ability to apply information management policies to content types is valuable because it provides more granular control than site collection level auditing. It also mitigates the need to configure audit settings at the level of list instances or document library instances.

Office SharePoint Server 2007 also enables you to define information management policies at the site collection level. This provides extra manageability, because you can define an information management policy once within a site collection, and then apply it to your choice of lists, document libraries, and content types within that site collection.

The Site Settings page has a link with the caption Site collection policies. If you click this link, it opens an application page named Policylist.aspx. This allows you to configure a custom policy, which is scoped to the current site collection. Figure 9 shows an example of a custom policy that was created to configure a standard set of audit events that can be applied to any list, document library, or content type.


Figure 9. Custom policy page



After you define a custom policy at the site collection level, you can then go to the List Settings page for a list or document library. You find a link with the caption of Information management policy settings, as shown in Figure 10. If you click this link, it redirects you to an application page named Policy.aspx, which enables you to create a new information policy. Alternatively, you can apply a policy that is already defined at the level of the site collection. On a server farm with Office SharePoint Server 2007 installed, the Windows SharePoint Services page that enables you to create and configure content types also provides a link that takes you to Policy.aspx. Here you can also create or apply policies.


Figure 10. List Settings page



Figure 11 shows the options that are available through the application page policy.aspx. This is a simple way to apply a custom policy created at the site collection level. It also enables you to define and configure a unique policy that applies only to the current list, document library, or content type. In many cases it promotes a higher level of manageability. For example, you can create all of your policies at the site collection level, and then simply apply your policies to the appropriate lists, document libraries, and content types.


Figure 11. Applying custom policies



This  can also be found at:http://msdn2.microsoft.com/ look for: OfficeSharePointServer2007 ValueAddedSupportforAuditing  When you start using this nice feature, you may find strange results when generating a report. This is because most events only work with office documents. If you try to check how many times a specific pdf document in a library has been viewed, it's impossible. So if you need to now how many times a certain non-Office document has been viewed, then you find yourself writing custom code. Maybe I'll try to figure this out later on.