SharePoint 2010: efficient union query across site collections?

Question

In SharePoint 2010, I would like to create a list in one SiteCollection that queries data from several other SiteCollections that have common fields:

Is this possible, and if so - can it be done efficiently?

The current proposed solutions involves setting up EventReceivers on each of the SiteCollections that we want to query, and updating a master list (the OverviewSiteCollection) with the new data each time one of the SiteCollections is updated or has data added.

Is there a better way to achieve what we want to achieve??

Answer 1

It's not possible out of the box, but you can buy the Lightning Conductor Web part from lightning tools. It works similarly to the Content Query Web Part, but it works across site collections.

It's compatible with Sharepoint 2007 and 2010.

http://www.lightningtools.com/pages/lightning-conductor-web-part.aspx http://www.lightningtools.com/blog/archive/2010/04/29/sharepoint-2010-and-the-lightning-conductor-web-part.aspx

As for it's efficiency, I'm not sure, as I've only used it for small collections of data.

Answer 2

That is probably how I would do it. Even if you were in the same site collection, I can't think of a way to aggregate and replicate items into another list. You could either push the items through an Event Receiver or pull the items through a Job Definition.

The only other thing I can think of are the new Taxonomy fields and Term Sets. But that is meant for text based choice fields. I don't think it would work for multi-column items as shown in your example.

Answer 3

Whenever I think about rolling up content across Site Collections, I think about leveraging (if possible) 1) Managed Metadata Services (taxonomy and Shared Content types)
2) Search (leverage search scope etc) 3) Some kind of web part to surface the data on the pages. And efficiency being one of your concerns, Search is your best bet! Nothing can beat its performance!

I have come across a good solution (Extending the Content Query Web Part to Aggregate Data Across Site Collections and External Sources) which might give you some directions: http://www.msteched.com/2009/NorthAmerica/OFC405

Search has its limitations though (the accuracy of the results depends on when the last index operation was run). If this bothers you, you may perhaps end up iterating the lists of your site collections manually as suggested here: http://social.msdn.microsoft.com/Forums/en-US/sharepointdevelopment/thread/d271da25-57f4-4f59-a34d-fd06bc4c8ce6

The lightning web part as suggested in another answer could be a good fit for you. I would suggest to evaluate it (like how it does it internally, permissions, additional configuration, efficiency, caching, control over display etc).