I wanted to write a few words on SharePoint “optimization” or to be more specific what to avoid when doing data queries.
So here we go:
1. Opening and using SPSite, SPWeb, SPList etc object
The first thing to know about SharePoint is that if you use the object model to get data lets say open an SPWeb object of site then you are going to waste resources of your server. SPSite, SPWeb and SPList are resource hogs. If you open a few of these then you are fine BUT if you happen to open many(lets say tens or HUNDREDS) then you are in trouble. The SPWeb object loads nearly all of it’s properties when you open an SPWeb. If you only need a few bits of info like a Web ID then you are better off using a class like SPWebInfo. The main point of this story is that do not open SP otb object unless absolutely needed and if you do need to open them keep it to the minimum. This means lets say that you need to find out the subwebs of a user under a web you should avoid using OTB functions like “GetSubwebsForCurrentUser”. Instead try to find out the subwebs for your current user in a different manner. Again this is not a problem if you only have a few webs to process but IF you have hundreds or thousands.
The unfortunate point here is that to my own experience SharePoint seem to load EVERYTHING there is for a requested opened object like SPWeb or SPSite, SPList etc. This is both good and bad. Good that use can get every piece of info you need at request BUT BAD because it hogs alot of resources while it does this. For heavy and complicated data queries and application logic this is bad.
2. SPRequest the one things you would like to have your hands on
SPRequest is something that you would like to get to use. SPRequest offers over 400 function that gets data from SP DB BUT you can not use it because the COM interface of an internal class (“SPRequestInternalClass”) is no accessible ourside of the SP DLL. The point here is that I recommend reading the following link on SPRequest for you to have a better understanding how the SPWeb, SPSite, SPList etc object are filled with data from the database. Also it is kind of sand that you can not use the functions in the internal class (I understand partially why they are hidden from use, mostly because MS wants most likely to avoid complex code that will break SP and clog MS SP support). That being said there are some great function available in the internal class. One of those is a function that returns all the web ID(notice ONLY the ID GUID) of all the subwebs for a user under a given web ID. Now this function would save alot of resouces in our problem above in step 1.
3. Different way of making data queries
I’m going to keep this short for the moment because the web is full of info on SP queries and optimization BUT I’ll make a quick notion of what is available.
– SPQuery: Normally used at list level. Not the fastest option. Use only if you know data amounts to be retrieved are not large.
–LINQ to SQL and CAMLEX: both are sound and fast to use at code level. I recommend both for most of data retrieval needs. A warning on CAMLEX, them queries generated by CAMLEX can be heavy so there might be additional resource usage when using CAMLEX.
– SPSiteDataQuery and CrossListQueryInfo: Good at site collection level. Can retrieve data from web under a site collection or under webs under a specified web. With proper queries inserted to these classes you can get data sufficiently fast for MOST of the needs of a client BUT there are times when data amount in a SP application is so large that the functionality of these classes are to slow or inadequate
– PortalSiteMapProvider: This class uses cached data for SP to retrieve data much faster than the previous examples. This might be a sound option is you want fast data retrieval with huge amounts of data. Especially if it is related to a custom navigation related functionality.
– Search Indexing: Lastly but a great choice if you can design and implement proper metadata to you site content is Search Indexing. This is to my knowledge the fastest way to perform data retrieval in SharePoint. It is so fast that when done properly there is nothing that can compete with this EXCEPT if you where allowed to make direct queries to the SP DB(which you are not of-course 🙂 ). The only major drawback here is that it really requires a great deal of knowledge of SP and searching and indexing to get this to work. If you get your metadata associations incorrect or are insufficient to specify which item is which then you will not get the right data when making queries. An example of this is a university SP application I worked on years ago( back when CMS 2002 and SP2003 was hot stuff 😀 ). This particular university wanted to have all of their courses, blogs, news and other information to be able to be queried from custom search pages that where responsive through AJAX or similar JS functionality. One of the major things we had to do was to have very specific metadata from SP list page metadata to HTML meta tags. This allowed us to get the wanted data very fast and accurately.
Here is also a link of the different performance numbers on different queries: