Solved

Large list in SP 2007 or SP 2010.

Posted on 2010-11-16
8
627 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
ID: 34152280
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
ID: 34152891
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
ID: 34153158
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Techsavy
ID: 34180721
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:Montoya
ID: 34180734
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
ID: 34183959
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
ID: 36135903
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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. …
Work Over Net is a new and very powerful collaboration product. With its new easy interface it is becoming very competitive to other similar products like webex and office interactive. WON 2010 have the standard business tools needed for multi-offic…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

813 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

10 Experts available now in Live!

Get 1:1 Help Now