Sharepoint 2007 Content Query Optimizer

Project Description

To discover, analyze and correct optimization issues and / or query errors or bug in the MOSS Content Query and Content Query Web Part. Applying this procedure can speed up cross site content query and correct issues where list information either fails to appear, appears incorrectly, or security is not properly applied and users see incorrect data.

Procedure analyzes the content database for a user supplied content type and determines if a content query would fail or if performance of the query could be improved. User can either use procedure to fix content database or review traces and determine another course of action.


Content Query Background - How it works and SQL Database Tables information
Forum and Blogs KBs that discuss the Content Query issue and limitations.

The Problem
How data is arranged in the SQL AllUsersTable for a single list does not matter and any query against just one list works fine, but for the cross site content query, field order is everything and if the content type fields do not occupy the same positions in the AllUserData table across all your lists your query for a specific content type then your query could:

1. Not display data from various lists or limit the number of lists that will be queried
2. Display the wrong data from lists,
3. Cause a security breach,
4. Waste your time trying to figure out what is wrong.
5. Or at minimum run longer, possibly time-out and use more resources than necessary on the SQL Server.

This bug is confirmed by MS in KB 946484.

The error appeared for me as a filtering error. Although everything looked identical in the lists configurations, the same filter would work everywhere except for one or two lists. If the filer was applied then no items appeared from the list, turn it off and the list data appeared.
Turns out the fields I was filtering for the content type were mapped to different SQL Table columns in the "Bad" list than the lists that were "working". Running this procedure corrected the columns and the information appeared correctly. Again the filter worked if I pointed to the single list but broke when I queried the Site Collection.

Although the SharePoint guides stress planning , that is not much help when working with migrated sites and/or lists that were pure a year ago when you first deployed but now have been updated by users and you are adding the new functionality in the way of ContentTypes and the Content Query.

Of course the worst part is that there is nothing in SharePoint to show you what is wrong.

Project Solution
This project consists of a stored procedure that you will need to install on the SQL Server where your content database is installed. It is designed to be complied in a separate work database on the server so it is not installed in the SharePoint content database. When run it will create some work tables and scan the database for a particular content type and display any errors that if finds.

• At minimum you will need
  • read write access to the Work database
  • read access to the content database
  • write access to the content database if need to correct the database tables.

There is an option to print the SQL update commands and/or run the update directly which will correct the database for content types that are not constant. The procedure will rank the content type across lists to determine which list represents the best candidate to use as a master template list for which all other lists will be updated. You can supply a master list template that you wish to use instead. Note that I mean a real list and not a "SharePoint List Template"

Note that any fields which are not part of the master list that appear in the other lists to be updated will simply be moved to other columns in the AllUsersData.

The update procedure performs two tasks

1. Determines which lists are out of sync with the master list.
2. Makes a copy to the list to be updated and the data that will be updated and stores in in the work database tables.
3. For those lists, updates the tp_Fields column in the AllLists table with the new column settings. Technically it updates xml attributes ColName, ColName2, Colname3 for each field if they are different. It does not simply replace the tp_fields column in whole from the master list.
4. For each lists the actual list data is moved from the old column to the new column in the AllUsersData table.
5. After the update, you should reset IIS and the Timer service on the all servers in the farm