SharePoint Cross List Queries not upto the task

I've been using SharePoint Cross List Queries in multiple places in a recent site development project and I've noticed that no matter how you use them with indexed columns or without indexed columns or any other settings these monsters consume huge amounts of memory and are extermely slow. So unless you have a small number of lists and not a lot of items in each list you shouldn't be using them.
In a recent project I was using SharePoint queries to fetch latest posts based on a selection & filtering algorithm from SharePoint forums. Now we had a huge number of forums each located in different sub sites and we needed to run a query where we could get the latest post that matched a specific criteria. Once the system grew to a huge amount of data we realized that the Cross List queries were running very slow and at one point we started getting "Server Out of Memory" exceptions. So to cut a long story short we eventually had to create SharePoint events put them on list adding, updating & deleting events and replicate the fields needed in a SQL table and then run our query on the table.
My personal on why SharePoint Cross List Queries suck in performance: I think when Microsoft developed this feature they only had small lists or a small number of lists in mind plus they also had to make sure that this feature was compatible with other SharePoint features (like list item level security) so they couldn't just translate the CAML in cross list query to a SQL statement and run it on the DB even when you have configured all the columns in your query as indexed columns. So what happens is SharePoint loads all the data related to those lists into memory, sorts them, tries to filter them and then you get a Server out of memory exception on large amounts of data.

Advanced SharePoint Queries! :)

In the previous post I described two methods where you could fetch any data you needed from a SharePoint list, but sometimes we need more than a simple fetch from a list. One of the situations that I had to deal with in the most recent project I was involved in was fetching data from multiple lists scattered across different sub-sites and this had to be done in a performance optimized method (no recursively looping through all sub sites and searching each list separately!).

Let's consider a concrete example:

Assume we have multiple SharePoint blogs setup under a sub-site called 'blogs.' Now as we all know SharePoint blogs are sub-sites in themselves and posts/comments made to a blog are stored in lists under that sub-site. So if we have multiple blogs setup under the imaginary 'blogs' sub-site we will have a site hierarchy similar to this:

/blogs

/blogs/My Blog

• Posts list
  
• Comments list
  

/blogs/Some Other Blog

• Posts list
  
• Comments list
  

/blogs/XYZ Blog

• Posts list
  
• Comments list
  

…

Now suppose we want to display a 'master moderation list' where an administrator can view all posts/comments that haven't been approved yet (are pending) and then do whatever he/she would do with them (we only care about the first part).

This is where SharePoint Cross List Queries can come to the rescue. By creating a Cross List Query you can retrieve all items from all lists under a specific sub-site in one data table and then perform all needed operations on the result:

SPSite site = new
SPSite("http://mysite");

SPWeb web = site.OpenWeb();

CrossListQueryInfo qi = new CrossListQueryInfo();

qi.ViewFields = "<FieldRef Name=\"Title\" />" +
"<FieldRef Name=\"Body\" />" +
"<FieldRef Name=\"ID\" />" +
"<FieldRef Name=\"PublishedDate\" />";
qi.Query = "<Where><Eq>" +
"<FieldRef Name=\"_ModerationStatus\" />" +
"<Value Type=\"ModStat\">" +
"Pending" +
"</Value>" +
"</Eq></Where>" +
"<OrderBy>" +
"<FieldRef Name=\"Modified\" Ascending=\"FALSE\" />" +
"</OrderBy>";
qi.RowLimit = 100;
qi.Webs = "<Webs Scope=\"Recursive\" />";
//101 is the id for blog lists. Obviously you have to change this
//for any other list type that you are targeting
qi.Lists = "<Lists ServerTemplate=\"101\" >";
qi.WebUrl = "/blogs";
qi.ShowUntargetedItems = false;

CrossListQueryCache qCache = new CrossListQueryCache(qi);
DataTable dt = qCache.GetSiteData(web);

foreach (DataRow dr in dt.Rows)
{
//do whatever you need to do with each item
}

A couple of useful hints:

• The CrossListQueryInfo class is located in the Microsoft.SharePoint.Publishing namespace so don't forget to add a using for it and a reference to the micrososft.sharepoint.publishing.dll
  
