3

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?

3

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...!!

  • Thanks for your help, this whole thing is a bit tricky because it's a project that's been 'given' to me. So the basics are that the list can hold millions of rows, but getting anything over 5k at a time back could be tricky. I'll go back to the team with this - thanks. – Richard Housham Dec 14 '16 at 15:12
  • 1
    Very nice answer. I wanted to add my two cents. You can have millions of items in the lists, But you need to be very careful about it. 1)If you have indexed fields, this index will be recreated each time you add, modify or delete an item. 2) Deletion of even a single item in such lists can take up to several seconds. 3) You can remove the list threshold, but after, let's say a 300-500K items you'll see why this threshold was imposed when your site might work very slowly, if you work with this list a lot. 4) avoid item-level permissions 5) number and type of columns in the list matter a lot – Denis Molodtsov Dec 14 '16 at 16:08
  • ...slowest column types are lookup columns. user field is also a lookup – Denis Molodtsov Dec 14 '16 at 16:09
  • Thanks, with your comment on lookup columns being slow does that mean that queries like this '<Eq><FieldRef Name="MyUser" LookupId="TRUE" /><Value Type="User">" + user.ID + "</Value></Eq>' would be quite slow to run? – Richard Housham Dec 14 '16 at 16:31
  • Also with your 2) Caveat are the index remade, for additions/modifications as well? – Richard Housham Dec 14 '16 at 16:32
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

  • Thanks for your answer that how the indexes work and thinks are really interesting. TBO I think what I'm hearing alot of is if you plan to have a really large sharepoint List - be prepared for difficulties/issues. So although it might take a bit longer to create/edit a SQL Database/Table and have add/edit/delete around it - it'll pay in the future. – Richard Housham Dec 14 '16 at 15:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.