The developer notes series is a set of brief articles or notes that serve as a reminder/knowledge base for problems I have encountered and the solutions to which I am sure to forget.

The SharePoint 2007 diary is to document my experience trying to solve a series of problems that I encountered.  It is my current hypothesis that they are all steming from the same source.  What is it?  Hehehehehe, I don't know as yet.  So read on....

Recently I was asked to evaluate some performance problems related to a SharePoint 2007 site that another team had built.  Each page was taking about 4 minutes to render.  The site had a rather beefy topology of 2 web servers, 2 applications servers, a database running on a cluster etc.  Without going into hardware details I determined hardware horsepower was not the issue.  The database in question was large.  Not huge just large at about 45 GB.

Please note:  I have never built a SharePoint (any version) application. However, I have tinkered around with a few.

My first steps to solve the problem can be found here.  At the end of that process I thought all probems were taken care of and I can go back to the world of custom application development.  Was I wrong.  Following is a log of all what has happened and my struggle in seeking a fix.

As I mentioned in my  previous post creating a well configured object cache will help performance of the Content Query Web Part (CQWP).  While this may be true, just configuring your object cache is not the end all.  The main problem with this approach is that when new content gets created you do not get to see it.  Well may be you can.  The object cache offers two options on when to clear the cache and rerun the query:

• Each time there is new data or existing data is edited.  Problem with this approach is that if you have a publishing site that creates lots of new pages then your cache is quite useless and the first user to hit the CQWP after any edit or new content is going to face a performance penalty.
• Refresh the cache periodically.  Well then new content will not be immediately visible.

So we have a problem.  I needed another approach to improving performance on the CQWP and I needed to understand why performance was so poor on this particular application.

A search on the web revealed no systemic problem with SharePoint 2007 CQWP having significant performance problems.  So why was this application behaving so poorly?

My search for an answer finally ended up becoming an issue ticket with Microsoft.  It was determined that I had too much data and therefore the query will run slow.  Honestly, I do not buy this argument.  SharePoint stores all content in a single table called AllUserData.  The table in this application had about 200,000 rows of which about 20,000 rows were of the Content Type that I was querying.  Clearly this should not take 4 minutes.

Anyway I decided to agree with Microsoft for the time being and asked them what is that I should do if in their opinion I had too much data?  They recommended that I reduce the amount of data that I am querying.  Well this led me to my first known problem with the CQWP.

They Left Out Relative Dates for Filters

Somebody needs to be fired over this.  How did a powerful query tool end up on an enterprise content management system without the ability to generate queries with relative dates?  One cannot create a query using the CQWP whereby I will view content created in the last 15 days.  I can create a date filter using Today or using a specific date.  I can create a filter like date <= Today AND date >= 2007/10/01.  But what I cannot do is say date >= Today - 15 days.

Strangely the technology for Cross List Queries allows for queries with relative dates. So why did the CQWP UI not allow for this?  Well that is anyones guess.

So now I started a new quest.  How do I set relative dates for a CQWP filter.  I ended up at the following MSDN article.  http://msdn2.microsoft.com/en-us/library/aa981241.aspx.  For a moment I was excited but then that was lost soon.  The article proposes two ways of customizing the CQWP.

• Query Override:  This allows the designer to specify a query that will be executed each time.  The syntax for this query allows you to specify relative dates.  The problem is that this query overrides any UI request.  So just exactly what is the point of a CQWP?  Just put a web part with a cross list query in it.
• Filter Override:  This allows you to set a default query, one which can be overriden by a user via the CQWP UI.  Ahaa! this sounds promising.  So I create a webpart XML file with the appropriate filter values for a relative date and upload the file.  Now I run into trouble.  Let's say I set a filter value of date >= [Today]-15.  Now let's say you wish to edit the query to change the sort order as displayed in the CQWP.  So I click on modify web part and the CQWP tool part window opens showing me my settings.  Well all but one.  You see, CQWP tool part has no way of displaying [Today]-15 so what it does for date >= [Today]-15 is date >= todays date.  now unless the user clicks cancel, i.e., if the user clicks Apply or OK, the relative date filter is lost forever and is replaced by a completely incorrect date filter.

So much for customizing the CQWP.  What this meant was that its time to create a CQWP of my own.  More on that in Part 2 ....