none
CQWP Performance against large number of items in a Site Collection?

    Question

  • Hi all,

    Do we need to be aware of any performance issues when using CQWP to query something like "Most Recent Documents" from a Site Collection potentially containing 10,000+ items which may grow over the years? We are not going to return 5,000+ items, only say, 15 items.

    Since we are going to use SharePoint 2010, we don't have access to the CSWP. 

    Thanks!


    • Edited by WATER004 Monday, August 04, 2014 3:21 PM
    Monday, August 04, 2014 3:20 PM

Answers

  • If you're going to be querying large lists, you need to make sure that the key part of your query is using an indexed field as noted before. It's also important to ensure that this initial query will retrieve less than 5000 items before the rest of the query is applied.

    In addition, be aware that when using the CQWP, there is a hard limit of 1000 lists before it just stops working and doesn't return any results. For example, if you're querying for any library in a site collection with a particular content type, and that content type exists in every site, then the CQWP will check every list in every site to build the results. If you have 10 lists in 100 sub-sites, you can very quickly hit that limit and see no results in your CQWP.

    Just because you have SharePoint 2010, doesn't mean you can't use search to do this, the Core Results Web Part can be used just as successfully using a static query with much better performance than CQWP which you need to offset against how stale you're results may be (They'll only be as fresh as your last content crawl.)

    Paul.


    Please ensure that you mark a question as Answered once you receive a satisfactory response. This helps people in future when searching and helps prevent the same questions being asked multiple times.

    Wednesday, August 06, 2014 12:30 PM
  • Hello,

    As Matthew said, you need to use indexed columns (set up in your list and only use them for your query)


    Best regards, Christopher.
    Blog | Mail
    Please remember to click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if it was useful.
    Why mark as answer?

    Wednesday, August 06, 2014 6:20 AM
    Answerer

All replies

  • I am assuming that you have an on premises setup, in which case you can experiment with the following to improve performance for CQWP

    ViewFieldsOverride: To limit the results to only the columns you require

    http://blogs.msdn.com/b/mimorr/archive/2009/11/19/improve-performance-of-content-query-web-parts-using-viewfieldsoverride.aspx

    Object Caching: CQWP uses cross-list query caching to improve its performance

    http://karinebosch.wordpress.com/my-articles/improving-performance-of-sharepoint-sites/part-5-object-caching/


    Thanks, Ransher Singh, MCP, MCTS | Click Vote As Helpful if you think that post is helpful in responding your question click Mark As Answer, if you think that this is your answer for your question.

    Monday, August 04, 2014 3:38 PM
  • Thanks Ransher! I will take a look at those links!

    Tuesday, August 05, 2014 2:11 AM
  • Hi Water004, 

    You need to think carefully about the number of CQWP's used on a single page, and the types of queries you are doing. 

    You still need to adhere to best practises when querying lists, to achieve good performance, and to prevent list view threshold exceptions (which happens when your queries need affect more than 5000 rows of a content database). 

    The best way to do this, is to use fields that have indexes on them in your filter / order by clauses. This is especially important on lists that contain more than 5000 item, even if you are just displaying 10 items. 


    Regards, Matthew
    MCPD | MCITP
    My Blog
    View Matthew Yarlett's LinkedIn profileView Matthew Yarlett's profile

    See my webpart on the TechNet Gallery that allows administrative users to upload, crop and format user profile photos. Check it out here: Upload and Crop User Profile Photos

    Tuesday, August 05, 2014 3:21 AM
  • Thanks Matthew,

    I tried it on a SP Online Trial, I have just 1 parent site and 1 subsite with total of around 20,000 items.

    I try to get the 25 most recent items using CQWP. It gives me this error:

    Your query returned a greater number of results than the web application's query throttling limit. Please adjust the configuration of this Web Part o increase the throttling Limit.

    Probably my other choice would be just a Custom Webpart, where I perform partial queries so as not to reach the limit, then just combine everything and sort that internally.

    What do you guys think?

    Wednesday, August 06, 2014 5:56 AM
  • Hello,

    As Matthew said, you need to use indexed columns (set up in your list and only use them for your query)


    Best regards, Christopher.
    Blog | Mail
    Please remember to click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if it was useful.
    Why mark as answer?

    Wednesday, August 06, 2014 6:20 AM
    Answerer
  • If you're going to be querying large lists, you need to make sure that the key part of your query is using an indexed field as noted before. It's also important to ensure that this initial query will retrieve less than 5000 items before the rest of the query is applied.

    In addition, be aware that when using the CQWP, there is a hard limit of 1000 lists before it just stops working and doesn't return any results. For example, if you're querying for any library in a site collection with a particular content type, and that content type exists in every site, then the CQWP will check every list in every site to build the results. If you have 10 lists in 100 sub-sites, you can very quickly hit that limit and see no results in your CQWP.

    Just because you have SharePoint 2010, doesn't mean you can't use search to do this, the Core Results Web Part can be used just as successfully using a static query with much better performance than CQWP which you need to offset against how stale you're results may be (They'll only be as fresh as your last content crawl.)

    Paul.


    Please ensure that you mark a question as Answered once you receive a satisfactory response. This helps people in future when searching and helps prevent the same questions being asked multiple times.

    Wednesday, August 06, 2014 12:30 PM
  • Thanks guys!

    @Christopher

    Thanks for that suggestion. I'll try and get back with the results.

    @Paul

    Thanks for the suggestion! The Core Results Web Part sounds interesting. I'll take a look into that.

    Tuesday, August 12, 2014 8:19 AM