wiliammbr's blog

Some thoughts on SharePoint and other random stuff

Improving your SPQuery performance on SharePoint

So many times I’ve seen my clients complaining about the performance of their Sharepoint sites. That of course collaborated to create a bad impression of the product. “The page takes too long to load” or “Web Parts with pagination are terrible” were some of their protests.

After the first contact with them, I usually asked for the source code to find out what was happening. It didn’t take too much to find out that almost every case there were happening due queries being made with SPList.Items property and followed by some LINQ query to filter the data.

An simple example of a method requesting all the List Items:

But as everyone know we can explore the SPQuery properties to use Collaborative Application Markup Language (CAML) to help on achieving better queries with improved performance. But the SPQuery isn’t just about CAML… there is a lot of options that can be used like:

RowLimit: sets a limit for the items being returned in your query. It can be interpreted as a SELECT TOP from SQL. This option is great in cases when you need to check if a list contains items or to prevent problems with List View Threshold:

ViewFields: it helps on defining which fields are going to be returned in your query. If a list contains 30 fields and we just need to show the ID, Title and Creation Date, you can define those fields and increase the page response. After all, we can compare it as a SELECT * and a SELECT Id, Title, Created from SQL.

ListItemCollectionPosition: this option allows you to search items organized by position based on the ID. It’s great for pagination and to overcome problems List View Threshold:

As always, thanks for reading.

References:
Best Practices: Common Coding Issues When Using the SharePoint Object Model
SPQuery Members

1 Comment

  1. Realmente potencializa o desempenho da consulta!

Comments are closed.

© 2017 wiliammbr's blog

Theme by Anders NorenUp ↑