• Cross list queries are very limited in which fields can be fetched or used in the where clause. I've had problems fetching the author of a list item or filtering based on similar fields. A solution that I had to use once was to handle events on the list so when items were added I would copy all the data that I needed into custom hidden fields and then use those fields in the cross list query.
  
• Adding indexed fields will help with the performance of cross list queries.

SharePoint Queries

We all know that SharePoint lists can provide a simple, user friendly and straight forward way of storing & retrieving user editable data. Once you start developing anything using SharePoint it would be very hard to escape SharePoint lists. Other than the fact that most SharePoint features rely on them (like blogs, discussion boards, galleries, etc.) they will save you a lot of time when you want data stored in a table format and the end user needs to edit that data, the administrator needs to secure the data, you want workflow and approval features, change history and all the other little features that SharePoint provides on list items.

But the problem arises when you as the developer need to use that data in other parts of your program and would like to access it just as you would a regular table in the database.

To be more specific let's assume we have decided to create a public (Internet) facing site which is going to among other features display a list of our products in different ways on separate pages. Let's say we want to show the latest products, the cheapest priced products, etc.

Now let's make a few assumptions: (1) we don't have this information in a standard DBMS (2) we want our team of "product administrators" to be able to introduce new products, modify previous specs etc. through a simple web UI and (3) the lists displayed on the public site will be formatted with the public sites branding.

OK let's see how we can solve this using SharePoint features and minimal coding effort:

I would create a SharePoint list and call it 'Products' (what a surprise!). This list will be setup to have all the necessary fields such as product name, description, price, …

Now if I was going to provide a view of this list showing the cheapest priced products, the first thing would be to create a view on the list which has the right ordering and filtering to achieve the criteria for cheapest priced product. Creating this view is very simple and can be easily achieved through the standard SharePoint UI. But we are looking for a way to fetch this list of products so we can display them in our own special web part/user control with our own special formatting, rules, etc.

OK how to get to this data using the SharePoint API:

A couple of different approaches can be taken to get to this data and I'm going to touch on two of them here:

1. You can access the same view that we described above using the backend API and fetch the items from the view.
   
2. You can create a query on the fly and execute it against the list to fetch the list of items you need and then work on them as needed.
   

Before I get into the details of each approach let's do a little comparison of each approach:

The first approach is much easier to program. You create a view through the standard SharePoint UI and give it a specific name and then start using it in your code.

The second approach requires more code but it is more flexible since you can decide about what you fetch at runtime (as opposed to working on a fixed view). I also like the second approach a lot better since all the things that I need are encapsulated in my code and I'm not relying on any external elements that for any reason might not exist (or might get deleted) and cause problems for my code.

So let's look at some code.

1^st approach: "You can access the same view that we described above using the backend API and fetch the items from the view"

SPSite site = new
SPSite("http://mysite");

SPWeb web = site.OpenWeb();

SPList productList = web.Lists["Products"];

SPView cheapestView = productList.Views["CheapestView"];

foreach (SPListItem item in productList.GetItems(cheapestView))

{

//do what ever you want to do with the item

}

2^nd approach: "You can create a query on the fly and execute it against the list to fetch the list of items you need and then work on them as needed"

SPSite site = new
SPSite("http://mysite");

SPWeb web = site.OpenWeb();

SPList productList = web.Lists["Products"];

SPQuery qry = new
SPQuery();

int maximumCheapPrice = 100; //what ever the maximum price criteria is

qry.Query = "<Where><Leq>" +

"<FieldRef Name=\"Price\" /><Value Type=\"Number\">" +

maximumCheapPrice.ToString() +

"</Value>" +

"</Leq></Where>" +

"<OrderBy>" +

"<FieldRef Name=\"Price\" Ascending=\"TRUE\" />" +

"</OrderBy>";

qry.RowLimit = 10; //only fetch the top 10 cheapest products

foreach (SPListItem item in productList.GetItems(qry))

{

//do what ever you want to do with the item

}

The weird way of defining a filter and order by clause for our query is called CAML. It's a XML based language that you can use to define whatever filter you need to fetch the data. There are some good free editors (try this) that you can use to define the filter and then copy paste the result into your code.