SPQuery best practices in SharePoint

This post will cover SPQuery best practices in SharePoint due to my recent experience with customers complaining about the performance of their SharePoint sites. This problem 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 complaints.

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

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

But as some of you know, we can explore the SPQuery properties to use Collaborative Application Markup Language (CAML) to help on achieving better queries with improved performance. As an advice, you can use CAML Query Builder to help you writing those queries:

But the SPQuery isn’t just about CAML… there is a lot of options that can be used too like the ones present in this post:

SPQuery RowLimit as a best practice

It 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:

SPQuery ViewFields as a best practice

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.

SPQuery ListItemCollectionPosition as best pratice

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 taking your time to read my blog. Hope this post can you on learning SPQuery best practices in SharePoint. Oh! Also I would like to suggest a post about Optimizing your SharePoint environment if you are dev like me.

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