Solved

Large list in SP 2007 or SP 2010.

Posted on 2010-11-16
8
606 Views
Last Modified: 2012-08-13
Hi,

we have a scenario, where we may end up populating a SharePoint list with over 100,000 records. In this case, what is the best way to handle these in a SharePoint list? Or is there any other better solution ? which gives me the benefit of SharePoint without affecting the performance of the list?


Thanks,

0
Comment
Question by:Techsavy
8 Comments
 
LVL 12

Accepted Solution

by:
geowrian earned 168 total points
Comment Utility
The basic rule of thumb is to keep the number of list items shown on a single page to about 2,000 or less. This can be accomplished by ensuring all views have the "Item Limit" set to show items in batches of 2,000 or less. In Sharepoint 2010, you can configure an option to force per-list/view limits (including different limits for administrative users).

You can also use folders to partition the items.

If you have any custom coding that retrieves the data, ensure they also follow the same practice of not pulling more than ~2,000 items at once (pull the items in chunks if necessary to go over this limit). The major performance issue is that while the SQL server is retrieving the items, it locks the entire table, making any other read/writes to any other lists wait for the very large query to complete.

Microsoft has a white paper on this exact subject:
http://go.microsoft.com/fwlink/?LinkId=95450&clcid=0x409
0
 
LVL 6

Assisted Solution

by:einarbrown
einarbrown earned 166 total points
Comment Utility
You can also index columns to increase performance in Sharepoint 2010.

http://www.sharepointsecurity.com/sharepoint/sharepoint-development/sharepoint-2010-indexing-best-practices/
0
 
LVL 2

Assisted Solution

by:mangesh004
mangesh004 earned 166 total points
Comment Utility
Hi,

Recommended approach is not to have more than 2000 items in a single container / view. If you need to have 100,000 records in a list (which is actually a huge no.) then i would recommend that create folders in the list and make sure that you are not crossing more than 2000 items in a single folder. You have some event handers to manage those folder creating quarterwise folders.

If the data is just read only then you can have it in SQL table and implement BDC to view the data. For write operations you can have some web parts / usercontrols.

If you have attachments in the list or item level permissions and you know that its gonna grow in futre then just avoid list. It will put too much pressure on the search crawing as well but that decision you can take based on the hardware and other application function.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:Techsavy
Comment Utility
Hi Mangesh,

will your approach improve performance by any chance?i.e if I have 100,000 items in a list without folders in one case, but then I have another case where i split these 100,000 items into different folders. will the performance differ? i.e in the second case, where have 100 folders that contain all 100, 000 items in it.? if this is true then i like this approach.


can you please clarify?

thanks
0
 
LVL 19

Expert Comment

by:Iammontoya
Comment Utility
all queries in SharePoint are going to happen on demand. If you want to improve performance, start with the suggestions above...and here are a couple of other things:

Set a rule to archive older items to another list. Chances are that you won't be accessing 100,000 records all the time. You can set up a worfklow that archives older items or items not modified in a long time to another list, therefore maintining a streamlined list.

Group your items in all your views. Grouping the items will dramatically increase your performance as it does not have to display each item on the screen. Just note that once they hit that plus (+) symbol, the natural course of things will take effect as it tries to display all items in that group.

Try to use a filter through a data view web part. That way the user is looking for specific filtered items which would only return a very small subset of the 100k items.

Hope that helps...
Cheers

0
 

Author Comment

by:Techsavy
Comment Utility
Hi Tammontoya,


Thanks for the brilliant idea of workflow.

I like the idea of setting up a workflow. However I am still waiting to clarify from mangesh 004 on using folders.

0
 
LVL 17

Expert Comment

by:GreatGerm
Comment Utility
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Note:  There are two main ways to deploy InfoPath forms:  Server-side and directly through the SharePoint site.  Deploying a server-side InfoPath form means the form is approved by the Administrator, thus allowing greater functionality in the form. …
Last week I faced a strange issue recently, i have deployed SharePoint 2003 servers for one project and one of the requirements was to open SharePoint site from same server. when i was trying to open site from the same server i was getting authentic…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now