SharePoint – How to handle the 5000 limit

One of the common scenario’s you run into when you are using SharePoint for some time is that lists and doc. libr. are quickly filling up over the magic number, being 5000 records.

At least this limit is set by default and can be changed using the Central Admin. But that’s a different story.

Well let’s give you an example how we can overcome the 5000 limit without having to change the default limits on a farm level.

In our SharePoint we have a list for users to record some Time Sheets per Project.

image

As you can see 2 of them are over the limit.

In the list settings it will throw up an alert saying you are reaching or over the top.

image

In normal circumstances a standard user would NOT be able to access any of the data in the list until it is again below the limit.

Solution :

– One of the solutions is to create a VIEW and a FILTER the list by user.

So that when he want to look at his dataset you queries that list and get’s back only his records. which is less then 5000.

image

As you can see there is another thing that strikes you (or maybe not Winking smile)

You see that I have created an Index Field –Created By (Indexed)

The difference for SharePoint is that if you don’t create indexes, it will have to scan the full database table. So there is a performance impact as well as as it has to read all records in the table before returning the results.

Best is to created indexes for large list, being the best practices

How to do this is easy, go to List settings –> Indexed Columns

image

And create one or more indexes.

image

Set the Filtered VIEW as default view, and test if a user (not administrator) can access the list, even if this contains more than 5000 records.

image

As you can see there is no problem at all add more records to the oversized list.

PS:  Important remark is that if you make a FILTER setting to limit the records to be returned.

Make sure that you DO NOT set a SORT settings.

Because the SORT will have to read the full list again before it returns the records.

So this will destroy the initial solution again Sad smile

Enjoy!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: