What are the possible issues with large sharepoint lists and queries?

Question

Background

We have developed a sharepoint 2010 enterprise solution - application that basically uses sharepoint lists in order to store the details.

In order to get the information out we use Caml queries and display the data basically outside of the normal 'view'

I guess this is a Server Side Object Model using webforms. The list will keep a record (same as sql server) of the records.

Problem

The queries look (roughly) like this

SPQuery query = new SPQuery();
            query.RowLimit = 500;

            // get all availability records that are in-progress or due,
            // ignore past periods...
            query.Query =
                "<OrderBy>" +
                    "<FieldRef Name=\"UnavailableFrom\" Ascending=\"FALSE\"/>" +
                "</OrderBy>" +
                "<Where>" +
                    "<And>" +
                        "<Eq><FieldRef Name=\"Clinician\" LookupId=\"TRUE\" /><Value Type=\"User\">" + user.ID + "</Value></Eq>" +
                        "<Geq><FieldRef Name=\"UnavailableTo\" /><Value Type=\"DateTime\" IncludeTimeValue=\"FALSE\">" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(requestedDate) + "</Value></Geq>" +
                    "</And>" +
                "</Where>";

            SPListItemCollection items = availabilityList.GetItems(query);

So my question is how is this interpreted behind the scenes. Does Sharepoint get all the records and then limit or does it work much like sql does. Get me some rows and then stop at the limit?

There is another case where I'm not doing a limit but then doing a count. What happens in this scenario. Could I run into difficulty in say a few years down the line when the tables get larger - say over 5000.

I've read stuff online which says that lists have trouble when they go over a certain amount, but that might be because of display in the 'sharepoint view'. Then again I've also read that they are built for this kind of usage.

I know the document storage, if used correctly with folders and such is ok. Do the lists follow the same rules?

Answer 1

So my question is how is this interpreted behind the scenes. Does Sharepoint get all the records and then limit or does it work much like sql does. Get me some rows and then stop at the limit?

Answer - No, SharePoint does not work like SQL. It first get all the records and then only number of records mentioned in row limit.

There is another case where I'm not doing a limit but then doing a count. What happens in this scenario. Could I run into difficulty in say a few years down the line when the tables get larger - say over 5000.

Answer - Assuming List items threshold value is 5000(set default by SP but we can change). If you CAML query filters is going to return more than 5000 items, it will not send data but will give you error. If you have specified multiple filter, the first filter should return items less than 5000. So Order of specifying filter is also important.

Also make sure you are creating index for columns which you are going to use in filter. If your filter columns are not indexed and items in list have crossed above 5000. your caml query will not work.

I've read stuff online which says that lists have trouble when they go over a certain amount, but that might be because of display in the 'sharepoint view'. Then again I've also read that they are built for this kind of usage.

Answer - Right, SharePoint list can hold millions of data. it is just that performance is not degraded we are being imposed with threshold limits.

I know the document storage, if used correctly with folders and such is ok. Do the lists follow the same rules?

Answer - Yes if you are storing items in folders, it means you are structuring your data to be in folders, so you can make data filter based on folders.

Hope this helps to understand...!!

Answer 2

You can use SQL Server Profiler to see exact queries to SharePoint content databases (btw queries are complex), but I know about the data structure inside SQL.

You will be surprised, but all your lists' and libraries' data stored in one single table - AllUserData, per content database basis. For example, if you have 10 site collections in one content database, all data (from lists and libraries) will be in one single table.

You know, that when your query exceed 5k limit, you well get an exceptions. There are different approaches how you can avoid this - use folders, use indexes and filters, or use search. You will have an exception in your CAML query too, without aforementioned tips.

Answering on your questions:

Does Sharepoint get all the records and then limit or does it work much like sql does. Get me some rows and then stop at the limit?

It doesn't work much like sql, it uses different internal mechanisms to create resulting SQL query and finally run it. This depends on filters, lookup, folders. If your query will return more than 5k items and fields Clinician and UnavailableTo are not indexed you will get an exception. If this fields are index, but the number of results returned (with indexed fields) is more than 5k you will get an exception.

There is another case where I'm not doing a limit but then doing a count. What happens in this scenario. Could I run into difficulty in say a few years down the line when the tables get larger - say over 5000.

Yes you will have problems, the same way as for your first question.

I know the document storage, if used correctly with folders and such is ok. Do the lists follow the same rules?

For list you can use folders too.

Further reading:
How list column indices really work under the hood

Manage large lists and libraries in SharePoint

Designing large lists and maximizing list performance