Content Query Web Part (CQWP) SharePoint 2007 Performance

Monday, 16 July 2007 14:18 by RanjanBanerji

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 step was to run some basic diagnostics on the various servers.  The state of maintenance of these servers was pathetic as best.  High disk fragmentation, no free disk space, temp files and folders scattered all over.  Wow! Anyway, these were unlikely causes for a 4 minute page load time.  The servers were cleaned up.

Then I started running perfmon (in my test environment) and SQL profiler to see what all is happening.  My test environment was obviously not as well equipped but it was sufficient.  I noticed that the major delay was coming from certain queries that SharePoint 2007 was generating.  There were many calls to some very similar queries which were huge in size (irrelevant) and they took up to 40 seconds each to run.  Well, there was my 4 minutes to render a page problem.

The question was why?  Was the problem with SQLServer 2005?  Why was SharePoint generating queries that took so long to execute?  The biggest question was, what can I do to tune this performance issue?

I started down what I thought was the logical path but it turned out to be a bad idea.  I started looking for problems in the SQL database.  Indexes, statistics, etc.  I then realized that even if I added an index to the table in question (allUserData) it will be of no help because the SharePoint queries were specifying what index to use.  Argh!!!!!!!!!!!!  Now what?

This is when it took me a while to figure things out.  Apparently not all SharePOint pages for this application were slow.  But a large number of them were.  All of the sow pages had plenty of web parts on them.  To be specific they all used instance of the same web part:  The Content Query Web Part.

The content query web part, I believe, is new with SharePoint 2007.  SharePoint stores all user data in a table aptly named allUserData.  The table has a few SharePoint columns and then a gazillion columns named something like col1, 2, ....., n.  All user data such as Lists, Pages, etc gets tossed into this table.  Essentially imagine a database with as little normalization as possible.  The Content Query Web Part allows you to easily create queries to extract data that is stored in the ambiguous blob (not BLOB) called allUserData.  The web part also lets you configure how the results are displayed etc.  Apparently a very powerful and useful tool loved by the SharePoint community. 

The Content Query Web Part uses a SharePoint 2007 technology called a "cross list query."  A very misleading name.  When I first heard this term I was sure it was related to cross tab queries.  But alas no.  I guess Microsoft should have called it the across all list query or the list aggregator query or a site collection list aggregator query.  So what does this query do?  Well if you have a list of issues in SharePoint then each site that implements this list sees only data from its own site.  Something like select * from TBL_LIST where siteID = mySiteID.  So what if I want to see all the issues from my site and all the sub sites under me?  I would need a hierarchical query.  Thats what the cross list query is.

When you have a large database with allUserData having millions of rows and you start using the Content Query Web Part rather than a custom web part you are forcing SharePoint into making some really funky queries to extract data for you and of course you have no support for indexes.  End result?  Poor performance.  The performance get poorer the deeper your site tree structure gets.  In fact Microsoft says that the Content Query Web Part can get data from a max of 1000 lists, er! I think they meant 1 list across 1000 sites.

The Solution (READ THIS VERY CAREFULLY)

  • Don't use the Content Query Web Part for large databases with very deep site tree structures.  Create a custom web part with custom queries.  Now you can create indexes that your queries can use.

  • Do not put more than one Content query web part on a single page.  If each query takes time and you have 5 instances of the web part, well I am sure you can do the math.

  • Read the following MSDN article very carefully.  Read the section on the object cache.  http://technet.microsoft.com/en-us/library/cc298466.aspx.  Specifically read the part on Cross-List Query Caching.  The Content query web part uses cross-list queries.

  • Do not be smart and say you do not need to read the article above because you already have.

  • Do not be smart and skim over the section on the object cache section saying I already have this turned on.

The object cache is always turned on.  However it requires to be configured.  The MSDN article above explains how.  You need to set the cache size large enough such that your large query results have a place to be stored.  In order to do this you will have to use perfmon to monitor two SharePoint metrics under the SharePoint Publishing Cache Object:  Cache Hit Ratio and Cache object discards.  

Now keep tweaking your object cache settings until you get a hit ratio greater than 90 and a low cache discard rate.

Also, based on how frequently your data changes and how frequently the end user must see the updates you will want to decide whether the cache is cleared each time a change occurs or at a predetermined interval.

Hopefully these strategies will help you improve unexplained performance issue on your SharePoint 2007 applications.

AN UPDATE October 28, 2007 WATCH WHAT YOU ARE QUERYING

A few days ago I made an observation that I wish I had done so when the whole performance issue started.  The solution I mentioned above is important to tune your SharePoint 2007 application if its is using large complex cross list queries.  BUT, there is one other very important consideration to make.

The Content Query Web Part (CQWP) will query all records in your application (stored in table AllUserData) based on the Content Type you select while formulating the query.  The fact that you say display only 15 items is irrelevant. All data is queried.  In another post I discuss this issue.

What, therefore, becomes very important is exactly what are you querying?  If your Content Type contains a Site Column that holds large volume of data or multiple site columns of large volume of data and you select these site columns in your CommonViewFields property for the CQWP you can find yourself in a lot of trouble.

For example, the application I was debugging had a Content Type with a large amount of formatted text as one of its site columns.  So in the CQWP if I said get data based on this content type and set no filters then the CQWP will query for all records of this type (in my case 60,000 records).  So now imagine, in order to display 15 items you end up query and loading 60,000 rows of data each with large amounts of text.  Figure out the performance on that.

One of the developers had by mistake added this site column using the CommonViewFields property which when removed significantly improved performance.  The CQWP definitely needs to have a better way of selecting site columns to query based on content type in order to prevent such mistakes and to make the CQWP easier and more intuitive to use (hey! Microsoft, ever heard of a multiple select list box or check boxes or dual list boxes for selection?).

Tags:  
Categories:   SharePoint
Actions:   E-mail | Permalink | Comments (8) | Comment RSSRSS comment feed

Comments

February 26. 2008 05:37

Ashok

Hi this is very useful site for me

Ashok

July 14. 2008 20:47

Fadi

Hi,

Have you got a link or something to the Microsoft site about this?

Fadi

December 20. 2008 17:24

trackback

Trackback from Confluence: SharePoint Development Wiki

Content Query

Confluence: SharePoint Development Wiki

June 14. 2009 22:09

Francois P

Thanks!

One thing - in Windows Server 2008, what are the exact objects to add to perfmon? I'm finding SharePoint Publishing Cache objects and I'm wondering if these are the correct ones to monitor?

Regards,
Francois P.

Francois P

June 22. 2009 14:01

admin

Francois,

Yes you will add the SharePoint Publishing Cache object and if necessary select the specific instance.

admin

July 1. 2009 22:57

Precision Engineers

How to add existing webform into share point portal?

Precision Engineers

June 17. 2010 23:01

trackback

Content Query Web Part (CQWP), Cross List Query Nightmare Part 1

Content Query Web Part (CQWP), Cross List Query Nightmare Part 1

Ranjan Banerji

July 26. 2011 16:23

john

Great stuff..!!

john

Add comment


(Will show your Gravatar icon)

  Country flag

biuquote
  • Comment
  • Preview
Loading