Managing Sharepoint 2010 list with more than 5K items

MisUszatek
MisUszatek used Ask the Experts™
on
I have a list with more than 30K items and a lot of issues with it. First there is a problem with InfoPath that I am trying to resolve in a separate question. Here I just need to understand how that 5K limit works.

So I have a list with over 30K of items and new ones are added daily. When I go to the list my default view is last 7 days which is usually less than 1000 items. This view is grouped by person so you need to click a name to expand it. It seems to be working OK. The weird thing is that when I use the column filter to show me results for only one name it shows it collapsed with the correct number of items but when I click the name to expand it shows the error:

This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.

Why??? The original view returns only about 1K items and when I want to filter it even more to about 20 items I get that error? I have 4 columns indexed including person and date modified that I use for filtering here.

Any my sub-question: if I want to keep all items but the ones older than 3 months move automatically to a second list for archiving what functionality of SharePoint I should use for that? What would be the best practice?

 screenshot
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Clay FoxDirector of Business Integration

Commented:
Microsoft only recommends 2000 items in a list before performance starts to degrade.

The short answer is you need a real database, SharePoint is not disigned for this amount of data.  It is a flat data structure so that you can navigate quickly. For large amounts of data these design constraints actually work against you.

I would move the data to a SQL table. You can still use InfoPath and SharePoint, just the heavy lifting and storage and access is handled by the database and is what it was designed to do well.

Author

Commented:
Let me make sure I understand> Your recommendation is to use a stand-alone database on SQL and access it via BCS?
Top Expert 2011
Commented:
There are lot of ways you can improve the perfomance:
1. Indexing
2. Create folders to divide your list.
3. There have been good improvments in 2010 for large lists.
4. List Throttling

Take a look at this:
http://www.sharepointanalysthq.com/2010/05/large-list-improvements-in-sharepoint-2010/
 http://blogs.technet.com/b/speschka/archive/2009/10/27/working-with-large-lists-in-sharepoint-2010-list-throttling.aspx
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
@abhitrig
I have indexing and filtering. As you can see the view returns less than 1000 items and there are still problems with it. Trying to figure out why.
Top Expert 2011

Commented:
This is interesting... Can you try the following:
1. Create a new view with similar filters ( start from an empty view)
2. For the new view, set the pagination to say, 20


Also to answer your second question:
To move older items to a separate list, you can create a List/Site level workflow

Author

Commented:
Didnt have time to test it. Will do it next week. Thanks!

Author

Commented:
I don't have time to test it but EE is forcing me to close it. Thanks for your help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial