The content query web part is a powerful tool.  As a Support Escalation Engineer I get to see some very creative ways in which customers use this tool.  In this blog post I will be discussing how you can use ViewFieldsOverride to reduce not only the duration of these queries, but also the amount of data returned.

The Problem

When you perform a query using the out of the box Content Query Web Part (CQWP) SharePoint will pull from the database every column in your list or page library even if you only need to display a few columns.  This causes the query to take longer and to move a lot more data from the database server back to the WFE server.

Solution

When you use the ViewFieldsOverride you can specify the columns you want to display and SharePoint won’t pull back all of the extra columns.

Test

  1. I Created an out of the box SharePoint site.
  2. I then created a custom content type.
  3. Added every out of the box column to the content type.
  4. Gave the pages custom column “User Defined 1” a value of “Cheese” for multiple pages in the page library. 
  5. Filtered the CQWP to only return pages that had User Defined set to Cheese.  I did this so I could search the profiler trace for the word “Cheese” to find the query.
  6. Configured the CQWP to only return 2 items.
  7. Then I reset IIS and the SQL Server service.
  8. I started a profiler trace and loaded the page with the CQWP.
  9. Exported the web part and added the ViewFieldsOverride to only display the “Title” column which was already the only thing being displayed anyways, so there was no change to what the CQWP actually displayed.
  10. Imported the CQWP.
  11. Reset IIS and SQL Server service again.
  12. Started another profiler trace and accessed the page again.

Results – No Override

Columns Returned 31
Rows Returned 2
Reads 130
Duration 152
CPU 156

Results – With Override

Columns Returned 12
Rows Returned 2
Reads 102
Duration 90
CPU 94


As you can see from the above results, the duration, Reads (I/O), CPU, and the amount of data returned is decreased with the ViewFieldsOverride enabled.

Example

Here is an example of the change made to the exported CQWP.

<property name="ViewFieldsOverride" type="string">

     <![CDATA[<FieldRef Name="Title" Nullable="True" Type="Text"/>]]>

</property>