List Throttling Threshold Workaround for CQWP?

Question

Is there a good way to get around the list throttling threshold?

Here is my scenario: We have our top level site in a site collection with department sites beneath it. A few of the department sites are using a Task list. Originally, on the top level site, I created a page that had a few web parts on it that roll up all tasks for individuals who may be working in multiple areas (my tasks coming due in 2 weeks, my past due tasks, my deferred tasks, and my active tasks). All of these web parts are using the CQWP with some modifications to add a few more filters on it. It worked fine for a while until we went over our threshold of 5000 items. We do not want to increase this limit. I read that CQWP has some issues rendering on column that can't be indexed. I think the causing my issue is Assigned To (multi-select is not indexable). We do need the multi-select option.

Is there a better way to go around doing what I want to achieve? Either through a simple out-of-the-box solution or a third-party tool? I have to ability to edit the modify the additional filters on the web parts as well though Designer.

edit The CQWPs are looking at a specific site content type that is being used on these department task lists. For each CQWP, I have gone in and modified the filters using a CAML query, but none of them return information when they used to.

Answer 1

You can use the Content by Search Web Part (CSWP), which uses the search engine instead and it is not affected by the List View Threshold.

There are many examples if you google for this, such as Roll Up SharePoint 2013 Tasks with Content Search Web Part, Content by Search Web Part and Create a Search-Based Task Management Solution in SharePoint 2013

Basically you modify your search query to return only Task items, making sure that the lists are searchable and the properties you wish to returned are crawled.

Answer 2

When you use a Content Query Web Part with a large list, there are some important things to consider so that it correctly returns results and does not get blocked by the list view threshold.

• Use Content Query Web Parts to return items that users might frequently access, be interested in, or that might help users discover content.
• When you are using a Content Query Web Part against a large list, you should filter items so the query does not exceed the list view threshold.
• You should only use columns with indexes for filtering.
• Do not use the Content Query Web Part to query multiple lists if the total amount of items considered is greater than the list view threshold for auditors and administrators (by default 20,000).
• Use caching for faster load times and less SQL Server load.

Important thing: It follows the list view threshold setting for auditors and administration.

https://technet.microsoft.com/en-us/library/cc262813%28v=office.14%29.aspx?f=255&MSPPError=-2147217396