Solved

Large list in SP 2007 or SP 2010.

Posted on 2010-11-16
8
640 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

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…
The vision: A MegaMenu for a SharePoint portal home page The mission: Make it easy to maintain. Allow rich content and sub headers as well as standard links. Factor in frequent changes without involving developers or a lengthy Dev/Test/Prod rel…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

821 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